dj1710
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
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
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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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("t blLeaveEve nt")
dtLeave = Me.txtLeaveStart
The highlighted part of the text after the error is found is .txtLeaveStart
The field is called LeaveStart (not LeaveStartDate)
Set rst = CurrentDb.OpenRecordset("t
dtLeave = Me.txtLeaveStart
The highlighted part of the text after the error is found is .txtLeaveStart
The field is called LeaveStart (not LeaveStartDate)
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.
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.
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
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
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?
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.
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.
ASKER
The advice was perfect but my understanding of the subject prevented me from applying it immediately
So EventID is set to AutoNumber. That's good.
I'm glad to hear that it works.
I'm glad to hear that it works.
Open in new window