Avatar of SteveL13
SteveL13
Flag for United States of America 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?
Microsoft Access

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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

Your help has saved me hundreds of hours of internet surfing.
fblack61
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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question