Reoccurring Entries on a Form

  • Thread starter Thread starter AccessIM
  • Start date Start date
A

AccessIM

I have a form that is used to enter absent employees. The supervisor enters
the date, time, employee name and the reason for their absence. When an
employee is on an extended leave of absence, they are currently entering
every day individually which becomes tedious when an employee can be off for
4-6 weeks.

Is there a way to enter a date range for reoccurring events? In other
words, if an employee is going to be off for 1 week, I would like the
supervisor to be able to enter a beginning date (7/6/08) and an ending date
(7/12/08) and have it create records for each individual date in that range
(7/6, 7/7, 7/8, 7/9, 7/10, 7/11 and 7/12) copying the other fields and
changing the date.

Thank you in advance.
 
You could add records to the table with a loop. Since your form will add a
record for the original date entered, you want to start with the next day and
add records until you get to the last date.

Set rst = Currentdb.OpenRecordset("YourTable", dbOpenDynaset)
dtmLoopDate = DateAdd("d",1,Me.txtOffDate)

With rst
Do Until dtmLoopDate > Me.txtLastOffDate
.AddNew
![Employee] = Me.txtEmployee
....
![DayOff] = dtmLoopDate
.Update
dtmLoopDate = DateAdd("d",1, dtmLoopDate)
Loop
End With
 
Thank you so much for the quick response! I am a beginner at best in Visual
Basic and writing code so I am a little confused.

I understand how to modify the code from the "With rst" down to suit my
database but I do not understand the first two lines.

Could you please explain what they mean and also tell me where I insert the
code?

Klatuu said:
You could add records to the table with a loop. Since your form will add a
record for the original date entered, you want to start with the next day and
add records until you get to the last date.

Set rst = Currentdb.OpenRecordset("YourTable", dbOpenDynaset)
dtmLoopDate = DateAdd("d",1,Me.txtOffDate)

With rst
Do Until dtmLoopDate > Me.txtLastOffDate
.AddNew
![Employee] = Me.txtEmployee
....
![DayOff] = dtmLoopDate
.Update
dtmLoopDate = DateAdd("d",1, dtmLoopDate)
Loop
End With


--
Dave Hargis, Microsoft Access MVP


AccessIM said:
I have a form that is used to enter absent employees. The supervisor enters
the date, time, employee name and the reason for their absence. When an
employee is on an extended leave of absence, they are currently entering
every day individually which becomes tedious when an employee can be off for
4-6 weeks.

Is there a way to enter a date range for reoccurring events? In other
words, if an employee is going to be off for 1 week, I would like the
supervisor to be able to enter a beginning date (7/6/08) and an ending date
(7/12/08) and have it create records for each individual date in that range
(7/6, 7/7, 7/8, 7/9, 7/10, 7/11 and 7/12) copying the other fields and
changing the date.

Thank you in advance.
Click to expand...
Click to expand...
 
Set rst = Currentdb.OpenRecordset("YourTable", dbOpenDynaset)

this line opens the table and establishes the recordset object.

dtmLoopDate = DateAdd("d",1,Me.txtOffDate)

This line initialized the date varialbe you will use to write new records
the the database. For info on the DateAdd function, see VBA Help.

Where you put the code depends on how you want your form to work.

You might consider putting it in the Click event of a command button.
--
Dave Hargis, Microsoft Access MVP


AccessIM said:
Thank you so much for the quick response! I am a beginner at best in Visual
Basic and writing code so I am a little confused.

I understand how to modify the code from the "With rst" down to suit my
database but I do not understand the first two lines.

Could you please explain what they mean and also tell me where I insert the
code?

Klatuu said:
You could add records to the table with a loop. Since your form will add a
record for the original date entered, you want to start with the next day and
add records until you get to the last date.

Set rst = Currentdb.OpenRecordset("YourTable", dbOpenDynaset)
dtmLoopDate = DateAdd("d",1,Me.txtOffDate)

With rst
Do Until dtmLoopDate > Me.txtLastOffDate
.AddNew
![Employee] = Me.txtEmployee
....
![DayOff] = dtmLoopDate
.Update
dtmLoopDate = DateAdd("d",1, dtmLoopDate)
Loop
End With


--
Dave Hargis, Microsoft Access MVP


AccessIM said:
I have a form that is used to enter absent employees. The supervisor enters
the date, time, employee name and the reason for their absence. When an
employee is on an extended leave of absence, they are currently entering
every day individually which becomes tedious when an employee can be off for
4-6 weeks.

Is there a way to enter a date range for reoccurring events? In other
words, if an employee is going to be off for 1 week, I would like the
supervisor to be able to enter a beginning date (7/6/08) and an ending date
(7/12/08) and have it create records for each individual date in that range
(7/6, 7/7, 7/8, 7/9, 7/10, 7/11 and 7/12) copying the other fields and
changing the date.

Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top