Hide time from cbodropdown if current time > and date is today

[k3ll1n]

Hey all,

I'm still trying to learn but my needs are surpassing my studies at the moment. I'm hoping someone can take a look at the code below and let me know where to make the adjustment so that;

the time (i.e: 2:00 PM) is not listed in the cboAvailability dropdown if it is currently 2:01 PM. Currently if it's 3:00 PM, my clients are able to book 2:00, 2:30 etc. I would like hide this only if they select today's date. I wouldn't want it to hide 2:00 PM also for tomorrow let's say.

Does that make sense? I'm having a hard time trying to place it in this code below that I was originally assisted with.

Thank you kindly.
k3ll1n

Public Function GetExcludedTimes(vDate As Date) As String

Dim db As DAO.Database
Dim rs  As DAO.Recordset
Dim strSql  As String
 
Dim excluded As String
 
strSql = "Select AppointmentTime from tblAppointments where AppointmentDate = #" & Format(vDate, "dd\/mm\/yyyy") & "#"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql)

If rs.BOF And rs.EOF Then
GetExcludedTimes = "0"
Else

Do Until rs.EOF
excluded = "#" & rs!AppointmentTime & "#" & "," & excluded
excluded = Replace(excluded, "##", "")
rs.MoveNext
Loop
GetExcludedTimes = excluded
End If

MyExit:
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

Public Sub GetCboDates(VarDate As Date)

Dim db As DAO.Database
Dim rs  As DAO.Recordset
Dim strSql  As String
Dim StrCrit As String

StrCrit = GetExcludedTimes(VarDate)

If StrCrit = "0" Then
strSql = "select * from  tblAppointmentHoursDaily"
Else
strSql = "select * from  tblAppointmentHoursDaily where Hours not in (" & StrCrit & ")"
End If

'Screen.ActiveForm.cboAvailability.RowSource = ""
Forms!frmMainNavigation!NavigationSubform.Form!cboAvailability.RowSource = ""
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSql)

Do Until rs.EOF
'Debug.Print rs!Hours
'Screen.ActiveForm.cboAvailability.AddItem rs!Hours
Forms!frmMainNavigation!NavigationSubform.Form!cboAvailability.AddItem rs!Hours
rs.MoveNext
Loop

 
MyExit:
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[Cronk]

It seems you have a form with two combos, one where the date is selected and the other on a sub form which displays a list of available times.

The easiest way then is to change the row source of the time combo in the after update event of the date combo

if me.cboDate = Date() then
       me!NavigationSubform.Form!cboAvailability.RowSource = "Select AppointmentTime from tblAppointments where AppointmentTime>TimeValue(Now()) order by appointmentTime"
else
      me!NavigationSubform.Form!cboAvailability.RowSource = "Select AppointmentTime from tblAppointments appointmentTime"
end if

 

[k3ll1n]

The only cbos I have on the form are the Availability and UserID. No date cbo.

Attempting what you suggested on the txtbox did not work unfortunately.

 

[Cronk]

Post the code that did not work.

 

[k3ll1n]

Attached. FillApptCombo was already in the after update.

 

[Cronk]

I had assumed your date text box was in your frmMainNavigation. If not you'd need to go back to your original reference instead of using Me!

if me.cboDate = Date() then 
 Forms!frmMainNavigation!NavigationSubform.Form!cboAvailability.RowSource = "Select AppointmentTime from tblAppointments where AppointmentTime>TimeValue(Now()) order by appointmentTime"
else
 Forms!frmMainNavigation!NavigationSubform.Form!cboAvailability.RowSource = "Select AppointmentTime from tblAppointments appointmentTime"
end if

 

[k3ll1n]

I had assumed your date text box was in your frmMainNavigation. If not you'd need to go back to your original reference instead of using Me!

if me.cboDate = Date() then 
 Forms!frmMainNavigation!NavigationSubform.Form!cboAvailability.RowSource = "Select AppointmentTime from tblAppointments where AppointmentTime>TimeValue(Now()) order by appointmentTime"
else
 Forms!frmMainNavigation!NavigationSubform.Form!cboAvailability.RowSource = "Select AppointmentTime from tblAppointments appointmentTime"
end if

Appreciate the help Cronk
I've changed it to Forms! and I'm no longer getting an error. Though it doesn't seem to be doing much. I still have all the times that weren't booked today listed. As it's 9pm now, I should be able to select 2:00, 2:15, etc.

 

[Cronk]

You wrote
I should be able to select 2:00, 2:15, etc

Your original post said you did not want to see time slots before the current time on the same day.

Did you put a break point in your code to see that the code is being executed when you expect it?

Incidentally, I hope you corrected my line

if me.cboDate = Date() then

to use the name of your text box with the booking date

 

[k3ll1n]

Sorry, I did mean they should not be able to select it.

I also changed the me.cbodate to Me.txtAppointmentDate

It's acting as though nothing was done to it. I'm not familiar with break points. I am familiar with this cbo not displaying should anything be wrong lol.

 

[Cronk]

To set a break point, see http://www.techonthenet.com/access/tutorials/vbadebug/debug02.php
You can also select the line, and press F9 to toggle on/off, or select on the menu bar in the code window Debug |Toggle Break

To check the sql being applied to the drop down, add a line after the end if

debug.print Forms!frmMainNavigation!NavigationSubform.Form!cboAvailability.RowSource

(For more infor see http://www.baldyweb.com/ImmediateWindow.htm)