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 tblPotenialAppointmentDate
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 tblPotenialAppointmentDate
Last Comment
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.
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."
Private Sub YouCombo_BeforeUpdate(Cancel As Integer)
if yourcombo= me.doctorid then
Cancel=true
else
end if
End Sub
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
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 :
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
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.
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...
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...
ASKER
Tried that with...
And no matter what doctor I choose, the form is locked until I clear the selected doctor.
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
And no matter what doctor I choose, the form is locked until I clear the selected doctor.
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
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.
Something must slipped in your code....the msgbox should pop only if you have equality
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.
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
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
Sorry Steve, I am not a compiler. What line of code is causing the error?
Simpler
If len(Me.Date) =0 Then
ASKER
Pat: strWHERE = "AppointmentDate = #" & Forms!frmScheduleHeader.Fo
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
