Filling ComboBoxs from a common Function

[BrettM]

I am creating a simple Timesheet that will require users to select their start and finish times etc from a ComboBox which will give them selection options in 15 minute intervals. I want to fill the ComboBox selections automatically with a common Function call.

My problem is that I am having difficulty passing the ComboBox name to the Function. Can anyone tell me what is wrong please?

The Function is...

 Public Function FillComboTime(ctlCombo As ComboBox)
    For i = 0 To 95
        ctlCombo.AddItem Item:=Format(0 / #12:15:00 AM# * #12:15:00 AM# + i * #12:15:00 AM#, "HH:MM"), Index:=i
    Next i
End Function

And I am attempting to call it from the On Enter event of the ComboBox as

Private Sub Login1_Enter()
    FillComboTime (Me.Login1)
End Sub

I keep getting "Object Required" when I enter the ComboBox. Is my logic wrong?

 

[namliam]

Why do it the hard way?

If you have combo's that require this info, why not make a table for it? Then base the combo of the table!
Much much easier.

As for you "object required" this is because you are calling a function. A function when called via FillComboTime (Me.Login1) will want to return something to the caller.
MyReturn = FillComboTime (Me.Login1) will solve your problem
or call the function without the ()
FillComboTime Me.Login1
And/Or change your function to a sub, as functions are meant to be used to return values, but for this it is not needed.

Good luck !

 

[BrettM]

Thanks namliam,

No particular reason for doing it this way - just thought it was a little cleaner and more versatile.

Thanks for the tip about the brackets. It works great now. Cleaned up a little too.

Public Sub FillComboTime(ctlCombo As ComboBox)
    For i = 0 To 95
        ctlCombo.AddItem Item:=Format(i * #12:15:00 AM#, "HH:MM")
    Next i
End Sub

Regards Brett

ps. The scales have been weighted in your favor.
pps. If the above code looked familiar, it should. You suggested it to me last week.

 

[namliam]

If I gave you this code I must have said something about tables as well, or??
Anyways if you have a continued question on the same subject you should keep the same thread, not start a new one...

Now for you 'current' 15 minutes:

This will round down to the previous 15 minutes
format((int(time()/#0:15:00#) * #0:15:00#),"hh:mm:ss")
i.e. 15:52 => 15:45

To round up
format(((int(time()/#0:15:00#)+1) * #0:15:00#),"hh:mm:ss")
i.e. 15:52 => 16:00

To get the nearest 15 minutes.
format((round(time()/#0:15:00#) * #0:15:00#),"hh:mm:ss")
i.e. 15:52 => 15:45
but 15:53 => 16:00

 

[BrettM]

Thanks namlian.

You were quick. Got the answer to me before I was able to finish re-editing my post.

This was originally not the same question so didn't see the need to continue with the original.

Here is my final solution to getting a ComboBox to display the current time rounded to 15 minutes with a 24 hour incremented dropdown. In case anyone else is interested.

Public Sub FillComboTime(ctlCombo As ComboBox)
    ctlCombo.RowSource = ""
    For i = 0 To 95
        ctlCombo.AddItem Item:=Format(i * #12:15:00 AM#, "HH:MM")
    Next i
End Sub

called from

Private Sub Login1_GotFocus()
    FillComboTime Me.Login1
    Me.Login1 = Format(Round(Time() / #12:15:00 AM#) * #12:15:00 AM#, "HH:MM")
    Me.Login1.Dropdown 'Optional
End Sub

Thanks again for your assistance.

 

[namliam]

You can also set the default value in the sub...

Public Sub FillComboTime(ctlCombo As ComboBox)
    ctlCombo.RowSource = ""
    For i = 0 To 95
        ctlCombo.AddItem Item:=Format(i * #12:15:00 AM#, "HH:MM")
    Next i
    ctlCombo = Format(Round(Time() / #12:15:00 AM#) * #12:15:00 AM#, "HH:MM")
    ctlCombo.Dropdown 'Optional
End Sub