VBA help for allowing only 1 selected appointment time.

[Nikko]

Evening everyone,

New to the forums and also new to the vba world. I'm hoping someone may be able to direct me on how I am able to achieve this.

Currently I'm trying to create a quick appointment schedule.

We allow for 15 minute appointments between the hours of 2:00 PM and 4:00 PM.

I would like it if someone chose the appointment time 2:15 PM within the combo box, it would not show up again for that day unless that appointment was deleted.
I've had some help with the code below, but I am unable to get it to only recognize 1 time slot per day. This code allows me to select 2:15 multiple times.

Sub cboTime_enter()   
   Dim dteTime As Date
    
   Me.cboTime.RowSource = ""
   dteTime = #2:00:00 PM#
   Do While dteTime <= #4:00:00 PM#
      If dteTime <= Time() Then
         Me.cboTime.AddItem Format(dteTime, "h:nn AMPM")
      End If
      dteTime = DateAdd("n", 15, dteTime)
   Loop
   
End Sub

Any assistance is appreciated.
Thanks.

[ranman256]

the query in the combo box must be an outer join. qsFreeTime.
To find the time in available Time but not in qsAppts (used time)

qsAppts, is a query on the already existing appts for that day. 215 is in it.
now make another query, qsFreeTime,
in it bring in qsAppts and the table with all times,tTimeBlock.
join the 2 on TIME, dbl-click the join line,
set the property to ALL items in tTimeBlock, SOME items in qsAppts. (outer join)
bring down TIME field from both , tTimeBlock.Time , qsAppts.Time (this one doesnt have to be visible)
set qsAppts.Time IS NULL

no only available blocks will show in combo.

[Nikko]

This makes sense. Unfortunately I do not have time this morning to work on it.
I appreciate the feedback and I'll get to work on this today at some point.

Thanks ranman.

[Nikko]

the query in the combo box must be an outer join. qsFreeTime.
To find the time in available Time but not in qsAppts (used time)

qsAppts, is a query on the already existing appts for that day. 215 is in it.
now make another query, qsFreeTime,
in it bring in qsAppts and the table with all times,tTimeBlock.
join the 2 on TIME, dbl-click the join line,
set the property to ALL items in tTimeBlock, SOME items in qsAppts. (outer join)
bring down TIME field from both , tTimeBlock.Time , qsAppts.Time (this one doesnt have to be visible)
set qsAppts.Time IS NULL

no only available blocks will show in combo.

This is great. The only issue I seem to have now is that I'm unable to see the used times (of today) as available (tomorrow) when I change the date of the appointment.

ex: 2:00 PM is used today.
If I were to change the calendar picker to tomorrows date, 2:00 PM should be visible.

I have attempted to try a similar query as you said but for appointment times. I was unsuccessful.

I have attached my current db if that is any help.

Thanks ranman. You've already helped a great deal.

schedule.mdb