Double Booking Code needs a little extra jazz

[DanicaDear]

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:
ONE
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.

TWO
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....however, 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?

Private Sub Condo_Exit(Cancel As Integer)
 
Dim ThisStartDate   As String
Dim ThisEndDate     As String
Dim Criteria        As String
Dim Cancel          As Boolean
 
ThisStartDate = "#" & Format(Me!Arrival, "mm/dd/yyyy") & "#"
ThisEndDate = "#" & Format(Me!Departure, "mm/dd/yyyy") & "#"
Criteria = "[Condo] = '" & Me!Condo & "' And " & _
    "[Arrival] < " & ThisEndDate & " And [Departure] > " & ThisStartDate & ""
Cancel = Not IsNull(DLookup("[Condo]", "[tblBookings]", Criteria))
If Cancel Then _
Call MsgBox("Double Booking", _
vbOKOnly)
MsgBox "Looks good."
 
End Sub

[NeoPa]

Hi Danica Dearest.

To start with the easiest bit replace your lines #13 onwards with :

If Cancel Then
    Call MsgBox("Double Booking", vbOKOnly)
Else
    MsgBox "Looks good."
End If
 
End Sub

[NeoPa]

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 :

Dim datVar As Date
 
With Me
    If .Arrival > .Departure Then
        datVar = .Arrival
        .Arrival = .Departure
        .Departure = datVar
    End If
End With

[NeoPa]

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.

[NeoPa]

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 :

Private Sub Condo_Exit(Cancel As Integer)
    Dim ThisStartDate As String, ThisEndDate As String, Criteria As String
    Dim blnCancel As Boolean
    Dim datVar As Date
 
    With Me
        If .Arrival > .Departure Then
            datVar = .Arrival
            .Arrival = .Departure
            .Departure = datVar
        End If
        ThisStartDate = "#" & Format(.Arrival, "mm/dd/yyyy") & "#"
        ThisEndDate = "#" & Format(.Departure, "mm/dd/yyyy") & "#"
        Criteria = "[Condo] = '" & .Condo & "' And " _
                 & "[Arrival] < " & ThisEndDate & " And " _
                 & "[Departure] > " & ThisStartDate
        Cancel = Not IsNull(DLookup("[Condo]", "[tblBookings]", Criteria))
        If Cancel Then
            Call MsgBox("Double Booking", vbOKOnly)
        Else
            Call MsgBox ("Looks good.", vbOKOnly)
        End If
    End With
End Sub

[DanicaDear]

This works beautifully NeoPa. Thank you for the explanations, which I have studied. I added an extra message box on the arrival>departure 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!

[NeoPa]

Always a joy :-)

I'll PM about a chat soon.

[tumnahi232]

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”.

[NeoPa]

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?