Autofill subform when move to new record on Main form
in [General]
|
From: Steve Sanford limbim53 at yahoo dot on 23 Sep 2009 12:58 Barb, <unasked for advice> Read this site, especially #3...... no spaces/ naming convention http://mvps.org/access/tencommandments.htm A naming convention will keep you from using reserved words (ex. "Date" & "Day") as names for Access objects. See: http://allenbrowne.com/AppIssueBadWord.html You should read up on normalization. The table "TimeCardMDJEFF" is in need of being normalized. See: http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101 Here is Crystal's Access Basics: http://www.allenbrowne.com/casu-22.html </unasked for advice> OK, here is what the code does. In your main form you select a date (that is a Sunday), then click a button, and all of the employees that worked on the preceding Friday will have records for this week (Mon - Sat). Example: if a group of workers worked 9/7 - 9/12 (week ending 9/13), and *TODAY* is Sunday 9/13, to enter records for the week 9/14 - 9/19, in the main form, you would select *9/20/2009*, then click on the "ADD NEW" button. The code will look back at Fri 9/11, and create 6 records for each worker for the dates 9/14 - 9/19/2009. !!!!!!!!!!!!!!!!!!!!!!!!!!!! DO THE FOLLOWING ON A COPY OF YOUR MDB!!! When/if you think it is doing what you want, put it to your production mdb. !!!!!!!!!!!!!!!!!!!!!!!!!!!! The first two line of every code page should have: Option Compare Database Option Explicit Add a new button on the main form, name it something like "AddNEW", and paste the following code in the click event: '---------------------------------- Private Sub AutoFillNewRecord_Click() On Error GoTo Err_HandleError 'number of days to add 'change to 5 if you want Mon - Fri Const intNumDays As Integer = 6 Dim d As DAO.Database Dim r As DAO.Recordset Dim dteWeekEndDay As Date ' week ending date Dim dteFri As Date ' last friday date Dim dteMon As Date ' next monday date Dim tmpDate As Date Dim i As Integer ' loop counter Dim sSQL As String Dim WkDay As String ' saves record in main form If Me.Dirty Then Me.Dirty = False End If Set d = CurrentDb '- Get the new week ending date (store in a variable) dteWeekEndDay = Me.txtDate If Weekday(dteWeekEndDay) <> vbSunday Then MsgBox "Selected date is not a Sunday. Please check the date and try again." Exit Sub End If 'get the previous Sunday date dteFri = DateAdd("d", -7, dteWeekEndDay) '- calculate the last Fri date Do Until Weekday(dteFri) = vbFriday dteFri = DateAdd("d", -1, dteFri) Loop '- calculate the next Monday date (from the last Fri date) dteMon = DateAdd("d", 3, dteFri) '- open a recordset based on the table "TimeCardMDJEFF" WHERE [date] = Last Fri sSQL = "SELECT [Man Name], Job, [job name]," sSQL = sSQL & " [Date], Workdate, [Day], ST, ActualRate" sSQL = sSQL & " FROM TimeCardMDJEFF" sSQL = sSQL & " WHERE [Workdate] = #" & dteFri & "#;" ' Debug.Print sSQL Set r = d.OpenRecordset(sSQL) '- check for records in recordset. ' (there should be one Fri record per worker) ' -If no records, exit sub. If r.BOF And r.EOF Then MsgBox "ERROR! No records found for the week ending Fri " & dteFri Else r.MoveLast r.MoveFirst ' MsgBox r.RecordCount 'loop thru the recordset Do While Not r.EOF tmpDate = dteMon '- step thru the recordset, inserting 5 or 6 records for each worker, incrementing 'the Workdate for each new record. For i = 1 To intNumDays '(Monday to Saturday) Select Case Weekday(tmpDate) Case 1 WkDay = "Sun" Case 2 WkDay = "Mon" Case 3 WkDay = "Tue" Case 4 WkDay = "Wed" Case 5 WkDay = "Thu" Case 6 WkDay = "Fri" Case 7 WkDay = "Sat" End Select 'cerate the insert string sSQL = "INSERT INTO [TimeCardMDJEFF] ([Man Name], Job, [job name]," sSQL = sSQL & " [Date], Workdate, [Day], 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 'increment day tmpDate = DateAdd("d", 1, tmpDate) 'insert the record d.Execute sSQL, dbFailOnError Next i r.MoveNext Loop End If Exit_HandleError: On Error Resume Next 'clean up r.Close Set r = Nothing Set d = Nothing MsgBox "Done" Exit Sub Err_HandleError: Select Case Err.Number Case 3021 MsgBox "Help" Case Else MsgBox Err.Description, vbExclamation, "Search Error " & Err.Number End Select Resume Exit_HandleError End Sub '---------------------------------- HTH -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "babs" wrote: > > <- Is 9/27/2009 already entered in the main form? (do you pre-enter sundays > for the year?) > > They can be pre entered but for now they are not(actually not using this yet > - still developing obviously) > It is all in Excel for now - each worksheet Tab is a new Week end - the user > just copies and pastes the last job done from previous week and pastes it > into the new worksheet for new Week End. > > <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? > > This is what the code is doing now - when 9/27/09 for week End (puts in > 9/21-9/26)is entered on the main form and they click on the Add 6 records > button from the Main button- see Previous posts it - it grabs the man name > they have in the drop down list on main and add the 6 records with the date > for that one person -(like what you said on previous post) -to have > option(maybe one button for Add all who were scheduled prev week(Fri-or > anyday last week), and have the option also to add additional people with the > dropdown (like presently doing) > > thanks soo much ! > 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: Steve Sanford limbim53 at yahoo dot on 23 Sep 2009 13:15 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 23 Sep 2009 13:55 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 24 Sep 2009 08:29 Steve, 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. I really don't get the Insert into code and what the last line(sSQL = sSQL & """, " & r.Fields(6) & ", " & r.Fields(7) & ";") means???HELP see prev. post also, thanks soooo much, 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: Steve Sanford limbim53 at yahoo dot on 24 Sep 2009 18:31
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.) > > > > > > |