?
Solved

MS Access 2003 'auto repeat with date incriment'

Posted on 2012-09-16
12
Medium Priority
?
350 Views
Last Modified: 2012-09-17
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
Comment
Question by:dj1710
  • 6
  • 5
12 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38404486
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
 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 38404565
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
 

Author Comment

by:dj1710
ID: 38407717
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:dj1710
ID: 38407852
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
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38407858
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
 

Author Comment

by:dj1710
ID: 38407951
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38407980
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
 

Author Comment

by:dj1710
ID: 38408092
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38408116
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
 

Author Comment

by:dj1710
ID: 38408123
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
 

Author Closing Comment

by:dj1710
ID: 38408126
The advice was perfect but my understanding of the subject prevented me from applying it immediately
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38408127
So EventID is set to AutoNumber.  That's good.
I'm glad to hear that it works.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question