Create multiple records from a master and then delete the master

Posted on 2012-09-18
Last Modified: 2012-09-18

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

    Expert Comment

    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?

    Author Comment

    The primary key of the multi-day event comes before the single day events

    Author Comment

    the whole db
    LVL 29

    Accepted Solution

    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.

    Author Comment

    I did all except change the RecordSource and it appears to be working fine.

    Thanks again.
    LVL 29

    Expert Comment

    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.

    Author Comment

    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.
    LVL 29

    Expert Comment

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now