Appointments for medical office

[ghacig]

Hi

I tried to fined an answer in the previous posts but couldn't Hope somebody can help. The database is a comprehensive medical records database for a medical office and I need to create a form for the secretaries to schedule appointments for patients. The appointments are scheduled in a table called ClinicVisit. The record contains:
ClinicVisitID as Autonumber,
PatientID as number
ClinicDate as Date
ClinicTime as medium time



I created a separate table that contains the time slots:
SlotID Auto
SlotTime text

The slotTime are populated with time intervals:
9:00 - 9:30
9:30 - 10:00
etc.

Here what I need:

When you open the form to schedule the appointment for a particular patient, you need to see a list of all slots with those that are already filled shown as unavailable.

I would appreciate any help. Also, if this topic has been discussed before, I appreciate help finding the thread. Thx.

 

[MajP]

I would add a couple fields to your time slots. Even if you do not use them now. Someone is going to put more requirements on you. And if you need to do any real ordering or calculations on the intervals in the future.

SlotID Auto
SlotTime text
startTime (date time)
endTime (date time)

1 08:00-08:30 8:00 am 8:29 am
2 08:30-09:00 8:30 am 8:59 am
...

Also
ClinicVisitID as Autonumber,
PatientID as number
ClinicDate as Date
slotID 'do not need a time here

There are several ways to do what you want. The nicer the interface the harder it is to do.
One way would be like this. On your form you have some kind of unbounded datepicker to allow you to choose the date. You pick a date. You have a subform that is linked to the main form by
clinicDate to datepicker control. Yes you can link a subform to a control on the mainform. The subform would show all slots and who is assigned. This would be your visit table. You could use conditional formatting to hilite the available, and disable the already selected ones.
But the first time you go to a date there is no related records. So the trick is when you select a specific date if there are no related records you run an insert query to put a record in visit table for each slot. Every time you go to a new day, the first time it would run the insert query putting all slots (with no patient id) into the visit table. Now you can fill in the patient id.

Now you want to be a little careful here and not fire the code each time they change the date picker or you could get a lot of place holder records. So if they go in the past or a long time in the future do not insert the placeholders. Also you probably want to run a delete query for appointments not filled in the past.

Another option is an unbound form.
Another is to have a control to pick a slot and use query where slotID not in clinic visit where date is the selected date. That would only allow them to pick available slots.