473,606 Members | 2,082 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Double Booking Code needs a little extra jazz

269 Contributor
Hi everybody!
I snagged a piece of code from Bytes (written by the one and only NeoPa to whom my Access success is somewhat owed!). The code works great but I desire it to do a few more things that I am not able to put together. I did TRY and you experts may laugh at my message box sequence. I laugh at myself too, and then return to Bytes. LOL!

Below, then the code runs and it IS a double booking, i get a message box that says "Double Booking" and THEN I get another message box that says "Looks good." Well if it is a double booking, I don't want the box telling me it "looks good." All that really tells me is I don't know how to code very well. LOL. I added the second message box as an indicator to me that the code did fire, because usually it's not a double booking, so nothing would happen when I ran the code. So "Looks Good" let's me know the code ran its check. Need help to exit the code after the "double booking" message box when that's true.

Two other things I would really like:
I want it to check that the departure date > arrival date. Typos sometimes get me here and that check would help, especially during year changes when I type the wrong year sometimes.

Right now this code fires when I click a button. But I would really like it to run when I exit the field (by tab key, or by mouse click somewhere else). I tried to put the [Event Procedure] On Exit but it gives me an error in the code here: Dim Cancel As Boolean and won't fire. But it works fine on the button click....howeve r, sometimes I could forget to click the button. Trying to eliminate more of my errors!

Here is the wonderful code. Can anyone help me repair either of these two things above?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Condo_Exit(Cancel As Integer)
  3. Dim ThisStartDate   As String
  4. Dim ThisEndDate     As String
  5. Dim Criteria        As String
  6. Dim Cancel          As Boolean
  8. ThisStartDate = "#" & Format(Me!Arrival, "mm/dd/yyyy") & "#"
  9. ThisEndDate = "#" & Format(Me!Departure, "mm/dd/yyyy") & "#"
  10. Criteria = "[Condo] = '" & Me!Condo & "' And " & _
  11.     "[Arrival] < " & ThisEndDate & " And [Departure] > " & ThisStartDate & ""
  12. Cancel = Not IsNull(DLookup("[Condo]", "[tblBookings]", Criteria))
  13. If Cancel Then _
  14. Call MsgBox("Double Booking", _
  15. vbOKOnly)
  16. MsgBox "Looks good."
  18. End Sub
Nov 18 '22 #1
8 9884
32,566 Recognized Expert Moderator MVP
Hi Danica Dearest.

To start with the easiest bit replace your lines #13 onwards with :
Expand|Select|Wrap|Line Numbers
  1. If Cancel Then
  2.     Call MsgBox("Double Booking", vbOKOnly)
  3. Else
  4.     MsgBox "Looks good."
  5. End If
  7. End Sub
Nov 20 '22 #2
32,566 Recognized Expert Moderator MVP
For your problem 'ONE'.
Add an extra (Date) variable after line #6 and then check that the [Arrival] date is earlier than the [Departure] one. If it isn't then switch them.
This code should replace your blank line #7 :
Expand|Select|Wrap|Line Numbers
  1. Dim datVar As Date
  3. With Me
  4.     If .Arrival > .Departure Then
  5.         datVar = .Arrival
  6.         .Arrival = .Departure
  7.         .Departure = datVar
  8.     End If
  9. End With
Nov 20 '22 #3
32,566 Recognized Expert Moderator MVP
For your problem 'TWO'.
Here you have tried to use the same (reserved) name (Cancel) for two different purposes within the same procedure. First in the procedure heading as an Integer, and then later, on line #6, as a Boolean. As your error message tried so hard to explain - that just can't work.

The predefined version of Cancel in the procedure header is not optional so you'll need to change the name of the other one so they don't work against each other.

You say this code is originally from me anyway, which I find curious as I always use 3-letter prefixes for names which ensures I never fall over such isues in the first place. However we managed to get here though, I suggest changing lines #6, #12 & #13 to use a variable called blnCancel instead of Cancel and I'm sure all will work normally.
Nov 20 '22 #4
32,566 Recognized Expert Moderator MVP
Just in case all those changes become a little complicated to manage I've copied below a version that should work past all your problems :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Condo_Exit(Cancel As Integer)
  2.     Dim ThisStartDate As String, ThisEndDate As String, Criteria As String
  3.     Dim blnCancel As Boolean
  4.     Dim datVar As Date
  6.     With Me
  7.         If .Arrival > .Departure Then
  8.             datVar = .Arrival
  9.             .Arrival = .Departure
  10.             .Departure = datVar
  11.         End If
  12.         ThisStartDate = "#" & Format(.Arrival, "mm/dd/yyyy") & "#"
  13.         ThisEndDate = "#" & Format(.Departure, "mm/dd/yyyy") & "#"
  14.         Criteria = "[Condo] = '" & .Condo & "' And " _
  15.                  & "[Arrival] < " & ThisEndDate & " And " _
  16.                  & "[Departure] > " & ThisStartDate
  17.         Cancel = Not IsNull(DLookup("[Condo]", "[tblBookings]", Criteria))
  18.         If Cancel Then
  19.             Call MsgBox("Double Booking", vbOKOnly)
  20.         Else
  21.             Call MsgBox ("Looks good.", vbOKOnly)
  22.         End If
  23.     End With
  24. End Sub
Nov 20 '22 #5
269 Contributor
This works beautifully NeoPa. Thank you for the explanations, which I have studied. I added an extra message box on the arrival>departu re and it works beautifully also.

Sometimes the answers are so simple. Sometimes they are more complex. Either way, I super grateful for the help. Thank you so much!
Nov 21 '22 #6
32,566 Recognized Expert Moderator MVP
Always a joy :-)

I'll PM about a chat soon.
Nov 21 '22 #7
1 New Member
This actually isn’t too hard. I built one with Dialogflow to screen any recruiters reaching out to me on LinkedIn or via email. All it takes is a database of commonly asked questions and your responses to those questions. We’re not far off from “have your bot talk to my bot to see if we’re a good fit”.
Nov 22 '22 #8
32,566 Recognized Expert Moderator MVP
So, without offering any actual help whatever, you wanted to post to make everyone realise how much more clever you are than those needing to ask for help? That's a bit "WOW!!!" don't you think?
Nov 22 '22 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Grant | last post by:
Hi I have a database which logs the usage of rooms. Some booking are entered well in advance, and some have stays of more than six months. I would like to ensure that rooms which have been booked cannot be booked again, i.e. cannot be booked in that date range Any help will be much appreciated
by: Just Me | last post by:
In code I check to see if the Floppy drive is ready and display it's state. But if the user inserts or removes a floppy disk I need an event or a Wndproc message to cause the code to display the new state. Is the some way the code can be alerted to the fact that a removable disk drive's state changed? I wonder if the above isn't clear.
by: johnblack | last post by:
I am creating a tennis court booking system. I am trying to create a macro that will not allow the same court to be booked on the same date at the same time. I have the following tables and fields tblMembers MemberID FirstName LastName etc
by: anotherjoe | last post by:
Question 1: I have reservation database in access 2007 I have a cabin information table with: cabinid (primarykey) cabinname(text) cabinlocation(text) I have a customer table with: customer id(primary key) last name (text)
by: Keyboyx | last post by:
I am creating a Dentist Booking form, for my A level project. I have the following Tables: Appointments Appointment ID Dentist ID Patient ID Appointment Date Appointment Time
by: Wayne | last post by:
I'm building a bookings database for trucks that among other things captures the TruckName, LoadDate, LoadTime, UnloadDate and UnloadTime. Is there a simple way to prevent double bookings for any particular truck so that when a new entry is being added, if the LoadDate and LoadTime doubles up with an existing entry I can flag it to the user?
by: Jordan Boden | last post by:
Hi, I have a problem trying to prevent a time slot and seat number being double booked. I am using PHP and MySQL for this. Currently I have no errors concerning variable names or issues with inserting data into the database. The only problem I seem to have involves preventing a certain seat number and time from being double booked. I have tried numerous things and I think the problem lies within my 'if' statement so i have shown a snippet of...
by: Lucylu | last post by:
Hi, I am creating a website for a project where you are able to book driving lessons. I have created code which (sort of) prevents double booking, however, I'm having a problem. If there is a booking stored in the DB at 11.30-12.30, if i try to book a lesson from 11-12 it prevents me from doing so, however if I try book from 12-1, it allows me to go ahead with the booking. The code I am using is below. Does anyone know how to solve...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.