asked on

MS Access 2003 'auto repeat with date incriment'

Hi,

I have a db that records event start & finish dates.
I have been able to identify which days between the 2 dates are work days but now I need to record a seperate event in the db for each date.
eg. Event starts on 17/09/20112 and finishes on 20/09/2012
Rather than record a single 3-days event on 17/09/2012 I want to record a 1-day event on each of the ,17/18/19.
It has been suggested that I use 'Do Loop'or 'Next Loop' but I don't understand how.

This is the data I have

eventID:123
staffID:abc
LeaveType: sickleave
LeaveStartDate:17/09/2012
LeaveFinish: 19/09/2012

When I submit the form I want a 1-day leave event recorded for the 17th and the 18th (19th is the day that the employee returns to work)
Each single day event should be recorded with the startdate and finish date (finish date is the next day).

I've been trying to get a viable solution for this issue for more than a week and the tiny bit of hair I have left is now snowy white. Any help/advice will be appreciated.

Thanks,
David

IrogSinta

Something like this perhaps:

Dim rst as DAO.Recordset
Dim dtLeave as Date

Set rst = CurrentDb.NameOfTable
dtLeave = Me.txtLeaveStartDate

Do Until dtLeave = Me.txtLeaveFinish
    rst.AddNew
    rst!eventID = Me.txtEventID
    rst!staffID = Me.txtStaffID
    rst!LeaveType = Me.txtLeaveType
    rst!LeaveStartDate = dtLeave
    rst!LeaveFinish =  dtLeave+1
    rst.Update
    dtLeave = dtLeave+1
Loop
rst.Close
Set rst = Nothing

Open in new window

Oliver Wastell

As IrogSinta's post, however I've modified the code so that there is a field for the date of the day event, with the previous start and finish dates for the start and finish dates of the leave event:

Dim rst as DAO.Recordset
Dim dtLeaveStart as Date
dim dtLeaveEnd as Date
Dim dtLeaveDateCounter as Date

Set rst = CurrentDb.NameOfTable
dtLeaveStart = Me.txtLeaveStartDate
dtLeaveFinish  = Me.txtLeaveFinish
dtLeaveDateCounter = dtLeaveStart 

Do Until dtLeaveDateCounter = dtLeaveFinish  
    rst.AddNew
    rst!eventID = Me.txtEventID
    rst!staffID = Me.txtStaffID
    rst!LeaveType = Me.txtLeaveType
    rst!LeaveDayEvent = dtLeaveDateCounter 
    rst!LeaveStartDate = dtLeaveStart 
    rst!LeaveFinish = dtLeaveFinish  
    rst.Update
    dtLeaveDateCounter = dtLeaveDateCounter +1
Loop
rst.Close
Set rst = Nothing

Open in new window

dj1710

ASKER

Now I feel really stupid.

Do I put this script on the command button that I use to save the record?

I've tried adding it to the button script for 'Save & New Record' but it doesn't do anything.

Perhaps somebody could direct to specific instructions on how and where to insert this type of action/script. Or you could just tell me where it goes.

Thanks
dj

dj1710

ASKER

OK, I've tried putting the script on a button (see below)

But I'm getting a compile error: Method or data not found.
The line 'Set rst = CurrentDb.tblLeaveEvent' seems to be the cause of the error but I don't understand why. there are 2 tables in the db tblEmployes and tblLeaveEvent.

Private Sub Command18_Click()

Dim rst As DAO.Recordset
Dim dtLeaveStart As Date
Dim dtLeaveFinish As Date
Dim dtLeaveDateCounter As Date

Set rst = CurrentDb.tblLeaveEvent
dtLeaveStart = Me.txtLeaveStart
dtLeaveFinish = Me.txtLeaveFinish
dtLeaveDateCounter = dtLeaveStart

Do Until dtLeaveDateCounter = dtLeaveFinish
rst.AddNew
rst!eventID = Me.txtEventID
rst!staffID = Me.txtStaffID
rst!LeaveType = Me.txtLeaveType
rst!LeaveDayEvent = dtLeaveDateCounter
rst!LeaveStartDate = dtLeaveStart
rst!LeaveFinish = dtLeaveFinish
rst.Update
dtLeaveDateCounter = dtLeaveDateCounter + 1
Loop
rst.Close
Set rst = Nothing

End Sub

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dj1710

ASKER

I've tried the latest suggestion from IrogSinta but I'm still getting a compile error. This time the error seems to be with with the first line

Set rst = CurrentDb.OpenRecordset("tblLeaveEvent")
dtLeave = Me.txtLeaveStart

The highlighted part of the text after the error is found is .txtLeaveStart
The field is called LeaveStart (not LeaveStartDate)

IrogSinta

dj1710,
If you see code that is prefixed with a "Me." --- that means that what follows is the name of a control on your form. For clarity, programmers normally rename the controls rather than stick with the default names used when the controls are added. A combo box may get renamed usually to something like cbxFieldName, a list box would be lbxFieldName, and a textbox would be txtFieldName. Hence, in the code I gave you, I added txt prefixes to fields I assumed you had textboxes for.
txtEventID / txtStaffID / txtLeaveType / txtLeaveFinish, etc...

You could either rename your controls to match what I gave you or you could change the code I gave to match the name of your controls.

dj1710

ASKER

Irogsinta

I made the changes you suggested and the script has progressed.

I now get this warning on the form when I try to save
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the fields that contain duplicate data, remove the index or redefine the index to permit duplicates and try again."

I have removed the indexing on all fields within the LeaveEvent table and I have allowed all fields except the EventID to contain duplicates.

But the error is still occuring
In the VB editor the line 'rst.update' is highlighted.

Damn this stuff is complex

IrogSinta

I'm not sure what EventID stands for here but it definitely should allow duplicates as well since you are inserting the same record multiple times (but with different dates) with the same EventID. EventID is not supposed to be a number identifying each row uniquely is it?

dj1710

ASKER

The EventID is the primary key for LeaveEvent table.
Obviously I need to incriment that number by 1 for each new entry. I removed EventID from the script and it finally works!

Thank you very very much for your help.

dj1710

ASKER

The advice was perfect but my understanding of the subject prevented me from applying it immediately

IrogSinta

So EventID is set to AutoNumber. That's good.
I'm glad to hear that it works.