Listbox to show time (1 Viewer)

[access7]

Good Morning

I am hoping someone might be able to give me some advice on the following...

I would like a list box on my form which lists times for the user to pick (increasing in 15 min intervals).

I would like to start time to be 08:00 and the last to be 19:00

I know I will need to use some kind of loop to increment the time intervals but I'm not sure on how to get these to then all appear in the list for the user to pick from??

 

[bob fitz]

Why not put the time values in a table and use the table in the Row Source of the combo box.

 

[access7]

Thanks, that does seem the easier option however I was really hoping to avoid that and increase my knowledge on the VBA side of things.

I have found the following code that I have tried adapting however although the loop is working fine the actual values are just not appearing in my listbox? Can you help on this at all?

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

 

[bob fitz]

Check that the Row Source Type property of the combo box is set to Value List.

 

[access7]

Perfect!! I knew I was close with the code but couldnt work out why it wouldnt show in the box! Thanks! :-D

 

[access7]

The only other thing is I only want it in Short Time format - I tried to put display_time = #9:00# instead but it keeps reverting back...

 

[access7]

I have tried adding
display_time = Format(display_time, "h:n")
But this is not working??

 

[bob fitz]

I've altered the code you posted. The alterations are in red. It appears to work as you require now.

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 & [B][COLOR=red]Left[/COLOR][COLOR=red]([/COLOR][/B]display_time[B][COLOR=red], 5)[/COLOR][/B] & ","
display_time = DateAdd("n", 15, display_time)
Next intx
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
show_time.RowSource = strRowSource
End Sub

 

[access7]

That works perfectly I'm a great believer in not doing anything i dont understand fully though so please could you explain how the 'red' added bits have worked - is it similar to the Trim function; i.e. it is only showing 5 characters (being the 4 numbers and the semi-colon?)

Thanks again for your help on this

 

[bob fitz]

The Left() function is quite straight forward. This is what the Help file says about it. If you need any clarification please post back and I will try to help if I can.

Left Function


Returns a Variant (String) containing a specified number of characters from the left side of a string.
Syntax
Left(string, length)
The Left function syntax has these named arguments:
PartDescriptionstringRequired. String expression from which the leftmost characters are returned. If string contains Null, Null is returned.lengthRequired; Variant (Long). Numeric expression indicating how many characters to return. If 0, a zero-length string ("") is returned. If greater than or equal to the number of characters in string, the entire string is returned.


Remarks
To determine the number of characters in string, use the Len function.
Note Use the LeftB function with byte data contained in a string. Instead of specifying the number of characters to return, length specifies the number of bytes.

There is also a Right() function which is similar. The Mid() function is also worth knowing.