Is the return of the car indicated by the End Date field? If so, you could use DLookup to see if the field is null or not and throw an error message if it is. I'm not sure I would use BeforeUpdate though, but that's mostly just personal preference. Also, I would remove the spaces in your field names...for instance, I would change End Date to EndDate.
Here's an example of DLookup:
-
Dim varHire As Variant 'declare a variable for the hire ID. I used variant because I'm not sure what data type HireID is.
-
Dim strHire As Variant 'declare a variable for the return value of DLookup
-
-
varHire = txtHire 'this assumes that you get the hire ID from a field on a form
-
-
strHire = DLookup("EndDate", "tblHire", "HireID = '" & varHire & "'") 'EndDate is the field you're looking up, tblHire is the table you look in, and HireID is the lookup value to compare against.
-
-
if Not IsNull(strHire) then
-
MsgBox "The vehicle you are trying to book has already been allocated", vbExclamation, "Error - Booked Vehicle" 'inform the user that the vehicle has already been booked
-
Me.HireID = Null 'set the hire field to null
-
Me.HireID.SetFocus 'put the cursor back in the hire field so the user can select another vehicle
-
Exit Sub 'exit the sub so that the user can re-enter an hire ID
-
end if
-
3
