double booking code help!

[JackieFeng]

I am trying to find a way to prevent double booking.

In my booking form, i have date(date/time),start time(date/time) and doctor ID(number)-all from tblBooking


For example, doctor 1 9/6/2016 9:00. then next time i enter this information into form which should prevent me doing this and an error message should display.

What code should i put into this form? (code for multiple fields)

[CJ_London]

something like

if dcount("*","tblBooking","DoctorID=" & me.txtDoctorID & " AND StartTime = " & cdate(me.txtStartTime) & " AND StartDate=" & cdate(me.txtStartDate) <>0 Then msgBox "Time Already Booked"

You say you have a field called Date - this is a reserved word and should not be used for a field name

[JackieFeng]

Thank you for replay.

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub


If dcount("'", "tblBooking","DoctorID=" & me.txtDoctorID &"
AND StartTime = " & cdate(me.txtStartTime)&"
AND ApptDate = "& cdate(me.txtAppDate)<>0


Then msgBox "Time Already Booked"


The code i entered are red color.. Do you know what the problem is ?

[CJ_London]

it needs to be in an event sub - you haven't explained how your form works so I didn't want to waste time guessing which event - but it will be the first one after all three fields have been completed by the user. Also, you a) have not copied my code properly and b) I would be very surprised if I guessed the names of your form controls correctly

[JackieFeng]

This is the error message... The fields are after update [Event Procedure]

[orange]

If dcount("'", "tblBooking","DoctorID=" & me.txtDoctorID &"
AND StartTime = " & cdate(me.txtStartTime)&"
AND ApptDate = "& cdate(me.txtAppDate)<>0)'<------You need a closing bracket


There is a sample database re reservations here
You may find some info that is helpful

Good luck.

[JackieFeng]

..This is my first time using code.. I closed bracket and it worked.. but another error message shown 'Only comments only appear after End Sub, End function.........

[CJ_London]

bracket is in the wrong place

......& cdate(me.txtAppDate))<>0

Also you need some lessons on how to structure code

This link may help - it is for excel vba but the principle is the same

https://www.youtube.com/watch?v=aQKXzOJ4jSE