dj1710
asked on
Create multiple records from a master and then delete the master
Hi,
The following script has been compiled mainly with the Assistance of the resident experts but I have a small problem.
The script is intended to record a single multi-day event as a series of seperate single day events, and it does.
However, the multi-day event is also being recorded.
ie. An event runs from Mon to Wed, db is correctly recording an event for Mon, an event for Tue and an event for Wed but it is incorrectly also recording an event for Monday to wednesday.
How can I delete the multi-day event and retain the single day events that it created?
Private Sub Command18_Click()
Dim rst As DAO.Recordset
Dim dtLeave As Date
Set rst = CurrentDb.OpenRecordset("t
dtLeave = Me.LeaveStart
Do Until dtLeave = Me.LeaveFinish
If Weekday(dtLeave, vbMonday) < 6 Then
rst.AddNew
rst!UserID = Me.UserID
rst!LeaveType = Me.LeaveType
rst!LeaveStart = dtLeave
rst!LeaveFinish = dtLeave + 1
rst.Update
End If
dtLeave = dtLeave + 1
Loop
rst.Close
Set rst = Nothing
End Sub
The following script has been compiled mainly with the Assistance of the resident experts but I have a small problem.
The script is intended to record a single multi-day event as a series of seperate single day events, and it does.
However, the multi-day event is also being recorded.
ie. An event runs from Mon to Wed, db is correctly recording an event for Mon, an event for Tue and an event for Wed but it is incorrectly also recording an event for Monday to wednesday.
How can I delete the multi-day event and retain the single day events that it created?
Private Sub Command18_Click()
Dim rst As DAO.Recordset
Dim dtLeave As Date
Set rst = CurrentDb.OpenRecordset("t
dtLeave = Me.LeaveStart
Do Until dtLeave = Me.LeaveFinish
If Weekday(dtLeave, vbMonday) < 6 Then
rst.AddNew
rst!UserID = Me.UserID
rst!LeaveType = Me.LeaveType
rst!LeaveStart = dtLeave
rst!LeaveFinish = dtLeave + 1
rst.Update
End If
dtLeave = dtLeave + 1
Loop
rst.Close
Set rst = Nothing
End Sub
ASKER
The primary key of the multi-day event comes before the single day events
ASKER
the whole db
db004.mdb
db004.mdb
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 did all except change the RecordSource and it appears to be working fine.
Thanks again.
Thanks again.
It doesn't quite make sense leaving the RecordSource as it is. If you wanted to add a Leave event for Sue Smith for example, you have to skip the first 5 records because they all pertain to David Jones. Then if you want to add someone like Linda Smith, you have to enter all her information (Last, First, Email address) rather than it being already in the form.
Changing your record source would show you all 39 employees. You could add a dropdown to go straight to a particular employee. Then you could have either a listbox or subform that shows all the dates that they plan to take leave.
That's my suggestion. If you want to take that route and in the process get stuck, just open another question and someone's always here to lend a hand.
Changing your record source would show you all 39 employees. You could add a dropdown to go straight to a particular employee. Then you could have either a listbox or subform that shows all the dates that they plan to take leave.
That's my suggestion. If you want to take that route and in the process get stuck, just open another question and someone's always here to lend a hand.
ASKER
The way I'm doing at the moment I enter the UserID (employee identifier) in the form and all of the employee details are populated by a query on the employee table.
All I select on the form is the leave type and the start & finish dates.
I thought that was the most efficient method, but I'm a novice. I am appreciative of any advice to improve the design but due to my level of understanding the advice needs to be comprehensive.
All I select on the form is the leave type and the start & finish dates.
I thought that was the most efficient method, but I'm a novice. I am appreciative of any advice to improve the design but due to my level of understanding the advice needs to be comprehensive.
If this Leave log is currently your only intention for this database then you could leave it the way it is since it is workable the way you described it. I'm glad I could help.
As I mentioned in your previous question, I don't see how this code can create the multi-day event you mention. I'm wondering if that record is already in the table. Does the primary key of the extra record come after these single day event ones that are created?