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.

In the OnCurrent event, check if the employee has a record in tbl_TIMESHEET
and enable/disable the button

"Carl" <carl.barrett (AT) newcastle (DOT) gov.uk> wrote

Carl wrote:

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 (AT) netfront (DOT) net ---

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:

On Jul 22, 2:30*pm, XPS35 <xps... (AT) gmail (DOT) com> wrote:
What a star, thanks very much for your help.

On Jul 22, 2:30*pm, XPS35 <xps... (AT) gmail (DOT) com> wrote:

Hi (again)

The above solution worked, however some of the dates are coming though
as British dd/mm/yyyy and others are coming through as US. For
example, the following is a list of dates I need inserted into the
timesheet table

11/07/2010
18/07/2010
25/07/2010
01/08/2010 (this is inserted into the table as 08/01/2010 even though
the variable EndDate stores it correctly)
08/08/2010
15/08/2010

I've tried the following on the solution you provided and it makes no
difference:

For WeekCount = 1 To 26
DoCmd.RunSQL "INSERT INTO tbl_TIMESHEET(EMPLYE_ID,TMESHT_DATE) " &
_
"VALUES(" & Me.EMPLYE_ID & ",#" & EndDate & "#)"
EndDate = FormatDateTime(DateAdd("d", 7, EndDate), 2)
Next


For WeekCount = 1 To 26
DoCmd.RunSQL "INSERT INTO tbl_TIMESHEET(EMPLYE_ID,TMESHT_DATE) " &
_
"VALUES(" & Me.EMPLYE_ID & ",#" & EndDate & "#)"
EndDate = Format(DateAdd("d", 7, EndDate), "dd/mm/yyyy")
Next

Again any help would be appreciated.

Cheers.