Autofill subform when move to new record on Main form
in [General]
|
From: babs on 16 Sep 2009 11:40 below is code that I used on a different database that worked well to automatically populate the new record based on values inprevious record but it was only a single form: Function AutoFillNewRecord(F As Form) Dim rs As DAO.Recordset, C As Control Dim FillFields As String, FillAllFields As Integer 'On Error Resume Next 'Exit if not on the new record. If Not F.NewRecord Then Exit Function 'Goto the last record of the form recordset(to auofill form). Set rs = F.RecordsetClone rs.MoveLast 'Exit if you cannot move to the last record(no records). If Err <> 0 Then Exit Function 'Get the list of fields to autofill. FillFields = ";" & F![AutoFillNewRecordFields] & ";" 'If there is no criteria field, then set flag indicating ALL 'fields should be autofilled. FillAllFields = Err <> 0 F.Painting = False 'Visit each field on the form. For Each C In F 'Fill The field if All fields are to be filled Or if the '....ControlSource field can be found in the Fill Fields list. If FillAllFields Or InStr(FillFields, ";" & (C.Name) & ";") > 0 Then C = rs(C.ControlSource) End If Next F.Painting = True End Function With the new database I have a Main form (for the Week End Date)and schedule of jobs for that given week in the Subform. ex. possible 8 employees list 6times in subform for Mon-Sat. jobs When navigate to new record(new week) [or Prob. should tie to a command button- to insert records} on Main form I want to autofill the new subform with Each persons([man]) previous record from the Fri([day]-field name). Insert Mon-Fri for all previous sched. people -prior record and grab job name they were on that Fri - Since sat. jobs are odd jobs and 9Xout of 10 the new week schedules is usually where they left off on Fri. - I currently have a command button with the following code but it only inserts the six new records based on who is selected in the man drop down list - would like it to grab the job from the previous record - Friday.???any ideas??? thanks, barb
From: Steve Sanford limbim53 at yahoo dot on 16 Sep 2009 21:08 Hi Barb, > I currently have a command button with the following code but it only > inserts the six new records based on who is selected in the man drop down > list - would like it to grab the job from the previous record - Friday.???any > ideas??? There doesn't appear to be any "following code"??? 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?? -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.)
From: babs on 17 Sep 2009 09:56 Sorry here is the code attached to the command button I was referring to:-know shouldn't use date as a field but I inhereted this and it is many places so prefer not to change Private Sub Command5_Click() 'START Dim dteWeedday As Date Dim i As Integer Dim strSQL As String DoCmd.SetWarnings False ' run your code For i = 1 To 6 '(Monday to Saturday) dteWeekday = DateAdd("d", Me.txtDate, -i) 'increment sunday by i days strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate],[date],[man name],[actualRate]) VALUES (#" & Format(dteWeekday, "m-d-yyyy") & "#," & "#" & Format(Me.Date, "m-d-yyyy") & "#,""" & Me.cboman.Column(0) & """,""" & Me.cboman.Column(1) & """)" DoCmd.RunSQL (strSQL) Next i 'END Me.JeffTimeCardMDJEFFSubform.Requery 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 YES - you got it!! > If there were 10 workers on the previous friday, you would want 60 records > entered?? Woudl Love to have the option to Insert each employees prev. Fri job(new 6 records) AND/OR do Entire previous week schedule-All last week schedule (is On record on main form based on week end date)- subform list who is all scheduled for what in that week. thanks sooo much for your help, barb "Steve Sanford" wrote: > Hi Barb, > > > I currently have a command button with the following code but it only > > inserts the six new records based on who is selected in the man drop down > > list - would like it to grab the job from the previous record - Friday.???any > > ideas??? > > There doesn't appear to be any "following code"??? > > > 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?? > > -- > Steve S > -------------------------------- > "Veni, Vidi, Velcro" > (I came; I saw; I stuck around.) > >
From: Douglas J. Steele on 17 Sep 2009 10:09 From your Insert statement,it looks as though you have separate tables for each employee ([TimeCardMDJEFF]). If so, you should merge them all into the same table. Also, your use of the DateAdd function is technically backwards. The syntax is DateAdd(interval, number, date) so you should have: dteWeekday = DateAdd("d", -i, Me.txtDate) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "babs" <babs(a)discussions.microsoft.com> wrote in message news:D1562236-E4C6-4810-8CA0-75DA21FAC00F(a)microsoft.com... > > Sorry here is the code attached to the command button I was referring > to:-know shouldn't use date as a field but I inhereted this and it is many > places so prefer not to change > > Private Sub Command5_Click() > 'START > Dim dteWeedday As Date > Dim i As Integer > Dim strSQL As String > > DoCmd.SetWarnings False > ' run your code > For i = 1 To 6 '(Monday to Saturday) > dteWeekday = DateAdd("d", Me.txtDate, -i) 'increment sunday by i days > strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate],[date],[man > name],[actualRate]) VALUES (#" & Format(dteWeekday, "m-d-yyyy") & "#," & > "#" > & Format(Me.Date, "m-d-yyyy") & "#,""" & Me.cboman.Column(0) & """,""" & > Me.cboman.Column(1) & """)" > DoCmd.RunSQL (strSQL) > Next i > 'END > > Me.JeffTimeCardMDJEFFSubform.Requery > > > 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 > YES - you got it!! > >> If there were 10 workers on the previous friday, you would want 60 >> records >> entered?? > Woudl Love to have the option to Insert each employees prev. Fri job(new 6 > records) AND/OR do Entire previous week schedule-All last week schedule > (is > On record on main form based on week end date)- subform list who is all > scheduled for what in that week. > > thanks sooo much for your help, > barb > > > "Steve Sanford" wrote: > >> Hi Barb, >> >> > I currently have a command button with the following code but it only >> > inserts the six new records based on who is selected in the man drop >> > down >> > list - would like it to grab the job from the previous record - >> > Friday.???any >> > ideas??? >> >> There doesn't appear to be any "following code"??? >> >> >> 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?? >> >> -- >> Steve S >> -------------------------------- >> "Veni, Vidi, Velcro" >> (I came; I saw; I stuck around.) >> >>
From: babs on 17 Sep 2009 11:10 I only have One table for the employees schedule it is the [TimeCardMDJEFF]- It is grabbing items selected FOR NOW of the combo man box in the Main form and inserting it into the new records for that given week. For some reason the DateAdd is working All tied to the command button is working see previous post - just want it be able to grab what job they were on the previous week .......... see above post for more explanation thanks sooo much, barb "Douglas J. Steele" wrote: > From your Insert statement,it looks as though you have separate tables for > each employee ([TimeCardMDJEFF]). If so, you should merge them all into the > same table. > > Also, your use of the DateAdd function is technically backwards. The syntax > is > > DateAdd(interval, number, date) > > so you should have: > > dteWeekday = DateAdd("d", -i, Me.txtDate) > > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "babs" <babs(a)discussions.microsoft.com> wrote in message > news:D1562236-E4C6-4810-8CA0-75DA21FAC00F(a)microsoft.com... > > > > Sorry here is the code attached to the command button I was referring > > to:-know shouldn't use date as a field but I inhereted this and it is many > > places so prefer not to change > > > > Private Sub Command5_Click() > > 'START > > Dim dteWeedday As Date > > Dim i As Integer > > Dim strSQL As String > > > > DoCmd.SetWarnings False > > ' run your code > > For i = 1 To 6 '(Monday to Saturday) > > dteWeekday = DateAdd("d", Me.txtDate, -i) 'increment sunday by i days > > strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate],[date],[man > > name],[actualRate]) VALUES (#" & Format(dteWeekday, "m-d-yyyy") & "#," & > > "#" > > & Format(Me.Date, "m-d-yyyy") & "#,""" & Me.cboman.Column(0) & """,""" & > > Me.cboman.Column(1) & """)" > > DoCmd.RunSQL (strSQL) > > Next i > > 'END > > > > Me.JeffTimeCardMDJEFFSubform.Requery > > > > > > 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 > > YES - you got it!! > > > >> If there were 10 workers on the previous friday, you would want 60 > >> records > >> entered?? > > Woudl Love to have the option to Insert each employees prev. Fri job(new 6 > > records) AND/OR do Entire previous week schedule-All last week schedule > > (is > > On record on main form based on week end date)- subform list who is all > > scheduled for what in that week. > > > > thanks sooo much for your help, > > barb > > > > > > "Steve Sanford" wrote: > > > >> Hi Barb, > >> > >> > I currently have a command button with the following code but it only > >> > inserts the six new records based on who is selected in the man drop > >> > down > >> > list - would like it to grab the job from the previous record - > >> > Friday.???any > >> > ideas??? > >> > >> There doesn't appear to be any "following code"??? > >> > >> > >> 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?? > >> > >> -- > >> Steve S > >> -------------------------------- > >> "Veni, Vidi, Velcro" > >> (I came; I saw; I stuck around.) > >> > >> > > >
|
Next
|
Last
Pages: 1 2 3 4 5 6 7 Prev: Paste Append into Access 2007 from Excel 2007 Next: Access 2007 - Run Time error '2114': |