Insert/create new records using a loop
in [Help]
|
From: Carl on 22 Jul 2010 07:36 I have a couple of tables called tbl_EMPLYE (has EMPLYE_ID as autonumber PK) and tbl_TIMESHEET (has TIMESHEET_ID as autonumber PK, and EMPLYE_ID as FK) based on a one to many relationship. These use a form and subform where the form is based on tbl_EMPLYE and has a combo box based on employee. The subform is based on tbl_TIMESHEET and the two are linked by the EMPLYE_ID column. The frm_EMPLYE displays the Employee number and the Start Date they started a placement (always a Monday date). The frm_TIMESHEET displays a Week ending date (always a Sunday) along with a few other fields in a tabulated layout. What I need is a command button on the EMPLYE form that will create 26 records (timesheets) in the tbl_TIMESHEET table where each week ending date displays accordingly. For example. If the Start Date on the main form (frm_EMPLYE) is 19- JUL-2010 (a Monday date), I want 26 records in the subform to show (showing Sunday dates): 25-JUL-2010 01-AUG-2010 08-AUG-2010 etc.. What I then need is for the command button to be disabled on the frm_EMPLYE form for each employee that has had the 26 records added (would this be used in conjunction with a flag or something????) Any help would be greatly appreciated (or alternative solution) Cheers.
From: paii, Ron on 22 Jul 2010 08:12 In the OnCurrent event, check if the employee has a record in tbl_TIMESHEET and enable/disable the button "Carl" <carl.barrett(a)newcastle.gov.uk> wrote in message news:1c0a218e-1433-4fe0-ae01-9d75207e8052(a)t10g2000yqg.googlegroups.com... > I have a couple of tables called tbl_EMPLYE (has EMPLYE_ID as > autonumber PK) and tbl_TIMESHEET (has TIMESHEET_ID as autonumber PK, > and EMPLYE_ID as FK) based on a one to many relationship. These use a > form and subform where the form is based on tbl_EMPLYE and has a combo > box based on employee. The subform is based on tbl_TIMESHEET and the > two are linked by the EMPLYE_ID column. > > The frm_EMPLYE displays the Employee number and the Start Date they > started a placement (always a Monday date). > > The frm_TIMESHEET displays a Week ending date (always a Sunday) along > with a few other fields in a tabulated layout. > > What I need is a command button on the EMPLYE form that will create 26 > records (timesheets) in the tbl_TIMESHEET table where each week ending > date displays accordingly. > > For example. If the Start Date on the main form (frm_EMPLYE) is 19- > JUL-2010 (a Monday date), I want 26 records in the subform to show > (showing Sunday dates): > > 25-JUL-2010 > 01-AUG-2010 > 08-AUG-2010 > > etc.. > > What I then need is for the command button to be disabled on the > frm_EMPLYE form for each employee that has had the 26 records added > (would this be used in conjunction with a flag or something????) > > Any help would be greatly appreciated (or alternative solution) > > Cheers.
From: XPS35 on 22 Jul 2010 09:30 Carl wrote: > I have a couple of tables called tbl_EMPLYE (has EMPLYE_ID as > autonumber PK) and tbl_TIMESHEET (has TIMESHEET_ID as autonumber PK, > and EMPLYE_ID as FK) based on a one to many relationship. These use a > form and subform where the form is based on tbl_EMPLYE and has a combo > box based on employee. The subform is based on tbl_TIMESHEET and the > two are linked by the EMPLYE_ID column. > > The frm_EMPLYE displays the Employee number and the Start Date they > started a placement (always a Monday date). > > The frm_TIMESHEET displays a Week ending date (always a Sunday) along > with a few other fields in a tabulated layout. > > What I need is a command button on the EMPLYE form that will create 26 > records (timesheets) in the tbl_TIMESHEET table where each week ending > date displays accordingly. > > For example. If the Start Date on the main form (frm_EMPLYE) is 19- > JUL-2010 (a Monday date), I want 26 records in the subform to show > (showing Sunday dates): > > 25-JUL-2010 > 01-AUG-2010 > 08-AUG-2010 > > etc.. > > What I then need is for the command button to be disabled on the > frm_EMPLYE form for each employee that has had the 26 records added > (would this be used in conjunction with a flag or something????) > > Any help would be greatly appreciated (or alternative solution) > > Cheers. The commands for your command button would be something like: Private Sub Sheets_Click() Dim WeekCount As Byte Dim EndDate As Date EndDate = DateAdd("d", 6, Me.EMPLYE_StartDate) For WeekCount = 1 To 26 DoCmd.RunSQL "INSERT INTO TIMESHEET(EMPLYE_ID,WeekEndingDate) " & _ "VALUES(" & Me.EMPLYE_ID & ",#" & EndDate & "#)" EndDate = DateAdd("d", 6, EndDate) Next 'show the added sheets Me.TIMESHEET_Subform.Requery 'field that tells you sheets are generated Me.SHEETS_ADDED = True Me.SHEETS_ADDED.SetFocus 'Sheets is the name of the command button Me.Sheets.Visible = False End Sub In the event Current of the form you can check the value of 'Me.SHEETS_ADDED' and make the button (un)visible accordingly. -- Groeten, Peter http://access.xps350.com --- news://freenews.netfront.net/ - complaints: news(a)netfront.net ---
From: John Spencer on 22 Jul 2010 09:31 Ok, first you need a small utility table (uu_Counter) with one field Counter) that contains the numbers 1 to 26. Then you need to run a query that will add the 26 weekly records. Assumption: There is a control on the frm_Emplye that is named txtEmplye_ID and has Emplye_ID as its source. The code for the button click event might look like Dim strSQL as String strSQL = "INSERT INTO tbl_TimeSheet (Emplye_ID, PeriodEndDate)" & _ " SELECT EmplyeID, DateAdd("d",7*[UU_Counter].[Counter]-1,[Start Date])" & _ " FROM tbl_Emplye, uu_Counter" & _ " WHERE uu_Counter <=26 and EmplyeID=" & me.txtEmplye_ID CurrentDB.Execute strSQL, dbFailOnError If you have a unique index set on the combination of Emplye_ID and Start Date in tbl_Timesheet you will not be able to add any duplicate records. As noted elsewhere, you could use the current event of frm_Emplye to detect whether or not records already existed in tbl_Timesheet for the specific employee IF DCount("*","tbl_Timesheet","Emplye_ID=" & Me.txtEmplye_ID)= 0 Then Me.btnAddRecords.Enabled = True Else Me.btnAddRecords.Enabled = False End if Or you could add that test to the above code and use it to allow the code to run or not. IF DCount("*","tbl_Timesheet","Emplye_ID=" & Me.txtEmplye_ID)= 0 Then Dim strSQL as String strSQL = "INSERT INTO tbl_TimeSheet (Emplye_ID, PeriodEndDate)" & _ " SELECT EmplyeID, DateAdd("d",7*[UU_Counter].[Counter]-1,[Start Date])" & _ " FROM tbl_Emplye, uu_Counter" & _ " WHERE uu_Counter <=26 and EmplyeID=" & me.txtEmplye_ID CurrentDB.Execute strSQL, dbFailOnError ELSE Beep MsgBox "Time Sheet records already exist for this employee." End IF John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Carl wrote: > I have a couple of tables called tbl_EMPLYE (has EMPLYE_ID as > autonumber PK) and tbl_TIMESHEET (has TIMESHEET_ID as autonumber PK, > and EMPLYE_ID as FK) based on a one to many relationship. These use a > form and subform where the form is based on tbl_EMPLYE and has a combo > box based on employee. The subform is based on tbl_TIMESHEET and the > two are linked by the EMPLYE_ID column. > > The frm_EMPLYE displays the Employee number and the Start Date they > started a placement (always a Monday date). > > The frm_TIMESHEET displays a Week ending date (always a Sunday) along > with a few other fields in a tabulated layout. > > What I need is a command button on the EMPLYE form that will create 26 > records (timesheets) in the tbl_TIMESHEET table where each week ending > date displays accordingly. > > For example. If the Start Date on the main form (frm_EMPLYE) is 19- > JUL-2010 (a Monday date), I want 26 records in the subform to show > (showing Sunday dates): > > 25-JUL-2010 > 01-AUG-2010 > 08-AUG-2010 > > etc.. > > What I then need is for the command button to be disabled on the > frm_EMPLYE form for each employee that has had the 26 records added > (would this be used in conjunction with a flag or something????) > > Any help would be greatly appreciated (or alternative solution) > > Cheers.
From: Carl on 22 Jul 2010 10:30
On Jul 22, 2:30 pm, XPS35 <xps...(a)gmail.com> wrote: > Carl wrote: > > I have a couple of tables called tbl_EMPLYE (has EMPLYE_ID as > > autonumber PK) and tbl_TIMESHEET (has TIMESHEET_ID as autonumber PK, > > and EMPLYE_ID as FK) based on a one to many relationship. These use a > > form and subform where the form is based on tbl_EMPLYE and has a combo > > box based on employee. The subform is based on tbl_TIMESHEET and the > > two are linked by the EMPLYE_ID column. > > > The frm_EMPLYE displays the Employee number and the Start Date they > > started a placement (always a Monday date). > > > The frm_TIMESHEET displays a Week ending date (always a Sunday) along > > with a few other fields in a tabulated layout. > > > What I need is a command button on the EMPLYE form that will create 26 > > records (timesheets) in the tbl_TIMESHEET table where each week ending > > date displays accordingly. > > > For example. If the Start Date on the main form (frm_EMPLYE) is 19- > > JUL-2010 (a Monday date), I want 26 records in the subform to show > > (showing Sunday dates): > > > 25-JUL-2010 > > 01-AUG-2010 > > 08-AUG-2010 > > > etc.. > > > What I then need is for the command button to be disabled on the > > frm_EMPLYE form for each employee that has had the 26 records added > > (would this be used in conjunction with a flag or something????) > > > Any help would be greatly appreciated (or alternative solution) > > > Cheers. > > The commands for your command button would be something like: > > Private Sub Sheets_Click() > Dim WeekCount As Byte > Dim EndDate As Date > > EndDate = DateAdd("d", 6, Me.EMPLYE_StartDate) > > For WeekCount = 1 To 26 > DoCmd.RunSQL "INSERT INTO TIMESHEET(EMPLYE_ID,WeekEndingDate) " & _ > "VALUES(" & Me.EMPLYE_ID & ",#" & EndDate & "#)" > EndDate = DateAdd("d", 6, EndDate) > Next > > 'show the added sheets > Me.TIMESHEET_Subform.Requery > > 'field that tells you sheets are generated > Me.SHEETS_ADDED = True > Me.SHEETS_ADDED.SetFocus > > 'Sheets is the name of the command button > Me.Sheets.Visible = False > > End Sub > > In the event Current of the form you can check the value of > 'Me.SHEETS_ADDED' and make the button (un)visible accordingly. > > -- > Groeten, > > Peterhttp://access.xps350.com > > --- news://freenews.netfront.net/ - complaints: n...(a)netfront.net ---- Hide quoted text - > > - Show quoted text - What a star, thanks very much for your help. |
