Results 1 to 10 of 10
  1. #1
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94

    Macro to check data entry form before record is saved (BeforeUpdate)

    Hi Everyone,


    I have got some way through a db for my daughter, I'm not very good with Access. I have a bookings table called tblBookings, which is like a hotel room bookings system. The three relevant fields are the customer code [CusCode] the arrival date [Arrive] and the departure date [Depart]. CusCode is a short text field and Arrive and Depart are Date/Time fields, although time is not to be considered. The only related table is tblCustomers only to reference the property address.
    I want to prevent overlapping bookings being entered and to start with, I have a data entry form to enter the above fields and a 'Save Record' button. I wanted to use BeforeUpdate to check if the departure date (Depart) of the new proposed booking overlaps a previously confirmed booking already in the table. I've spent ages building a query that finds the previously accepted booking's depart date so that I can later go on the see if the new proposed booking's depart date falls within the previously saved one. Even later I can construct a similar query that finds the same information about any following accepted bookings and then see if the new booking's dates overlap past or future dates.
    Then I found out that BeforeUpdate can't use a query and I can't find a BeforeUpdate parameter for a macro.


    The sql that I constructed to find the most recent accepted booking in the past is this;
    Code:
    SELECT TOP 1 tblBookings.CusCode, tblBookings.Depart
    FROM tblBookings
    WHERE tblBookings.Depart <= forms!frmBookingsEntryForm.Depart AND tblBookings.CusCode = forms!frmBookingsEntryForm!Cuscode
    ORDER BY tblBookings.Depart DESC;
    Can anyone point me forwards to construct a macro that I can refer to in the form's BeforeUpdate property please?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This may help with the SQL:

    http://www.baldyweb.com/OverLap.htm

    Your situation probably requires a tweak, as presumably one booking can start on the same day another ends.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Hi Paul,
    Thanks for your reply. I have seen your overlap.htm on my journey to where I am right now, but I would like to verify the new data at the time of entry. You are correct about a departure and arrival on the same day, that's fine. I figured that if I can find a previously accepted booking with a departure date equal to or less that the new proposed booking, I would catch even a duplicate booking or any previous one going backwards. Later I would do the same thing but moving forwards.
    Thanks,
    Trevor.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not clear how you're thinking that would work. Sounds like you're relying on the user to check. I'd let the user enter the new from/to dates, then check for any overlaps before they can save the record. That's typically done in the before update event of the form, or behind a button to save the record. You could also do it after they enter both dates, in the after update event of the date textboxes.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Hello again,
    Yes, that's exactly what I want to do. What I don't know, is how I get the data already entered into the data entry form, checked with something like my code. I have read that the Save button with an entry in the beforeupdate property can't fire a query, it has to be vba or a macro. The macro is what I am asking for help on.
    Trevor.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, the macro or VBA code could open a query, if that's what you want done. Most of us don't let users into tables or queries directly, so I'd open a form or report based on the query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Quote Originally Posted by pbaldy View Post
    Well, the macro or VBA code could open a query, if that's what you want done. Most of us don't let users into tables or queries directly, so I'd open a form or report based on the query.
    Hi Paul,
    This db will only be used by one person, my daughter, however I still don't even intend her to delve into the table, but please have a glance at my original post again. You will see that I already have the form (data entry) which works properly. It has got a 'Save record' button. What I am asking is how to build an BeforeUpdate macro so that when the save button is clicked the dates of the new proposed booking are checked against the existing ones with code similar to what I outlined in the original post.
    Thanks,
    Trevor.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would build the query mentioned in post 2. Then code behind the button:

    Code:
    If DCount("*", "QueryName") > 0 Then
      MsgBox "Conflicts with another booking"
      Exit Sub
    Else
      'your save code
    End If
    When I mentioned opening a form, I meant one displaying the results of this query should it be desired.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Thanks Paul,
    I'll give that a try.
    Thanks for your help,
    Trevor.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem Trevor, post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-04-2017, 01:16 AM
  2. Replies: 9
    Last Post: 03-01-2017, 10:00 AM
  3. Replies: 6
    Last Post: 09-22-2014, 02:45 AM
  4. Replies: 7
    Last Post: 11-02-2012, 12:05 PM
  5. Replies: 1
    Last Post: 09-28-2011, 01:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums