Autofill subform when move to new record on Main form
in [General]
|
From: Steve Sanford limbim53 at yahoo dot on 24 Sep 2009 18:35 Barb, > Still trying to understand the Insert Into statement to help make it work - > Do the Names of the text boxes in the Subform(tied to TimecardMDJeff) have to > Match the names that is after the Values part of the Insert statement - > when and why use -r.Fields(7) and the others ones like this. No. The SQL "INSERT" inserts the record into the table. In your case, the table is "TimeCardMDJEFF". You would need to requery the subform to see the changes/added records. -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.)
From: babs on 29 Sep 2009 10:18 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 > > > > > > > > $28.20 > > > > > > > > Smith, Rick 80140014 Wheaton College Warrior Dome 9/6/09; 9/4/09Fri 8 > > > > > > > > $28.20 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Given the records above, I think the main form table already has 9/6/2009 > > > > > > > entered. > > > > > > > > > > > > > > So do you want to select 9/6/2009 in the main form, have a new record > > > > > > > entered in the main form with a date of "9/13/2009" , THEN find everyone that > > > > > > > worked on 9/4/2009 and enter records in the table "TimeCardMDJEFF" for the > > > > > > > dates 9/7/2009 thru 9/11/2009? > > > > > > > > > > > > > > > > > > > > > > > > > > > > Which brings up another question: do you want {Mon thru Fri} or {Mon thru > > > > > > > Sat}?? > > > > > > > > > > > > > > > > > > > > > -- > > > > > > > Steve S > > > > > > > -------------------------------- > > > > > > > "Veni, Vidi, Velcro" > > > > > > > (I came; I saw; I stuck around.) > > > > > > >
From: babs on 29 Sep 2009 20:22 Steve, - reposting not sure if you see this or if it fell through the cracks- sorry I just think I am close -thanks to you - not sure of why the error?? 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) & ";" thanks soo much, Barb "Steve Sanford" wrote: > Barb, > > > Still trying to understand the Insert Into statement to help make it work - > > Do the Names of the text boxes in the Subform(tied to TimecardMDJeff) have to > > Match the names that is after the Values part of the Insert statement - > > when and why use -r.Fields(7) and the others ones like this. > > No. The SQL "INSERT" inserts the record into the table. In your case, the > table is "TimeCardMDJEFF". > You would need to requery the subform to see the changes/added records. > > -- > Steve S > -------------------------------- > "Veni, Vidi, Velcro" > (I came; I saw; I stuck around.) > >
From: Steve Sanford limbim53 at yahoo dot on 30 Sep 2009 15:19 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 > > > > > > > > > $28.20 > > > > > > > > > Smith, Rick 80140014 Wheaton College Warrior Dome 9/6/09; 9/4/09Fri 8 > > > > > > > > > $28.20 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Given the records above, I think the main form table already has 9/6/2009 > > > > > > > > entered. > > > > > > > > > > > > > > > > So do you want to select 9/6/2009 in the main form, have a new record > > > > > > > > entered in the main form with a date of "9/13/2009" , THEN find everyone that > > > > > > > > worked on 9/4/2009 and enter records in the table "TimeCardMDJEFF" for the > > > > > > > > dates 9/7/2009 thru 9/11/2009? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Which brings up another question: do you want {Mon thru Fri} or {Mon thru > > > > > > > > Sat}?? > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > > > > Steve S > > > > > > > > -------------------------------- > > > > > > > > "Veni, Vidi, Velcro" > > > > > > > > (I came; I saw; I stuck around.) > > > > > > > >
From: babs on 30 Sep 2009 17:27
Steve, I pasted the code you posted in and changed the table now to the query that is now tied to the subform(recordsource)- but I am getting the EXACT SAme error Search error 3075 inside - syntax error in query expression '42.05' not sure on where the debug print should show up??? I tried a toggle break and pointed to the code and basically seems okay??? not sure why the error and no records added ?? thanks for still helpingBarb '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 "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 |