Help needed with a query or solution. Appointment Booking

[k3ll1n]

Evening all,



This seems to be the final piece to my puzzle but I'm not very experienced in queries yet. I've been researching for days now and hope someone here can shed some light on what I can do to accomplish this.

Currently my setup is like so;

ACCDB : https://www.dropbox.com/s/f6hhrfuk2j...tdb.accdb?dl=0

tblAppointmentHoursDaily

• | Show | Hours |
• | Checkbox | 2:00 PM |
• | Checkbox | 2:30 PM |
• | Checkbox | 3:00 PM |
• | Checkbox | 3:30 PM |

tblAppointments

• | TimeStamp | | AppointmentDate | AppointmentTime | UserID |

qAppointmentHoursAvailability (I was assisted with this query)

• SELECT DISTINCT tblAppointmentHoursDaily.HoursFROM tblAppointmentHoursDaily LEFT JOIN tblAppointments ON tblAppointmentHoursDaily.[Hours] = tblAppointments.AppointmentTime
  WHERE (((tblAppointments.AppointmentDate)<> Date() Or (tblAppointments.AppointmentDate) Is Null) AND ((tblAppointmentHoursDaily.Show)=True));

frmMain

• cboAvailability
  
  • Runs the qAppointmentHoursAvailability query and displays Available appointment times for that day.
  • If the user selects 2:00 PM, the field is refreshed using Me.Recalc so that it reserves the time in the record.

• txtAppointmentDate
  
  • I think this is another point where I'm stuck. Control Source is tblAppointments.AppointmentDate

What I would like to happen is that when a user books a time, say 2:00 PM TODAY, no one else can select that time. But if the user changes the date field to TOMORROW, 2:00 PM is in the cbo dropdown.


Does this make sense? I believe the issue is there's something missing in the query.
I thought I had it at one point where 2:00 PM did show up the next day when taken the day before, but returning to the original date allowed me to select 2:00 PM again. Causing a duplicate.

Any advice would be appreciated.

ACCDB https://www.dropbox.com/s/f6hhrfuk2j...tdb.accdb?dl=0

Cheers,
k3ll1n

[k3ll1n]

Assisted on another forum. A lot of this has since changed. Marking a resolved.