I had posted this in Access Forms and Access Other Topics, but didn't receive any responses; hopefully this is the correct forum for my question.
Environment: Access 2003 ADP (SQL Server 2K backend)
----------------------------
A little background: I'm building an app that allows a third party to schedule appointments between doctors and patients.
I've created a form that allows the user to choose the patient (claimant), then choose a doctor (provider), then enter the appointment date and time, along with "units" (15 minutes of time). I have the following tables in my view:
tblClaimant
tblProvider
tblSchedule (dates that provider is working)
tblScheduleDetail (hours provider is working within that date, and in what office)
tblAppointments (links to tblProvider and tblClaimant, contains appt. date)
tblAppointmentDetails (appt. start time, units, type of appt.)
What I need to do is, when the user enters an appointment, validate that appointment date and time against the provider's schedule (so if the provider isn't working on that day, or at that time, a MsgBox would notify the user and disallow the entry). I'd also like to prevent adding more than one claimant to an appointment time slot (so claimant John Doe can have 1 or 2 slots at 8:15, but nobody else can have that time with that doctor on that date).
I hope this is enough info. If not, let me know and I'll post whatever you need.
Thanks in advance for any help.
-------------
Kyle
Environment: Access 2003 ADP (SQL Server 2K backend)
----------------------------
A little background: I'm building an app that allows a third party to schedule appointments between doctors and patients.
I've created a form that allows the user to choose the patient (claimant), then choose a doctor (provider), then enter the appointment date and time, along with "units" (15 minutes of time). I have the following tables in my view:
tblClaimant
tblProvider
tblSchedule (dates that provider is working)
tblScheduleDetail (hours provider is working within that date, and in what office)
tblAppointments (links to tblProvider and tblClaimant, contains appt. date)
tblAppointmentDetails (appt. start time, units, type of appt.)
What I need to do is, when the user enters an appointment, validate that appointment date and time against the provider's schedule (so if the provider isn't working on that day, or at that time, a MsgBox would notify the user and disallow the entry). I'd also like to prevent adding more than one claimant to an appointment time slot (so claimant John Doe can have 1 or 2 slots at 8:15, but nobody else can have that time with that doctor on that date).
I hope this is enough info. If not, let me know and I'll post whatever you need.
Thanks in advance for any help.
-------------
Kyle