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....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?
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....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?
Code:
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
Comment