How to prevent data entry in a sub-form where Date, Time, and Doctor already exists

SteveL13
SteveL13 used Ask the Experts™
on
I'm trying to figure out how to prevent data entry in a sub-form where Date, Time, and Doctor already exists.

The user tries to select a Doctor from a combobox and if that combination already exists give the user a warning message that they have already scheduled that doctor for that date and time.

The field names are Date, Time, and Doctor.  The table the subform is bound to is tblPotenialAppointmentDatesAndTimes.  The field names in the table are AppointmentDate, AppointmentTime, and DoctorName.  Can someone help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
In the BeforeUpdate event of the ComboBox you just pickup the value selected (along with the other Columns) and if matches the existing values simply Cancel =True.

Author

Commented:
I don't know how to "In the BeforeUpdate event of the ComboBox you just pickup the value selected (along with the other Columns) and if matches the existing values simply Cancel =True."
John TsioumprisSoftware & Systems Engineer

Commented:
Private Sub YouCombo_BeforeUpdate(Cancel As Integer)
if yourcombo= me.doctorid then
Cancel=true
else
end if
End Sub

Open in new window

As for the point you can ask for help from mods
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Is not working.  Here is what I have:

Private Sub Doctor_BeforeUpdate(Cancel As Integer)

    If Doctor = Me.DoctorName Then
        Cancel = True
    Else
    End If

End Sub

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
Is "working"  but probably you are comparing different fields...
The combo probably on the bound column holds the ID of the doctor  (e.g 7 -->Steve) and on the textbox it has the name  (Steve)
Probably on the 2nd Column you hold the Doctor's name so you can test the equality like this :
if Doctor.Column(1) = Me.DoctorName Then

Open in new window

Author

Commented:
Tried that.  Still doesn't work.  When I select a doctor I can't leave the field and I get no warning that the doctor is already scheduled for that date and time.
John TsioumprisSoftware & Systems Engineer

Commented:
Now you got me confused:
Just put a Msgbox to indicate that you actually picked the doctor and is a match...then you can clear the selection and force the user to select again...

Author

Commented:
Tried that with...

    If Doctor.Column(1) = Me.DoctorName Then
        MsgBox "The doctor you have chosen already has a scheduled event in this time slot."
        Me.Doctor = ""
        Cancel = True
    Else
    End If

Open in new window


And no matter what doctor I choose, the form is locked until I clear the selected doctor.
John TsioumprisSoftware & Systems Engineer

Commented:
Check if this helps
If Doctor.Column(1) = Me.DoctorName Then
        MsgBox "The doctor you have chosen already has a scheduled event in this time slot."        
        Cancel = True
        Me.Doctor.Undo
    Else
    End If

Open in new window

Author

Commented:
The field does clear now when I try to select a doctor that is already scheduled for the time slot.  But if I select another doctor that hasn't been scheduled for the time slot I get the same msgbox.
John TsioumprisSoftware & Systems Engineer

Commented:
Something must slipped in your code....the msgbox should pop only if you have equality
Distinguished Expert 2017

Commented:
When you have a combination of three fields that determine existence, you can't use the CONTROL level events to do the validation since all three values are not available when the events for the first control run so your results will be incorrect.  The validation must be done in the FORM's BeforeUpdate event.

You can use a dCount() to find the existing appointment.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strWHERE As String
    If Me.txtAppointmentDate & "" = "" Then
        MsgBox "Appointment Date is required.", vbOKOnly
        Cancel = True
        Me.txtAppointmentDate.SetFocus
        Exit Sub
    End If
    If Me.txtAppointmentTime & "" = "" Then
        MsgBox "Appointment Time is required.", vbOKOnly
        Cancel = True
        Me.txtAppointmentTime.SetFocus
        Exit Sub
    End If
    If Me.cboDoctorName & "" = "" Then
        MsgBox "Doctor Name is required.", vbOKOnly
        Cancel = True
        Me.cboDoctorName.SetFocus
        Exit Sub
    End If
    strWHERE = "AppointmentDate = #" & Me.txtAppointmentDate & "# AND AppointmentTime = #" & Me.txtAppointmentTime & "#" And DoctorName = " & Me.cboDoctorName"
    If DCount("*", "tblPotenialAppointmentDate", strWHERE) > 0 Then
        MsgBox "This doctor is already booked for this time slot.  Please choose a different doctor or a different time/date.", vbOKOnly
        Cancel = True
        Me.cboDoctorName.SetFocus
        Exit Sub
    End If
    
End Sub

Open in new window

Author

Commented:
Pat: I'm getting an error:  "Application-defined or object-defined error".  Am using:

Dim strWHERE As String
    If Me.Date & "" = "" Then
        MsgBox "Appointment Date is required.", vbOKOnly
        Cancel = True
        Me.Date.SetFocus
        Exit Sub
    End If
    If Me.Time & "" = "" Then
        MsgBox "Appointment Time is required.", vbOKOnly
        Cancel = True
        Me.Time.SetFocus
        Exit Sub
    End If
    If Me.Doctor & "" = "" Then
        MsgBox "Doctor Name is required.", vbOKOnly
        Cancel = True
        Me.Doctor.SetFocus
        Exit Sub
    End If
    strWHERE = "AppointmentDate = #" & Forms!frmScheduleHeader.Form.Date & "# AND Forms!frmScheduleHeader.Form.Time = #" & Forms!frmScheduleHeader.Form.Time & "#" And DoctorName = " & Me.cboDoctorName"
    If DCount("*", "tblPotenialAppointmentDate", strWHERE) > 0 Then
        MsgBox "This doctor is already booked for this time slot.  Please choose a different doctor or a different time/date.", vbOKOnly
        Cancel = True
        Forms!frmScheduleHeader.Form.DoctorName.SetFocus
        Exit Sub
    End If

Open in new window

Distinguished Expert 2017

Commented:
Sorry Steve, I am not a compiler.  What line of code is causing the error?
John TsioumprisSoftware & Systems Engineer

Commented:
Simpler

    If len(Me.Date) =0 Then

Open in new window

similar for the others

Author

Commented:
Pat:  strWHERE = "AppointmentDate = #" & Forms!frmScheduleHeader.Form.Date & "# AND Forms!frmScheduleHeader.Form.Time = #" & Forms!frmScheduleHeader.Form.Time & "#" And DoctorName = " & Me.cboDoctorName"
Distinguished Expert 2017
Commented:
Date and Time are both function names so are a poor choice as object names.  Sorry I forgot to mention that earlier.

There is a typo in the second line.  You have an extra " between the pound sign and the AND.  And also an extra " at the end.

It should be:

strWHERE = "AppointmentDate = #" & Forms!frmScheduleHeader.Form.Date & "# AND Forms!frmScheduleHeader.Form.Time = #" & Forms!frmScheduleHeader.Form.Time & "# And DoctorName = " & Me.cboDoctorName

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial