Advancing unbound combo box

[Rig20]

Hello,

I’m sure this is a relatively easy fix, but I have an unbound combo box on a form that runs numerous queries in the After Update section. Everything there works fine. All I need to do is have a command button on the form that advances the focus of the combo box to the next record in the query its row source is based on (plus I’m sure it will need an error catcher when it gets to the end of the list). I know how to advance the form’s record with a button, but neither the form nor the combo box are bound. I’m sure someone knows off the top of their head, so I thought I would ask, since googling for an hour has turned up no results.

Thanks in advance.

[JConsulting]

Hello,

I’m sure this is a relatively easy fix, but I have an unbound combo box on a form that runs numerous queries in the After Update section. Everything there works fine. All I need to do is have a command button on the form that advances the focus of the combo box to the next record in the query its row source is based on (plus I’m sure it will need an error catcher when it gets to the end of the list). I know how to advance the form’s record with a button, but neither the form nor the combo box are bound. I’m sure someone knows off the top of their head, so I thought I would ask, since googling for an hour has turned up no results.

Thanks in advance.

for clarity sake...you want to select the first item in the combo box...it runs queries. You push a button and the next item in the combo box is "clicked"...etc...until all items are done.?

[Rig20]

for clarity sake...you want to select the first item in the combo box...it runs queries. You push a button and the next item in the combo box is "clicked"...etc...until all items are done.?

Well, my combo box works perfectly and runs all the code that I have set up for it. All I want to do is have a command button that takes the combo box to the next record (in the drop down list) rather than clicking combo, selecting the next record, etc. My form is designed to look through many records so to be able to go to the next record in the combo quickly would be handy. For the button, I don't think I can use:

DoCmd.GoToRecord , , acNext

because my form is unbound. Somehow I need to reference the combo and have it go to the next record in its list.

Thanks for your help.

[JConsulting]

Well, my combo box works perfectly and runs all the code that I have set up for it. All I want to do is have a command button that takes the combo box to the next record (in the drop down list) rather than clicking combo, selecting the next record, etc. My form is designed to look through many records so to be able to go to the next record in the combo quickly would be handy. For the button, I don't think I can use:

DoCmd.GoToRecord , , acNext

because my form is unbound. Somehow I need to reference the combo and have it go to the next record in its list.

Thanks for your help.

put this in a code module

Function AdvanceCombo(strForm As String, strControl As String)
'This function clears all entries on all listboxes on the form strForm
Dim c As Control
Dim F As Form
Dim I As Long
Dim cValue As String
Set F = Forms(strForm)
Set c = F(strControl)
cValue = c.Value
For I = 0 To c.ListCount - 1
If c.ItemData(I) = cValue Then c.Value = c.ItemData(I + 1)
Next I
End Function
 

for your button code

Private Sub Command15_Click()
AdvanceCombo Me.Name, "cboTest" '<---replace cbotest with your combo name
End Sub
 

J

[Rig20]

Shiny! Thanks for your help. I don't think I would have discovered that on my own.

BTW, I had make the button redo the After Update of the combo, because it did not do it automatically, just in case anyone else has a similar problem. The finished button looks like this:

Private Sub cmdnext_Click()
AdvanceCombo Me.Name, "cboName"
cboName_AfterUpdate
End Sub

Thanks again!

[Aviqq]

Do you have a code for doing the opossite? I would like to go to previous record rather than advance.

Also, If the combobox is null, is there anyway to go to the first record in the combobox rather than an error popping up? Currently I get Run-time error '94' Invalid use of Null. This also occurs when there is no more selections available in the combobox. Could the selections wrap around back to the begging? Thank you.

[jimatqsi]

Rig20,
You have hit on one of the real annoying features of a combo box. You have to click, scroll and click to make a choice. I rarely place a combo box on my forms anymore because of that.

You may have avoided this problem altogether by using a list box instead of a combo box. It depends on how much space you've got on your form, and perhaps the number of rows in the combo box and maybe some other things unique to your particular situation. But, if you had a list box instead of a combo box, and the choices were not too numerous, that list box would have served as your command button right there. Just click the member of the list and launch the same code you would have with the combo box.

Food for thought in the future!

Jim