Autofill subform when move to new record on Main form
in [General]
|
From: Steve Sanford limbim53 at yahoo dot on 17 Sep 2009 12:11 Not quite sure what you mean by this: > 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. Examples??? Based on Doug's post, what are your tables and relationships (the main form table/ subform table)? Do you have a table for each employee? What is the record source (SQL) for the main form? What is the record source (SQL) for the combo box? Do you have a table of employees? It looks like the person's name is entered into the record. Is the field [actualRate] a text field or a number? In your code, I don't see a field for the job. How do you know what job they were doing on last Fri.? -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.)
From: babs on 17 Sep 2009 14:47 <What is the record source (SQL) for the main form The Main form record Source is a table Jeff Week End table - with Only [Date] which is the Week End Date so - (so would have for one year would have 52 records -weeks on main form) <What is the record source (SQL) for the combo box? I have on the Main form a combo box for Man name called cboman- yes that I have the recordsource from the qry current employees (any better idea-here?) On subform see all people that are sched for the 6 days M-Sat. for which jobs and # hours per day.- also with new week - can click on command button for now to insert 6 records to who is picked in combo box - would like the option to grab job#, & name from previous friday.- to fill new week 6 records The main form and subform are Linked by the [Date] (week end date) The subforms record source it the Table TimeCardMDJEFF The fields are: 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 fields but these are most impt. <Do you have a table for each employee? No- the list is in the qry currently employed <Do you have a table of employees? It looks like the person's name is entered into the record. Yes the qry of current employess - persons name for now is entered based on what is picked in combo box on main -when click on Insert 6 rec button In subform may need to ind. add a new record - would like drop down there also> <Is the field [actualRate] a text field or a number? It is a currency field <In your code, I don't see a field for the job. How do you know what job they were doing on last Fri.? there is a field job# and name(jobname) in the subform - and for now it is a drop down (with record source - qry currentjobs) Hope this clarifies things - thanks for helping a ton!!! BArb "Steve Sanford" wrote: > > Not quite sure what you mean by this: > > > 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. > > Examples??? > > Based on Doug's post, what are your tables and relationships (the main form > table/ subform table)? Do you have a table for each employee? > > What is the record source (SQL) for the main form? > What is the record source (SQL) for the combo box? > Do you have a table of employees? It looks like the person's name is entered > into the record. > > Is the field [actualRate] a text field or a number? > In your code, I don't see a field for the job. How do you know what job they > were doing on last Fri.? > > > -- > Steve S > -------------------------------- > "Veni, Vidi, Velcro" > (I came; I saw; I stuck around.) >
From: Steve Sanford limbim53 at yahoo dot on 17 Sep 2009 18:51 babs, Since I can't see your database, I have to ask questions to learn about the structure and relationships. Asking about SQL for forms and combo boxes helps me to know actual table, field and control names. Examples of inserted records helps ensure I am going in the right direction. Without this info and examples, I will describe how I would procede. There are many posters that might be able to do this using a query.. but I am not one of them (yet). So, on a button click, I would: - Get the new week ending date (store in a variable) - calculate the last Fri date - calculate the next Monday date (from the last Fri date) - open a recordset based on the table "TimeCardMDJEFF" WHERE [date] = Last Fri - check for records in recordset. If no records, exit sub. (this will be one record per worker) - step thru the recordset, inserting 6 records for each worker, incrementing the Workdate for each new record. - close the recordset. Or you could open a form with a list box of workers. With the list box set to multiselect, you could select the workers you want to have new records added from the previous Fri. The code would be like the above code, but anyone that was selected in the list box that had NOT worked on the previous Fri, would still have 6 records inserted, but the Job number and related data would be blank. -------- There is a "Find and Replace" tool that would help you rename objects (like [date] to [WkEndDate]) written by Rick Fisher at http://www.rickworld.com/download.html It is shareware good for 30 days, after which the registration cost is $37 per copy. Very reasonable. -------- HTH -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "babs" wrote: > <What is the record source (SQL) for the main form > The Main form record Source is a table Jeff Week End table - with Only > [Date] which is the Week End Date so - (so would have for one year would have > 52 records -weeks on main form) > <What is the record source (SQL) for the combo box? > I have on the Main form a combo box for Man name called cboman- yes that I > have the recordsource from the qry current employees (any better idea-here?) > > On subform see all people that are sched for the 6 days M-Sat. for which > jobs and # hours per day.- also with new week - can click on command button > for now to insert 6 records to who is picked in combo box - would like the > option to grab job#, & name from previous friday.- to fill new week 6 records > > The main form and subform are Linked by the [Date] (week end date) > The subforms record source it the Table TimeCardMDJEFF The fields are: > 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 > fields but these are most impt. > > <Do you have a table for each employee? > No- the list is in the qry currently employed > > > <Do you have a table of employees? It looks like the person's name is entered > into the record. > Yes the qry of current employess - persons name for now is entered based on > what is picked in combo box on main -when click on Insert 6 rec button > In subform may need to ind. add a new record - would like drop down there > also> > > <Is the field [actualRate] a text field or a number? > It is a currency field > > <In your code, I don't see a field for the job. How do you know what job they > were doing on last Fri.? > there is a field job# and name(jobname) in the subform - and for now it is a > drop down (with record source - qry currentjobs) > > > Hope this clarifies things - thanks for helping a ton!!! > BArb > "Steve Sanford" wrote: > > > > > Not quite sure what you mean by this: > > > > > 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. > > > > Examples??? > > > > Based on Doug's post, what are your tables and relationships (the main form > > table/ subform table)? Do you have a table for each employee? > > > > What is the record source (SQL) for the main form? > > What is the record source (SQL) for the combo box? > > Do you have a table of employees? It looks like the person's name is entered > > into the record. > > > > Is the field [actualRate] a text field or a number? > > In your code, I don't see a field for the job. How do you know what job they > > were doing on last Fri.? > > > > > > -- > > Steve S > > -------------------------------- > > "Veni, Vidi, Velcro" > > (I came; I saw; I stuck around.) > >
From: babs on 18 Sep 2009 09:55 Steve, I really like the idea of the listbox to grab who we should sched for that week and EVERYTHING else you said! One problem is I am not very strong at VB code with Records set???- get it for grabbing values from drop down boxes columns and doing calculations 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 Additional employees(man name) and would schedule them for week end 9/6/09 Any code that you can help me with for adding the new records and grabbing (example of code) prev. fri job would be GREAT! thanks again so much for help! Barb "Steve Sanford" wrote: > babs, > > Since I can't see your database, I have to ask questions to learn about the > structure and relationships. Asking about SQL for forms and combo boxes helps > me to know actual table, field and control names. Examples of inserted > records helps ensure I am going in the right direction. > > Without this info and examples, I will describe how I would procede. There > are many posters that might be able to do this using a query.. but I am not > one of them (yet). > > So, on a button click, I would: > > - Get the new week ending date (store in a variable) > - calculate the last Fri date > - calculate the next Monday date (from the last Fri date) > - open a recordset based on the table "TimeCardMDJEFF" WHERE [date] = Last Fri > - check for records in recordset. If no records, exit sub. > (this will be one record per worker) > - step thru the recordset, inserting 6 records for each worker, incrementing > the Workdate for each new record. > - close the recordset. > > > Or you could open a form with a list box of workers. With the list box set > to multiselect, you could select the workers you want to have new records > added from the previous Fri. The code would be like the above code, but > anyone that was selected in the list box that had NOT worked on the previous > Fri, would still have 6 records inserted, but the Job number and related data > would be blank. > > > > -------- > There is a "Find and Replace" tool that would help you rename objects (like > [date] to [WkEndDate]) written by Rick Fisher at > > http://www.rickworld.com/download.html > > It is shareware good for 30 days, after which the registration cost is $37 > per copy. Very reasonable. > -------- > > > HTH > -- > Steve S > -------------------------------- > "Veni, Vidi, Velcro" > (I came; I saw; I stuck around.) > > > "babs" wrote: > > > <What is the record source (SQL) for the main form > > The Main form record Source is a table Jeff Week End table - with Only > > [Date] which is the Week End Date so - (so would have for one year would have > > 52 records -weeks on main form) > > <What is the record source (SQL) for the combo box? > > I have on the Main form a combo box for Man name called cboman- yes that I > > have the recordsource from the qry current employees (any better idea-here?) > > > > On subform see all people that are sched for the 6 days M-Sat. for which > > jobs and # hours per day.- also with new week - can click on command button > > for now to insert 6 records to who is picked in combo box - would like the > > option to grab job#, & name from previous friday.- to fill new week 6 records > > > > The main form and subform are Linked by the [Date] (week end date) > > The subforms record source it the Table TimeCardMDJEFF The fields are: > > 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 > > fields but these are most impt. > > > > <Do you have a table for each employee? > > No- the list is in the qry currently employed > > > > > > <Do you have a table of employees? It looks like the person's name is entered > > into the record. > > Yes the qry of current employess - persons name for now is entered based on > > what is picked in combo box on main -when click on Insert 6 rec button > > In subform may need to ind. add a new record - would like drop down there > > also> > > > > <Is the field [actualRate] a text field or a number? > > It is a currency field > > > > <In your code, I don't see a field for the job. How do you know what job they > > were doing on last Fri.? > > there is a field job# and name(jobname) in the subform - and for now it is a > > drop down (with record source - qry currentjobs) > > > > > > Hope this clarifies things - thanks for helping a ton!!! > > BArb > > "Steve Sanford" wrote: > > > > > > > > Not quite sure what you mean by this: > > > > > > > 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. > > > > > > Examples??? > > > > > > Based on Doug's post, what are your tables and relationships (the main form > > > table/ subform table)? Do you have a table for each employee? > > > > > > What is the record source (SQL) for the main form? > > > What is the record source (SQL) for the combo box? > > > Do you have a table of employees? It looks like the person's name is entered > > > into the record. > > > > > > Is the field [actualRate] a text field or a number? > > > In your code, I don't see a field for the job. How do you know what job they > > > were doing on last Fri.? > > > > > > > > > -- > > > Steve S > > > -------------------------------- > > > "Veni, Vidi, Velcro" > > > (I came; I saw; I stuck around.) > > >
From: Steve Sanford limbim53 at yahoo dot on 18 Sep 2009 16:10
babs, Is this right?? In table "TimeCardMDJEFF" Field Type ---------------------- Man Name string Job string job name string Date date Workdate date Day string ST single ActualRate single What is the name of the main form table? Is the main form recordsource a query? -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "babs" wrote: > Steve, > I really like the idea of the listbox to grab who we should sched for that > week and EVERYTHING else you said! One problem is I am not very strong at VB > code with Records set???- get it for grabbing values from drop down boxes > columns and doing calculations > > > 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 > > > Additional employees(man name) and would schedule them for week end 9/6/09 > > Any code that you can help me with for adding the new records and grabbing > (example of code) prev. fri job would be GREAT! > > thanks again so much for help! > Barb > > "Steve Sanford" wrote: > > > babs, > > > > Since I can't see your database, I have to ask questions to learn about the > > structure and relationships. Asking about SQL for forms and combo boxes helps > > me to know actual table, field and control names. Examples of inserted > > records helps ensure I am going in the right direction. > > > > Without this info and examples, I will describe how I would procede. There > > are many posters that might be able to do this using a query.. but I am not > > one of them (yet). > > > > So, on a button click, I would: > > > > - Get the new week ending date (store in a variable) > > - calculate the last Fri date > > - calculate the next Monday date (from the last Fri date) > > - open a recordset based on the table "TimeCardMDJEFF" WHERE [date] = Last Fri > > - check for records in recordset. If no records, exit sub. > > (this will be one record per worker) > > - step thru the recordset, inserting 6 records for each worker, incrementing > > the Workdate for each new record. > > - close the recordset. > > > > > > Or you could open a form with a list box of workers. With the list box set > > to multiselect, you could select the workers you want to have new records > > added from the previous Fri. The code would be like the above code, but > > anyone that was selected in the list box that had NOT worked on the previous > > Fri, would still have 6 records inserted, but the Job number and related data > > would be blank. > > > > > > > > -------- > > There is a "Find and Replace" tool that would help you rename objects (like > > [date] to [WkEndDate]) written by Rick Fisher at > > > > http://www.rickworld.com/download.html > > > > It is shareware good for 30 days, after which the registration cost is $37 > > per copy. Very reasonable. > > -------- > > > > > > HTH > > -- > > Steve S > > -------------------------------- > > "Veni, Vidi, Velcro" > > (I came; I saw; I stuck around.) > > > > > > "babs" wrote: > > > > > <What is the record source (SQL) for the main form > > > The Main form record Source is a table Jeff Week End table - with Only > > > [Date] which is the Week End Date so - (so would have for one year would have > > > 52 records -weeks on main form) > > > <What is the record source (SQL) for the combo box? > > > I have on the Main form a combo box for Man name called cboman- yes that I > > > have the recordsource from the qry current employees (any better idea-here?) > > > > > > On subform see all people that are sched for the 6 days M-Sat. for which > > > jobs and # hours per day.- also with new week - can click on command button > > > for now to insert 6 records to who is picked in combo box - would like the > > > option to grab job#, & name from previous friday.- to fill new week 6 records > > > > > > The main form and subform are Linked by the [Date] (week end date) > > > The subforms record source it the Table TimeCardMDJEFF The fields are: > > > 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 > > > fields but these are most impt. > > > > > > <Do you have a table for each employee? > > > No- the list is in the qry currently employed > > > > > > > > > <Do you have a table of employees? It looks like the person's name is entered > > > into the record. > > > Yes the qry of current employess - persons name for now is entered based on > > > what is picked in combo box on main -when click on Insert 6 rec button > > > In subform may need to ind. add a new record - would like drop down there > > > also> > > > > > > <Is the field [actualRate] a text field or a number? > > > It is a currency field > > > > > > <In your code, I don't see a field for the job. How do you know what job they > > > were doing on last Fri.? > > > there is a field job# and name(jobname) in the subform - and for now it is a > > > drop down (with record source - qry currentjobs) > > > > > > > > > Hope this clarifies things - thanks for helping a ton!!! > > > BArb > > > "Steve Sanford" wrote: > > > > > > > > > > > Not quite sure what you mean by this: > > > > > > > > > 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. > > > > > > > > Examples??? > > > > > > > > Based on Doug's post, what are your tables and relationships (the main form > > > > table/ subform table)? Do you have a table for each employee? > > > > > > > > What is the record source (SQL) for the main form? > > > > What is the record source (SQL) for the combo box? > > > > Do you have a table of employees? It looks like the person's name is entered > > > > into the record. > > > > > > > > Is the field [actualRate] a text field or a number? > > > > In your code, I don't see a field for the job. How do you know what job they > > > > were doing on last Fri.? > > > > > > > > > > > > -- > > > > Steve S > > > > -------------------------------- > > > > "Veni, Vidi, Velcro" > > > > (I came; I saw; I stuck around.) > > > > |