• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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
0
dj1710
Asked:
dj1710
  • 6
  • 5
1 Solution
 
IrogSintaCommented:
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

0
 
Oliver WastellMemberCommented:
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

0
 
dj1710Author Commented:
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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
dj1710Author Commented:
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
0
 
IrogSintaCommented:
Try this code, I made a slight correction:
Dim rst as DAO.Recordset
Dim dtLeave as Date

Set rst = CurrentDb.OpenRecordset("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

0
 
dj1710Author Commented:
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)
0
 
IrogSintaCommented:
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.
0
 
dj1710Author Commented:
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
0
 
IrogSintaCommented:
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?
0
 
dj1710Author Commented:
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.
0
 
dj1710Author Commented:
The advice was perfect but my understanding of the subject prevented me from applying it immediately
0
 
IrogSintaCommented:
So EventID is set to AutoNumber.  That's good.
I'm glad to hear that it works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now