[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Create multiple records from a master and then delete the master


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("tblLeaveEvent")
dtLeave = Me.LeaveStart

Do Until dtLeave = Me.LeaveFinish
    If Weekday(dtLeave, vbMonday) < 6 Then
        rst!UserID = Me.UserID
        rst!LeaveType = Me.LeaveType
        rst!LeaveStart = dtLeave
        rst!LeaveFinish = dtLeave + 1
    End If
    dtLeave = dtLeave + 1

Set rst = Nothing
End Sub
  • 4
  • 4
1 Solution
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?
dj1710Author Commented:
The primary key of the multi-day event comes before the single day events
dj1710Author Commented:
the whole db
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Okay, the problem is not with the code.  That works fine.  The problem is with your form.  LeaveType, LeaveStart, and LeaveFinish cannot be bound controls.  These have to be unbound.  Since they're bound, you're actually changing an existing record, the one whose EventID you see.

Change the RecordSource of your form to tblEmployees.
Delete the ControlSources for LeaveType, LeaveStart, LeaveFinish, and Note
Remove the control named EventID.

See if that works for you.
dj1710Author Commented:
I did all except change the RecordSource and it appears to be working fine.

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.
dj1710Author Commented:
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.
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.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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