Autofill subform when move to new record on Main form
in [General]
|
From: babs on 30 Sep 2009 17:36 Looked at the Immediate Window in Visual code view and see this below- don't know if the order In the tables behind the scenes for some strange reason Job # is a number field - not sure if that would explain the error and where would I change the code. Immediate window - not sure also why grabbing Mon and not Fri??? INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date], Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE", "1800270", "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0, 42.05; INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date], Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE", "1800270", "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0, 42.05; INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date], Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE", "1800270", "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0, 42.05; INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date], Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE", "1800270", "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0, 42.05; INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date], Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE", "1800270", "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0, 42.05; "Steve Sanford" wrote: > Yes, still here. > > When you got the error, what line was highlighted? > > Have you tried using " Debug.Print sSQL" statements after the sSQL lines > to see if the SQL statement are formed correctly? > > When you look at the line in the debug window, the delimiters for field > types are: > > Type Delimiters Example > ------------------------------------------ > strings " " or ' ' "Hi" or 'Hi' > dates # # #1/1/2009# > numbers no delimiters 28.50 > > > Using data you provided, I put the sSQL lines in my code and ran it. The > debug window showed the value for "[Job #]" didn't have quotes around it. > > > Here is the modified insert SQL line: > > 'create the insert string > sSQL = "INSERT INTO [TimeCardMDJEFF]" > sSQL = sSQL & " ([Man Name], [Job #]," > sSQL = sSQL & " [name], [Date]," > sSQL = sSQL & " Workdate, [Day]," > sSQL = sSQL & " [Hours(ST)], ActualRate)" > sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, """ & r.Fields(1) > sSQL = sSQL & """, """ & r.Fields(2) & """, #" & dteWeekEndDay > sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay > sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";" > > Debug.Print sSQL > > > HTH > -- > Steve S > -------------------------------- > "Veni, Vidi, Velcro" > (I came; I saw; I stuck around.) > > > "babs" wrote: > > > Steve, > > I added the comma after name on both sql - but now getting a different error - > > In title bar of box it says Search error 3075 > > inside - syntax error in query expression '42.05' > > > > I looked it up but can figure out what is wrong??? > > inserted code below for both sSQL > > I had to go out of town and just got back - really want to get this figured > > out today if possible - thanks soo much for helping, > > Barb > > > > '- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] =Last Fri > > sSQL = "SELECT [Man Name], [Job #], [name]," > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate" > > sSQL = sSQL & " FROM TimeCardMDJEFF" > > sSQL = sSQL & " WHERE [Workdate] = #" & dteFri & "#;" > > > > > > > > 'create the insert string > > sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name]," > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate)" > > sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, " & r.Fields(1) > > sSQL = sSQL & ", """ & r.Fields(2) & """, #" & dteWeekEndDay > > sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay > > sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";" > > > > "Steve Sanford" wrote: > > > > > Barb, > > > > > > When you changed the field names in the insert statement, a comma was also > > > removed. A comma must be at the end of this line (after name, but inside the > > > quote): > > > > > > sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name]," > > > > > > Add the last comma, and it should run correctly. > > > > > > > > > > > > > don't get the Insert into code and what the last line(sSQL = sSQL & """, " & > > > > r.Fields(6) & ", " & r.Fields(7) & ";") means???HELP > > > > > > "r" is the record set name and "r.Fields(6)" is the 7th field in the > > > recordset. > > > > > > The record set field numbers are zero based, so the first field is zero, the > > > 2nd field is 1, the third field is 2,........ > > > > > > Using "r.Fields(6)" is a way of getting the data from a field in a record > > > set without knowing the name if the field. Instead of "r.Fields(6)" , > > > I could have used r.Fields("[Hours(ST)]"). > > > > > > In the recordset "r": > > > Field # Your Name > > > ------------------------------------- > > > 0 [Man Name] > > > 1 [Job #] > > > 2 [name] > > > 3 [Date] > > > 4 Workdate > > > 5 [Day] > > > 6 [Hours(ST)] > > > 7 ActualRate > > > > > > > > > > > > HTH > > > -- > > > Steve S > > > -------------------------------- > > > "Veni, Vidi, Velcro" > > > (I came; I saw; I stuck around.) > > > > > > > > > "babs" wrote: > > > > > > > Steve, > > > > Yeah - I know they should use the SS# as PK - but they have been doingthis > > > > for years and seems to be okay. > > > > > > > > I added a new record on main for new weekend date- have people on fri. of > > > > prev. week - added All of your wonderful suggest code to the button on click > > > > event > > > > > > > > I am getting Error on Insert Into - then Done- but no records show up. > > > > a few of the field names- I changed to reflex there name - job is Job > > > > #,job name is Name, ST is HOurs(ST) Not sure but just always add the [ ] > > > > just in case. > > > > > > > > below is the code I have in it- just grab the 2 sections out. I really > > > > don't get the Insert into code and what the last line(sSQL = sSQL & """, " & > > > > r.Fields(6) & ", " & r.Fields(7) & ";") means???HELP > > > > > > > > > > > > > > > > '- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] =Last Fri > > > > sSQL = "SELECT [Man Name], [Job #], [name]," > > > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate" > > > > sSQL = sSQL & " FROM TimeCardMDJEFF" > > > > sSQL = sSQL & " WHERE [Workdate] = #" & dteFri & "#;" > > > > > > > > > > > > 'create the insert string > > > > sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name]" > > > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate)" > > > > sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, " & r.Fields(1) > > > > sSQL = sSQL & ", """ & r.Fields(2) & """, #" & dteWeekEndDay > > > > sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay > > > > sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";" > > > > > > > > > > > > > > > > AGAIN - thanks a ton for helping!! > > > > Barb > > > > "Steve Sanford" wrote: > > > > > > > > > Barb, > > > > > > > > > > If you want to use a list box instead of or in addition to the combo box, > > > > > you could have a multiselect list box that would list the workers. You select > > > > > one or more people, then click on a button. It could look to see if there was > > > > > a record for the previous Fri and add records for the week. If there wasn't a > > > > > record record, it could add a weeks worth of blank records for him. > > > > > > > > > > > > > > > Another question....(I know - but it is important). > > > > > > > > > > What do you do if you have two John Smiths or Jim Jones?? Since you use a > > > > > person's name, how do you tell them apart?? > > > > > > > > > > > > > > > > > > > > HTH > > > > > -- > > > > > Steve S > > > > > -------------------------------- > > > > > "Veni, Vidi, Velcro" > > > > > (I came; I saw; I stuck around.) > > > > > > > > > > > > > > > "babs" wrote: > > > > > > > > > > > Steve, > > > > > > this was what you stated before and this is what I would love - Also I think > > > > > > the Insert 6 record button (code in previous posts)I have on the main form > > > > > > with the man name combo box would take care of inserting the records - for a > > > > > > New man not sched. last week. > > > > > > > > > > > > Your prev. post > > > > > > If I understand correctly, if there were two workers (Perl and Earl), you > > > > > > would want to: > > > > > > > > > > > > 1) look at the previous friday's schedule > > > > > > 2) insert 6 records (Mon - Sat) records for this week's jobs for Perl and > > > > > > 3) insert 6 records (Mon - Sat) records for this week's jobs for Earl > > > > > > > > > > > > > > > > > > If there were 10 workers on the previous friday, you would want 60 records > > > > > > entered?? > > > > > > > > > > > > Thanks sooo much for still helping would love to get it done Before Thurs. > > > > > > if possible. > > > > > > > > > > > > Barb > > > > > > > > > > > > > > > > > > "Steve Sanford" wrote: > > > > > > > > > > > > > Barb, > > > > > > > > > > > > > > > Is the main form recordsource a query? No it is just a table for the Week > > > > > > > > > > > > > > I would recommend using a query for the main form record source. A table is > > > > > > > just a bucket the you put data into; it is not guaranteed to be in any order, > > > > > > > whereas a query is sortable. > > > > > > > > > > > > > > > > > > > > > I have 98% of the code done. I have a pretty good idea of what you want > > > > > > > done, but I don't know *how* you do it. > > > > > > > > > > > > > > Let's say you have one worker "Bob". > > > > > > > Bob worked 9/14 - 9/18/2009. > > > > > > > NO records have been entered for the week 9/21 - 9/25/2009. (Week ending 9/26) > > > > > > > And let's say today is Sun. 9/20/2009. > > > > > > > > > > > > > > So, my questions are: > > > > > > > > > > > > > > - Is 9/27/2009 already entered in the main form? (do you pre-enter sundays > > > > > > > for the year?) > > > > > > > > > > > > > > > > > > > > > - Do you want to select 9/20/2009 in the main form and have the code enter > > > > > > > records for 9/21 - 9/25 (9/27 must already be entered in the main form) > > > > > > > > > > > > > > - Do you want to select 9/20/2009 in the main form and have the code enter > > > > > > > the next sunday date (9/27), then have the code enter the records for the > > > > > > > week 9/21 - 9/25? > > > > > > > > > > > > > > - Do you want to enter 9/27 in the main form, then select it and have the > > > > > > > code enter the new records for 9/21 - 9/25? > > > > > > > > > > > > > > > > > > > > > I'm looking for how you manually add records now. > > > > > > > > > > > > > > HTH > > > > > > > -- > > > > > > > Steve S > > > > > > > -------------------------------- > > > > > > > "Veni, Vidi, Velcro" > > > > > > > (I came; I saw; I stuck around.) > > > > > > > > > > > > > > > > > > > > > "babs" wrote: > > > > > > > > > > > > > > > You have the TimecardMdJeff table correct! > > > > > > > > > > > > > > > > What is the name of the main form table? WeekEndDate > > > > > > > > > > > > > > > > > > > > > > > > Is the main form recordsource a query? No it is just a table for the Week > > > > > > > > End Date - ie. just 52 records for one year. The combo box on the main for > > > > > > > > is not bound and just set up for now to list Available people to schedule > > > > > > > > when click on them - would like to be able to insert their 6 new records > > > > > > > > based on the previous week. > > > > > > > > > > > > > > > > For next post - we schedule Sunday night or very early Sunday morning. > > > > > > > > > > > > > > > > thanks again for helping ! would love some code to help with your above > > > > > > > > ideas - it is really what I would like to have happen. > > > > > > > > Barb > > > > > > > > > > > > > > > > > > > > > > > > "Steve Sanford" wrote: > > > > > > > > > > > > > > > > > OK, now I need to get my head around *when* you add the new week > > > > > > > > > records........ > > > > > > > > > > > > > > > > > > > Here is a sample of a few records for the data - The mainform is just Week > > > > > > > > > > End date so pretty self explanitory there - thinking would have the List box > > > > > > > > > > instead of combo box of who to sched. on that mainform like have now > > > > > > > > > > > > > > > > > > > > For subform > > > > > > > > > > Man Name,Job#,Name(actually job name),Date(which is Week End Date), > > > > > > > > > > > > Workdate,Day,ST(for hours in straight time),OT,DT,ActualRate - there are more > > > > > > > > > > Smith, Rick ;80140020 ;The Legacy at Millennium Park; 9/6/09; 8/31/09;Mon; > > > > > > > > > > 8;other hours for overtime double time are blank, $28.20 > > > > > > > > > > Smith, Rick 90170054 Outdoor Pipe at 1060 Northpoint 9/6/09; 9/1/09 Tues 8 > > > > > > > > > > $28.20 > > > > > > > > > > Smith, Rick 80140020 The Legacy at Millennium Park 9/6/09; 9/2/09Wed 8 > > > > > > > > > > $28.20 > > > > > > > > > > Smith, Rick 80140014 Wheaton College Warrior Dome 9/6/09; 9/3/09Thur 8
From: babs on 30 Sep 2009 17:44 Steve, Was able to modify code to get rid of "" for job # and immediate window shows the 1 Record and no quotes around Job # - should be good since it is a number field. - see below - STILL GETTING SAME SYNTAX ERROR not sure why grabbing the Mon - and shouldn't it show 6 records added in immediate window?? Help?? 'create the insert string sSQL = "INSERT INTO [JeffTime Card MD Query]" sSQL = sSQL & " ([Man Name], [Job #]," sSQL = sSQL & " [name], [Date]," sSQL = sSQL & " Workdate, [Day]," sSQL = sSQL & " [Hours(ST)], ActualRate)" sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, " & r.Fields(1) sSQL = sSQL & ", """ & r.Fields(2) & """, #" & dteWeekEndDay sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";" Debug.Print sSQL INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date], Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE", 1800270, "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0, 42.05; "Steve Sanford" wrote: > Yes, still here. > > When you got the error, what line was highlighted? > > Have you tried using " Debug.Print sSQL" statements after the sSQL lines > to see if the SQL statement are formed correctly? > > When you look at the line in the debug window, the delimiters for field > types are: > > Type Delimiters Example > ------------------------------------------ > strings " " or ' ' "Hi" or 'Hi' > dates # # #1/1/2009# > numbers no delimiters 28.50 > > > Using data you provided, I put the sSQL lines in my code and ran it. The > debug window showed the value for "[Job #]" didn't have quotes around it. > > > Here is the modified insert SQL line: > > 'create the insert string > sSQL = "INSERT INTO [TimeCardMDJEFF]" > sSQL = sSQL & " ([Man Name], [Job #]," > sSQL = sSQL & " [name], [Date]," > sSQL = sSQL & " Workdate, [Day]," > sSQL = sSQL & " [Hours(ST)], ActualRate)" > sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, """ & r.Fields(1) > sSQL = sSQL & """, """ & r.Fields(2) & """, #" & dteWeekEndDay > sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay > sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";" > > Debug.Print sSQL > > > HTH > -- > Steve S > -------------------------------- > "Veni, Vidi, Velcro" > (I came; I saw; I stuck around.) > > > "babs" wrote: > > > Steve, > > I added the comma after name on both sql - but now getting a different error - > > In title bar of box it says Search error 3075 > > inside - syntax error in query expression '42.05' > > > > I looked it up but can figure out what is wrong??? > > inserted code below for both sSQL > > I had to go out of town and just got back - really want to get this figured > > out today if possible - thanks soo much for helping, > > Barb > > > > '- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] =Last Fri > > sSQL = "SELECT [Man Name], [Job #], [name]," > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate" > > sSQL = sSQL & " FROM TimeCardMDJEFF" > > sSQL = sSQL & " WHERE [Workdate] = #" & dteFri & "#;" > > > > > > > > 'create the insert string > > sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name]," > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate)" > > sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, " & r.Fields(1) > > sSQL = sSQL & ", """ & r.Fields(2) & """, #" & dteWeekEndDay > > sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay > > sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";" > > > > "Steve Sanford" wrote: > > > > > Barb, > > > > > > When you changed the field names in the insert statement, a comma was also > > > removed. A comma must be at the end of this line (after name, but inside the > > > quote): > > > > > > sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name]," > > > > > > Add the last comma, and it should run correctly. > > > > > > > > > > > > > don't get the Insert into code and what the last line(sSQL = sSQL & """, " & > > > > r.Fields(6) & ", " & r.Fields(7) & ";") means???HELP > > > > > > "r" is the record set name and "r.Fields(6)" is the 7th field in the > > > recordset. > > > > > > The record set field numbers are zero based, so the first field is zero, the > > > 2nd field is 1, the third field is 2,........ > > > > > > Using "r.Fields(6)" is a way of getting the data from a field in a record > > > set without knowing the name if the field. Instead of "r.Fields(6)" , > > > I could have used r.Fields("[Hours(ST)]"). > > > > > > In the recordset "r": > > > Field # Your Name > > > ------------------------------------- > > > 0 [Man Name] > > > 1 [Job #] > > > 2 [name] > > > 3 [Date] > > > 4 Workdate > > > 5 [Day] > > > 6 [Hours(ST)] > > > 7 ActualRate > > > > > > > > > > > > HTH > > > -- > > > Steve S > > > -------------------------------- > > > "Veni, Vidi, Velcro" > > > (I came; I saw; I stuck around.) > > > > > > > > > "babs" wrote: > > > > > > > Steve, > > > > Yeah - I know they should use the SS# as PK - but they have been doingthis > > > > for years and seems to be okay. > > > > > > > > I added a new record on main for new weekend date- have people on fri. of > > > > prev. week - added All of your wonderful suggest code to the button on click > > > > event > > > > > > > > I am getting Error on Insert Into - then Done- but no records show up. > > > > a few of the field names- I changed to reflex there name - job is Job > > > > #,job name is Name, ST is HOurs(ST) Not sure but just always add the [ ] > > > > just in case. > > > > > > > > below is the code I have in it- just grab the 2 sections out. I really > > > > don't get the Insert into code and what the last line(sSQL = sSQL & """, " & > > > > r.Fields(6) & ", " & r.Fields(7) & ";") means???HELP > > > > > > > > > > > > > > > > '- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] =Last Fri > > > > sSQL = "SELECT [Man Name], [Job #], [name]," > > > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate" > > > > sSQL = sSQL & " FROM TimeCardMDJEFF" > > > > sSQL = sSQL & " WHERE [Workdate] = #" & dteFri & "#;" > > > > > > > > > > > > 'create the insert string > > > > sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name]" > > > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate)" > > > > sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, " & r.Fields(1) > > > > sSQL = sSQL & ", """ & r.Fields(2) & """, #" & dteWeekEndDay > > > > sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay > > > > sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";" > > > > > > > > > > > > > > > > AGAIN - thanks a ton for helping!! > > > > Barb > > > > "Steve Sanford" wrote: > > > > > > > > > Barb, > > > > > > > > > > If you want to use a list box instead of or in addition to the combo box, > > > > > you could have a multiselect list box that would list the workers. You select > > > > > one or more people, then click on a button. It could look to see if there was > > > > > a record for the previous Fri and add records for the week. If there wasn't a > > > > > record record, it could add a weeks worth of blank records for him. > > > > > > > > > > > > > > > Another question....(I know - but it is important). > > > > > > > > > > What do you do if you have two John Smiths or Jim Jones?? Since you use a > > > > > person's name, how do you tell them apart?? > > > > > > > > > > > > > > > > > > > > HTH > > > > > -- > > > > > Steve S > > > > > -------------------------------- > > > > > "Veni, Vidi, Velcro" > > > > > (I came; I saw; I stuck around.) > > > > > > > > > > > > > > > "babs" wrote: > > > > > > > > > > > Steve, > > > > > > this was what you stated before and this is what I would love - Also I think > > > > > > the Insert 6 record button (code in previous posts)I have on the main form > > > > > > with the man name combo box would take care of inserting the records - for a > > > > > > New man not sched. last week. > > > > > > > > > > > > Your prev. post > > > > > > If I understand correctly, if there were two workers (Perl and Earl), you > > > > > > would want to: > > > > > > > > > > > > 1) look at the previous friday's schedule > > > > > > 2) insert 6 records (Mon - Sat) records for this week's jobs for Perl and > > > > > > 3) insert 6 records (Mon - Sat) records for this week's jobs for Earl > > > > > > > > > > > > > > > > > > If there were 10 workers on the previous friday, you would want 60 records > > > > > > entered?? > > > > > > > > > > > > Thanks sooo much for still helping would love to get it done Before Thurs. > > > > > > if possible. > > > > > > > > > > > > Barb > > > > > > > > > > > > > > > > > > "Steve Sanford" wrote: > > > > > > > > > > > > > Barb, > > > > > > > > > > > > > > > Is the main form recordsource a query? No it is just a table for the Week > > > > > > > > > > > > > > I would recommend using a query for the main form record source. A table is > > > > > > > just a bucket the you put data into; it is not guaranteed to be in any order, > > > > > > > whereas a query is sortable. > > > > > > > > > > > > > > > > > > > > > I have 98% of the code done. I have a pretty good idea of what you want > > > > > > > done, but I don't know *how* you do it. > > > > > > > > > > > > > > Let's say you have one worker "Bob". > > > > > > > Bob worked 9/14 - 9/18/2009. > > > > > > > NO records have been entered for the week 9/21 - 9/25/2009. (Week ending 9/26) > > > > > > > And let's say today is Sun. 9/20/2009. > > > > > > > > > > > > > > So, my questions are: > > > > > > > > > > > > > > - Is 9/27/2009 already entered in the main form? (do you pre-enter sundays > > > > > > > for the year?) > > > > > > > > > > > > > > > > > > > > > - Do you want to select 9/20/2009 in the main form and have the code enter > > > > > > > records for 9/21 - 9/25 (9/27 must already be entered in the main form) > > > > > > > > > > > > > > - Do you want to select 9/20/2009 in the main form and have the code enter > > > > > > > the next sunday date (9/27), then have the code enter the records for the > > > > > > > week 9/21 - 9/25? > > > > > > > > > > > > > > - Do you want to enter 9/27 in the main form, then select it and have the > > > > > > > code enter the new records for 9/21 - 9/25? > > > > > > > > > > > > > > > > > > > > > I'm looking for how you manually add records now. > > > > > > > > > > > > > > HTH > > > > > > > -- > > > > > > > Steve S > > > > > > > -------------------------------- > > > > > > > "Veni, Vidi, Velcro" > > > > > > > (I came; I saw; I stuck around.) > > > > > > > > > > > > > > > > > > > > > "babs" wrote: > > > > > > > > > > > > > > > You have the TimecardMdJeff table correct! > > > > > > > > > > > > > > > > What is the name of the main form table? WeekEndDate > > > > > > > > > > > > > > > > > > > > > > > > Is the main form recordsource a query? No it is just a table for the Week > > > > > > > > End Date - ie. just 52 records for one year. The combo box on the main for > > > > > > > > is not bound and just set up for now to list Available people to schedule > > > > > > > > when click on them - would like to be able to insert their 6 new records > > > > > > > > based on the previous week. > > > > > > > > > > > > > > > > For next post - we schedule Sunday night or very early Sunday morning. > > > > > > > > > > > > > > > > thanks again for helping ! would love some code to help with your above > > > > > > > > ideas - it is really what I would like to have happen. > > > > > > > > Barb > > > > > > > > > > > > > > > > > > > > > > > > "Steve Sanford" wrote: > > > > > > > > > > > > > > > > > OK, now I need to get my head around *when* you add the new week > > > > > > > > > records........ > > > > > > > > > > > > > > > > > > > Here is a sample of a few records for the data - The mainform is just Week > > > > > > > > > > End date so pretty self explanitory there - thinking would have the List box > > > > > > > > > > instead of combo box of who to sched. on that mainform like have now > > > > > > > > > > > > > > > > > > > > For subform > > > > > > > > > > Man Name,Job#,Name(actually job name),Date(which is Week End Date), > > > > > > > > > > > > Workdate,Day,ST(for hours in straight time),OT,DT,ActualRate - there are more > > > > > > > > > > Smith, Rick ;80140020 ;The Legacy at Millennium Park; 9/6/09; 8/31/09;Mon; > > > > > > > > > > 8;other hours for overtime double time are blank, $28.20 > > > > > > > > > > Smith, Rick 90170054 Outdoor Pipe at 1060 Northpoint 9/6/09; 9/1/09 Tues 8 > > > > > > > > > > $28.20 > > > > > > > > > > Smith, Rick 80140020 The Legacy at Millennium Park 9/6/09; 9/2/09Wed 8 > > > > > > > > > > $28.20 > > > > > > > > > > Smith, Rick 80140014 Wheaton College Warrior Dome 9/6/09; 9/3/09Thur 8
From: babs on 30 Sep 2009 17:48 SORRY - It is grabbing the correct Job# and job name(from prev. Fri) and putting the Mon all correct in the immedate window in VB view - just not sure why the ERROR and shouldn't I be seeing the 6 records in the immediate window>??? sorry for all the posts - just trying to get this done. thanks, barb "Steve Sanford" wrote: > Yes, still here. > > When you got the error, what line was highlighted? > > Have you tried using " Debug.Print sSQL" statements after the sSQL lines > to see if the SQL statement are formed correctly? > > When you look at the line in the debug window, the delimiters for field > types are: > > Type Delimiters Example > ------------------------------------------ > strings " " or ' ' "Hi" or 'Hi' > dates # # #1/1/2009# > numbers no delimiters 28.50 > > > Using data you provided, I put the sSQL lines in my code and ran it. The > debug window showed the value for "[Job #]" didn't have quotes around it. > > > Here is the modified insert SQL line: > > 'create the insert string > sSQL = "INSERT INTO [TimeCardMDJEFF]" > sSQL = sSQL & " ([Man Name], [Job #]," > sSQL = sSQL & " [name], [Date]," > sSQL = sSQL & " Workdate, [Day]," > sSQL = sSQL & " [Hours(ST)], ActualRate)" > sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, """ & r.Fields(1) > sSQL = sSQL & """, """ & r.Fields(2) & """, #" & dteWeekEndDay > sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay > sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";" > > Debug.Print sSQL > > > HTH > -- > Steve S > -------------------------------- > "Veni, Vidi, Velcro" > (I came; I saw; I stuck around.) > > > "babs" wrote: > > > Steve, > > I added the comma after name on both sql - but now getting a different error - > > In title bar of box it says Search error 3075 > > inside - syntax error in query expression '42.05' > > > > I looked it up but can figure out what is wrong??? > > inserted code below for both sSQL > > I had to go out of town and just got back - really want to get this figured > > out today if possible - thanks soo much for helping, > > Barb > > > > '- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] =Last Fri > > sSQL = "SELECT [Man Name], [Job #], [name]," > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate" > > sSQL = sSQL & " FROM TimeCardMDJEFF" > > sSQL = sSQL & " WHERE [Workdate] = #" & dteFri & "#;" > > > > > > > > 'create the insert string > > sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name]," > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate)" > > sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, " & r.Fields(1) > > sSQL = sSQL & ", """ & r.Fields(2) & """, #" & dteWeekEndDay > > sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay > > sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";" > > > > "Steve Sanford" wrote: > > > > > Barb, > > > > > > When you changed the field names in the insert statement, a comma was also > > > removed. A comma must be at the end of this line (after name, but inside the > > > quote): > > > > > > sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name]," > > > > > > Add the last comma, and it should run correctly. > > > > > > > > > > > > > don't get the Insert into code and what the last line(sSQL = sSQL & """, " & > > > > r.Fields(6) & ", " & r.Fields(7) & ";") means???HELP > > > > > > "r" is the record set name and "r.Fields(6)" is the 7th field in the > > > recordset. > > > > > > The record set field numbers are zero based, so the first field is zero, the > > > 2nd field is 1, the third field is 2,........ > > > > > > Using "r.Fields(6)" is a way of getting the data from a field in a record > > > set without knowing the name if the field. Instead of "r.Fields(6)" , > > > I could have used r.Fields("[Hours(ST)]"). > > > > > > In the recordset "r": > > > Field # Your Name > > > ------------------------------------- > > > 0 [Man Name] > > > 1 [Job #] > > > 2 [name] > > > 3 [Date] > > > 4 Workdate > > > 5 [Day] > > > 6 [Hours(ST)] > > > 7 ActualRate > > > > > > > > > > > > HTH > > > -- > > > Steve S > > > -------------------------------- > > > "Veni, Vidi, Velcro" > > > (I came; I saw; I stuck around.) > > > > > > > > > "babs" wrote: > > > > > > > Steve, > > > > Yeah - I know they should use the SS# as PK - but they have been doingthis > > > > for years and seems to be okay. > > > > > > > > I added a new record on main for new weekend date- have people on fri. of > > > > prev. week - added All of your wonderful suggest code to the button on click > > > > event > > > > > > > > I am getting Error on Insert Into - then Done- but no records show up. > > > > a few of the field names- I changed to reflex there name - job is Job > > > > #,job name is Name, ST is HOurs(ST) Not sure but just always add the [ ] > > > > just in case. > > > > > > > > below is the code I have in it- just grab the 2 sections out. I really > > > > don't get the Insert into code and what the last line(sSQL = sSQL & """, " & > > > > r.Fields(6) & ", " & r.Fields(7) & ";") means???HELP > > > > > > > > > > > > > > > > '- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] =Last Fri > > > > sSQL = "SELECT [Man Name], [Job #], [name]," > > > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate" > > > > sSQL = sSQL & " FROM TimeCardMDJEFF" > > > > sSQL = sSQL & " WHERE [Workdate] = #" & dteFri & "#;" > > > > > > > > > > > > 'create the insert string > > > > sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], [Job #], [name]" > > > > sSQL = sSQL & " [Date], Workdate, [Day], [Hours(ST)], ActualRate)" > > > > sSQL = sSQL & " VALUES (""" & r.Fields(0) & """, " & r.Fields(1) > > > > sSQL = sSQL & ", """ & r.Fields(2) & """, #" & dteWeekEndDay > > > > sSQL = sSQL & "#, #" & tmpDate & "#, """ & WkDay > > > > sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";" > > > > > > > > > > > > > > > > AGAIN - thanks a ton for helping!! > > > > Barb > > > > "Steve Sanford" wrote: > > > > > > > > > Barb, > > > > > > > > > > If you want to use a list box instead of or in addition to the combo box, > > > > > you could have a multiselect list box that would list the workers. You select > > > > > one or more people, then click on a button. It could look to see if there was > > > > > a record for the previous Fri and add records for the week. If there wasn't a > > > > > record record, it could add a weeks worth of blank records for him. > > > > > > > > > > > > > > > Another question....(I know - but it is important). > > > > > > > > > > What do you do if you have two John Smiths or Jim Jones?? Since you use a > > > > > person's name, how do you tell them apart?? > > > > > > > > > > > > > > > > > > > > HTH > > > > > -- > > > > > Steve S > > > > > -------------------------------- > > > > > "Veni, Vidi, Velcro" > > > > > (I came; I saw; I stuck around.) > > > > > > > > > > > > > > > "babs" wrote: > > > > > > > > > > > Steve, > > > > > > this was what you stated before and this is what I would love - Also I think > > > > > > the Insert 6 record button (code in previous posts)I have on the main form > > > > > > with the man name combo box would take care of inserting the records - for a > > > > > > New man not sched. last week. > > > > > > > > > > > > Your prev. post > > > > > > If I understand correctly, if there were two workers (Perl and Earl), you > > > > > > would want to: > > > > > > > > > > > > 1) look at the previous friday's schedule > > > > > > 2) insert 6 records (Mon - Sat) records for this week's jobs for Perl and > > > > > > 3) insert 6 records (Mon - Sat) records for this week's jobs for Earl > > > > > > > > > > > > > > > > > > If there were 10 workers on the previous friday, you would want 60 records > > > > > > entered?? > > > > > > > > > > > > Thanks sooo much for still helping would love to get it done Before Thurs. > > > > > > if possible. > > > > > > > > > > > > Barb > > > > > > > > > > > > > > > > > > "Steve Sanford" wrote: > > > > > > > > > > > > > Barb, > > > > > > > > > > > > > > > Is the main form recordsource a query? No it is just a table for the Week > > > > > > > > > > > > > > I would recommend using a query for the main form record source. A table is > > > > > > > just a bucket the you put data into; it is not guaranteed to be in any order, > > > > > > > whereas a query is sortable. > > > > > > > > > > > > > > > > > > > > > I have 98% of the code done. I have a pretty good idea of what you want > > > > > > > done, but I don't know *how* you do it. > > > > > > > > > > > > > > Let's say you have one worker "Bob". > > > > > > > Bob worked 9/14 - 9/18/2009. > > > > > > > NO records have been entered for the week 9/21 - 9/25/2009. (Week ending 9/26) > > > > > > > And let's say today is Sun. 9/20/2009. > > > > > > > > > > > > > > So, my questions are: > > > > > > > > > > > > > > - Is 9/27/2009 already entered in the main form? (do you pre-enter sundays > > > > > > > for the year?) > > > > > > > > > > > > > > > > > > > > > - Do you want to select 9/20/2009 in the main form and have the code enter > > > > > > > records for 9/21 - 9/25 (9/27 must already be entered in the main form) > > > > > > > > > > > > > > - Do you want to select 9/20/2009 in the main form and have the code enter > > > > > > > the next sunday date (9/27), then have the code enter the records for the > > > > > > > week 9/21 - 9/25? > > > > > > > > > > > > > > - Do you want to enter 9/27 in the main form, then select it and have the > > > > > > > code enter the new records for 9/21 - 9/25? > > > > > > > > > > > > > > > > > > > > > I'm looking for how you manually add records now. > > > > > > > > > > > > > > HTH > > > > > > > -- > > > > > > > Steve S > > > > > > > -------------------------------- > > > > > > > "Veni, Vidi, Velcro" > > > > > > > (I came; I saw; I stuck around.) > > > > > > > > > > > > > > > > > > > > > "babs" wrote: > > > > > > > > > > > > > > > You have the TimecardMdJeff table correct! > > > > > > > > > > > > > > > > What is the name of the main form table? WeekEndDate > > > > > > > > > > > > > > > > > > > > > > > > Is the main form recordsource a query? No it is just a table for the Week > > > > > > > > End Date - ie. just 52 records for one year. The combo box on the main for > > > > > > > > is not bound and just set up for now to list Available people to schedule > > > > > > > > when click on them - would like to be able to insert their 6 new records > > > > > > > > based on the previous week. > > > > > > > > > > > > > > > > For next post - we schedule Sunday night or very early Sunday morning. > > > > > > > > > > > > > > > > thanks again for helping ! would love some code to help with your above > > > > > > > > ideas - it is really what I would like to have happen. > > > > > > > > Barb > > > > > > > > > > > > > > > > > > > > > > > > "Steve Sanford" wrote: > > > > > > > > > > > > > > > > > OK, now I need to get my head around *when* you add the new week > > > > > > > > > records........ > > > > > > > > > > > > > > > > > > > Here is a sample of a few records for the data - The mainform is just Week > > > > > > > > > > End date so pretty self explanitory there - thinking would have the List box > > > > > > > > > > instead of combo box of who to sched. on that mainform like have now > > > > > > > > > > > > > > > > > > > > For subform > > > > > > > > > > Man Name,Job#,Name(actually job name),Date(which is Week End Date), > > > > > > > > > > > > Workdate,Day,ST(for hours in straight time),OT,DT,ActualRate - there are more > > > > > > > > > > Smith, Rick ;80140020 ;The Legacy at Millennium Park; 9/6/09; 8/31/09;Mon; > > > > > > > > > > 8;other hours for overtime double time are blank, $28.20 > > > > > > > > > > Smith, Rick 90170054 Outdoor Pipe at 1060 Northpoint 9/6/09; 9/1/09 Tues 8 > > > > > > > > > > $28.20 > > > > > > > > > > Smith, Rick 80140020 The Legacy at Millennium Park 9/6/09; 9/2/09Wed 8 > > > > > > > > > > $28.20 > > > > > > > > > > Smith, Rick 80140014 Wheaton College Warrior Dome 9/6/09; 9/3/09Thur 8
From: Hans Up on 30 Sep 2009 22:45 babs wrote: > sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";" > > Debug.Print sSQL > > INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date], > Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE", 1800270, > "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0, 42.05; You need a closing parenthesis for the list of VALUES. Change the last line of your code which creates the insert string to: sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ");" I think the INSERT statement should look like this: INSERT INTO [JeffTime Card MD Query] ([Man Name], [Job #], [name], [Date], Workdate, [Day], [Hours(ST)], ActualRate) VALUES ("BROOKS, CLAIRE", 1800270, "U.S. Post Office, Rockford", #10/4/2009#, #9/28/2009#, "Mon", 0, 42.05); Try pasting it into the SQL View of a new query and let us know whether or not the INSERT is successful.
From: Steve Sanford limbim53 at yahoo dot on 30 Sep 2009 22:26
> window>??? sorry for all the posts - just trying to get this done. > No problems. Did you remove the break point? (he said, clutching at straws). Did you compare the current code you have to the code I originally posted; forgetting the sSQL lines - are all the other lines there? Normally I don't do this, but I need to see your MDB. If you would change/ delete any sensetive data (SSN, Addresses, Phone numbers), do a compact and repair (maybe zip it) and send it to me??? There only needs to be a few records - just enough to test. (I have A2K & A2K3.) -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) |