Returning True/False to a form when query is run?

[hydeyho]

I'm really not sure how to go about this. I'm creating a course booking system and when creating a booking I need to check for current bookings with the same employee and course id's (i.e. the employee is already booked on the course). The query takes the employee and course id's from a form, and is initialised when the 'book' button is pressed. It correctly selects if the person is already booked on the course but I want the query to return a value to the form i.e. if it returns null/false then the booking can be created but if it returns a record/true then the booking already exists and a message box can be displayed.

I'm not sure if I'm going about this the right way, can anyone suggest how this can be done as described above or suggest a better way of doing the task?

 

[RuralGuy]

See if this link has any ideas you can use.

 

[hydeyho]

Thanks for the reply but my query already runs fine and successfully finds if an employee is already booked on a course. My problem is telling my form if the record already exists.

This is the code I have used when clicking the 'book' button (everything is bold is what I'm trying to achieve):

Private Sub btnBook_Click()

If (Me.intEmployee <> 0) And (Me.intCourseDetails <> 0) Then
DoCmd.Echo False

If (DoCmd.OpenQuery "qryCheckBooking" = Null) Then

DoCmd.OpenForm "frmCourseDetails"
DoCmd.GoToControl "intCourseDetails"
DoCmd.FindRecord Forms!frmMainBooking!frmBooking.Form!intCourseDetails
Forms!frmCourseDetails!intRemaining = Forms!frmCourseDetails!intRemaining - 1
DoCmd.Close acForm, "frmCourseDetails"
MsgBox "Booking Created", vbInformation, "Marston's Inns & Taverns"
DoCmd.Echo True
DoCmd.SendObject acSendNoObject, , , Forms!frmMainBooking!frmFullEmployeeDetails.Form!txtEmail, , , "Booking Confirmation (Ref - " & Forms!frmMainBooking!frmBooking.Form!intBooking & ")", Forms!frmMainBooking!frmFullEmployeeDetails.Form!txtFirstName & " " & Forms!frmMainBooking!frmFullEmployeeDetails.Form!txtLastName & " has been booked onto the " & Forms!frmMainBooking!frmFullCourseDetails.Form!txtCourse & " course on " & Forms!frmMainBooking!frmFullCourseDetails.Form!dteDate & "."

DoCmd.Echo False
Forms!frmMainBooking!frmFullEmployeeDetails.Form!txtSearch = ""
DoCmd.Close acForm, "frmMainBooking"
DoCmd.OpenForm "frmMainBooking"
DoCmd.Echo True

Else
MsgBox "Employee already booked on course", vbCritical
End If

Else
MsgBox "Employee or Course not selected", vbCritical
End If

End Sub

 

[RuralGuy]

How about just using a DCount() with your query to see if any records are returned?

 

[hydeyho]

Brilliant, thank you!

 

[RuralGuy]

Glad I could help.