Time Drop Down

[CanWest]

Ok I have a feeling that this is real simple but I am drawing a complete blank. I have searched here in the forums to no avail so I am asking.

I have a Time field on a form
I want to have a drop down with times incremented every 15 min

I tried the lookup wizard but it does not work on dat and time fields.

Any SUggestions?

 

[vbaInet]

You do realise that's going to be a long list of times there?

Approx 4*23 ~ 93 selections.

It would have to be done in code by looping until you get to 24, and adding using DateAdd() function. A fictitious date will do.

You will then need to programatically add the times to the combo box.

 

[ted.martin]

So you want to populate the Combo Value List with time. One thing you may wish to consider when incrementing by 15 minutes; do you mean exactly 15 minutes e.g if Now is 17:21, the next value would be 17:36 or are you rounding up to quarter of an hour intervals i.e. 17:45.

Either way, you will need to use, perhaps, some event to trigger the change in the value list e.g. form Load. I suggest you think through the above and then use some VBA to code up the combo box RowSource property.

 

[DCrake]

Why not create a simple table with all the 15min intervals in for the time range in your app. This may be 24hrs or could be between 8am to 6pm. This way you can limit the selections and can be called easier.

 

[CanWest]

You do realise that's going to be a long list of times there?

Approx 4*23 ~ 93 selections.

It would have to be done in code by looping until you get to 24, and adding using DateAdd() function. A fictitious date will do.

You will then need to programatically add the times to the combo box.

Actually I only need it from 9:00 Am to 4:00 PM. I do not know how to do this at all.

 

[vbaInet]

In that case, do as DCrake suggested. Create all the times and filter off BETWEEN 9 and am and 4 pm. Use that as the row source of your combo box.

 

[CanWest]

In that case, do as DCrake suggested. Create all the times and filter off BETWEEN 9 and am and 4 pm. Use that as the row source of your combo box.

Actually I did that. When I tried to use the lookup wizard in the table design, access would not allow a lookup on a date/time field. So then onmy form i created a combo box and then in the after update event set the value of the time field (EventStartTime) to the value of the Combo Box (cboStartTime1) using this line of vba code

    Forms!pfrm_ScheduledEventAdd!EventStartTime = Forms!pfrm_ScheduledEventAdd!cboStartTime

I also attempted to let the wizard set the value to the field. Both methods tell me the value entered is not correct for this field. Both the field and the combo box are set to short date

 

[Poppa Smurf]

Post this code in the LOAD Event of your form. In the code show_time is the name of the combo box used to display the times change this name to the name of your combo box.

The drop down is displayed in hh:nn:ss AM format you will need to play with the format to show hh:nn AM in the drop down.

Private Sub Form_Load()
Dim intx As Integer
Dim strRowSource As String

Dim display_time As Date

'set the record source of date_requested to show the next ten dates after the current date
display_time = #9:00:00 AM#

For intx = 0 To 28
strRowSource = strRowSource & display_time & ","
display_time = DateAdd("n", 15, display_time)
Next intx
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
show_time.RowSource = strRowSource
End Sub

Alternatively you could use a table with all the times listed and use the table as the record source of your drop down.

 

[CanWest]

Post this code in the LOAD Event of your form. In the code show_time is the name of the combo box used to display the times change this name to the name of your combo box.

The drop down is displayed in hh:nn:ss AM format you will need to play with the format to show hh:nn AM in the drop down.

Private Sub Form_Load()
Dim intx As Integer
Dim strRowSource As String

Dim display_time As Date

'set the record source of date_requested to show the next ten dates after the current date
display_time = #9:00:00 AM#

For intx = 0 To 28
strRowSource = strRowSource & display_time & ","
display_time = DateAdd("n", 15, display_time)
Next intx
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
show_time.RowSource = strRowSource
End Sub

Alternatively you could use a table with all the times listed and use the table as the record source of your drop down.

I have tried both of these methods. I keep getting the error message "The value you entered is not valid for this field. It is a date/Time field but I still keep getting this error. I am at a bit of a loss as to why.

 

[Poppa Smurf]

Attached is a sample database with a form that has two comboxes , one display the drop down using code the other displays the drop down using a table.

If you are still having problems post your form to the forum.

 

[DCrake]

I think the error is arising from the fact that his underlying bound control source is a date/time data type and he is attempting to enter a string value into his field. It maybe that he nees to convert the value in the combo to a date/time format or change the field type to a string.

 

[CanWest]

I think the error is arising from the fact that his underlying bound control source is a date/time data type and he is attempting to enter a string value into his field. It maybe that he nees to convert the value in the combo to a date/time format or change the field type to a string.

Close. I finally figured it out. In my combo box's source I had included two fields. The bound column was not a date field hence the error. I reduced it to a single column, the one that is a date field and all works perfectly now. Many thanks for everyones help.

 

[Poppa Smurf]

Your welcome, now you can progress on your project.