Prev: sum two subqueries
Next: couple of questions on mail merge
From: Carl on 27 Jul 2010 06:37
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 -


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.
First  |  Prev  | 
Pages: 1 2
Prev: sum two subqueries
Next: couple of questions on mail merge