asked on

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

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?

John Tsioumpris

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.

SteveL13

ASKER

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 Tsioumpris

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

SteveL13

ASKER

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 Tsioumpris

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

SteveL13

ASKER

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 Tsioumpris

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...

SteveL13

ASKER

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 Tsioumpris

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

SteveL13

ASKER

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 Tsioumpris

Something must slipped in your code....the msgbox should pop only if you have equality

PatHartman

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

SteveL13

ASKER

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

PatHartman

Sorry Steve, I am not a compiler. What line of code is causing the error?

John Tsioumpris

Simpler

    If len(Me.Date) =0 Then

Open in new window

similar for the others

SteveL13

ASKER

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

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial