Autofill Form

[wwhit]

I have a form that has the following to be used like a time card:

Employee Name
Hours
Start Date
End Date

I would like the user to be able to enter the above information for a week at a time. I would like access to auto fill the table for the week. For example:

Employee Name = Jane Doe
Hours = 8.00
Start Date = 08/15/2011
End Date = 08/19/2011

Then in the table it would look like this (autofill)
Employee Name Hours Date
Jane Doe 8.00 8/15/2011
Jane Doe 8.00 8/16/2011
Jane Doe 8.00 8/17/2011
Jane Doe 8.00 8/18/2011
Jane Doe 8.00 8/19/2011

Can this be done?

 

[crxftw]

Run a simple query:

INSERT INTO TimeCardTable (EmployeeName, Hours, StartDate, EndDate) VALUES ("Jane Doe", "8.00", Date(), DateAdd("d",4,Date())

 

[wwhit]

I have used the Insert Into with an append query that runs on the form with a command button. I am working with the Date Expression. This actual works but only puts one record into the table which is the date plus four into the table. If I have 08/16/2011 then it will add one record for 08/20/2011.

DateAdd("d",4,[Forms]![Time Off Form]![EMLDATE]) AS Expr4

Noticing I am getting the date from the form. I have looked up the date add function and it does indicate this will add to the date four times (mattering on what number you use). How can I have it enter four records and not just one?

 

[crxftw]

What version of Access are you using?

 

[wwhit]

I'm using 2007.

 

[JANR]

Create a simple loop which runs 5 times and insert values to your table.

ex:

Dim x As Integer
 
For x = 0 To 4
    Debug.Print DateAdd("d", x, Date)
Next x

This loop gives you consecutive dates from to days date and 5 dates ahead printed in the immediatewindow.

However pre-recorded records in a table isen't a very good idea, what happens if Jane Doe dosen't show up for work on one of the days. Do you plan to delete that record or...?

JR

 

[wwhit]

Yes we would delete the records that we don't need.
I have two questions. Would x need to equal something?
If I do this then in the query how do I do the expression? I've never done a loop before so I am curious before I try it.

Dim x As Integer
? x="EMLDATE"?
For x = 0 To 4
Debug.Print DateAdd("d",[Enter number of days to add],[Forms]![Time Off Form]![EMLDATE])
Next x

 

[JANR]

x is holding the countervalue to keep track on when the loop is done, I used it in the DateAdd function to add the correct number of days a 2 for 1 deal.

First run the function get this output:

DateAdd("d",0,Date) -> To day's date

next run

DateAdd("d",1,Date) -> Tomorrow

etc.

JR

 

[wwhit]

I put the following in the on click command button:

Dim x As Integer
For x = 0 To 4
Debug.Print DateAdd("d", x, [Forms]![Time Off Form]![EMLDATE])
Next x
DoCmd.OpenQuery "Time Off Append Query"

What am I doing wrong?

 

[JANR]

What I showed was just an example on a loop it wasen't inteded as a working solution since I don't know all the details, but I did a test which you can tyr and adapt:

Private Sub [COLOR=red]cmdAddWeek[/COLOR]_Click()
Dim x As Integer
For x = 0 To 4
    Dim s As String
    s = " Insert into [COLOR=red]TimeCardTable[/COLOR] ([COLOR=red]EmployeeName[/COLOR], [COLOR=red]WorkHours[/COLOR], [COLOR=red]WorkDate[/COLOR])" & _
        " Values ( [COLOR=blue]'Jane Doe'[/COLOR], [COLOR=blue]8.00[/COLOR] ," & Format(DateAdd("d", x, Forms![COLOR=red]skjema2[/COLOR]![COLOR=red]StartDAte[/COLOR]), "\#mm\/dd\/yyyy\#") & ")"
    CurrentDb.Execute s, dbFailOnError
    
Next x
End Sub

All the redparts needs to be changed to reflect your db setup, the blue part I would change to a more dynamic approach so you don't have toe change the code for different values, perhaps point to pre-defined controls on your form.

JR

 

[wwhit]

Here is the code I entered into the on click button and it is not working.

Private Sub Command16_Click()
Dim x As Integer
For x = 0 To 4
Dim s As String
s = " INSERT INTO [Daily Crew Assignment] ([DL EMPLOYEE], [DL WITH CREW], [DL MEMO], [DL EMLDATE], [DL ENTERED BY] )" & _
" VALUES ([Forms]![Time Off Form]![Combo9], [Forms]![Time Off Form]![Combo13], [Forms]![Time Off Form]![DL MEMO],[DL ENTERED BY] = fOSUserName()," & Format(DateAdd("d", x, [Forms]![Time Off Form]![DL EMLDATE]), "\#mm\/dd\/yyyy\#") & ")"


Next x
End Sub

 

[JANR]

You didn't execute your SQL

Private Sub Command16_Click()
Dim x As Integer
For x = 0 To 4
Dim s As String
s = " INSERT INTO [Daily Crew Assignment] ([DL EMPLOYEE], [DL WITH CREW], [DL MEMO], [DL EMLDATE], [DL ENTERED BY] )" & _
" VALUES ([Forms]![Time Off Form]![Combo9], [Forms]![Time Off Form]![Combo13], [Forms]![Time Off Form]![DL MEMO],[DL ENTERED BY] = fOSUserName()," & Format(DateAdd("d", x, [Forms]![Time Off Form]![DL EMLDATE]), "\#mm\/dd\/yyyy\#") & ")"

Currentdb.Execute s, dbFailOnError

Next x
End Sub

JR

 

[wwhit]

I figured it out. I just did a DoCmd to run the append query four times. That was easier. Thanks,