471,572 Members | 855 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,572 software developers and data experts.

Double Booking Code needs a little extra jazz

269 256MB
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Condo_Exit(Cancel As Integer)
  2.  
  3. Dim ThisStartDate   As String
  4. Dim ThisEndDate     As String
  5. Dim Criteria        As String
  6. Dim Cancel          As Boolean
  7.  
  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."
  17.  
  18. End Sub
1 Week Ago #1

✓ answered by 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 :
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
  5.  
  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

8 4398
NeoPa
32,439 Expert Mod 16PB
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
  6.  
  7. End Sub
1 Week Ago #2
NeoPa
32,439 Expert Mod 16PB
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
  2.  
  3. With Me
  4.     If .Arrival > .Departure Then
  5.         datVar = .Arrival
  6.         .Arrival = .Departure
  7.         .Departure = datVar
  8.     End If
  9. End With
1 Week Ago #3
NeoPa
32,439 Expert Mod 16PB
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.
1 Week Ago #4
NeoPa
32,439 Expert Mod 16PB
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
  5.  
  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
1 Week Ago #5
DanicaDear
269 256MB
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!
5 Days Ago #6
NeoPa
32,439 Expert Mod 16PB
Always a joy :-)

I'll PM about a chat soon.
5 Days Ago #7
tumnahi232
1 Bit
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”.
4 Days Ago #8
NeoPa
32,439 Expert Mod 16PB
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?
4 Days Ago #9

Post your reply

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

Similar topics

1 post views Thread by Lucylu | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

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.