Select a combobox in VBA (Access 2002/SP2)

Topic

New Reply

Is it possible to make a selection from a combobox dropdown list entirely in VBA code? I have a form with a cbo whose initial selection is known by the application when the form is loaded. For convenience I want to have the program make the initial selection.

I will use the code that I am trying to get working (unsuccessfully) as an example of what I’m trying to do. If you have a solution, in no way has it to resemble my code (which, again, doesn’t work). We start with a table of composer names which contains two fields: txtCompCode and txtCompName. The cbo in question is to be loaded from the table of composer names.

Private Sub Form_Open(Cancel As Integer)
Me![cboComposerNames].SetFocus
‘Comment: This gives the cbo immediate focus and control to cboComposerNames_GotFocus below.
End Sub

Private Sub cboComposerNames_GotFocus()
Me![cboComposerNames].RowSource = _
“SELECT txtCompCode, txtCompName FROM tblComposers WHERE txtCompCode = ‘GERS’;”
‘Comment: As the select criteria “GERS” is known, this SQL creates an internal dropdown list consisting of a single line.
Me![cboComposerNames] = Me![cboComposerNames].Column(1, 0)
‘Comment: This attempts to assign the single dropdown line to the cbo’s listbox. It fails; the listbox remains blank..
Debug.Print Me![cboComposerNames]
‘Comment: This is to show the result of the above assignment. It works!?! Gershwin’s name is printed although the listbox is still blank..
End Sub

(Note. Inserting Me![cboComposerNames].dropdown immediately following the SQL statement does produce the one line list, but, after selecting it, the results are exactly the same as described below.)

The application has now stopped. To get it moving again I click on the cbo’s down arrow and select the single entry for Gershwin. cbo_GotFocus is run again, cbo_AfterUpdate is run and the application continues on using the selected code and name as it should EXCEPT that the cbo’s listbox continues to show blank.

I simply want to know how, on initial entry, to accomplish the selection of the lone dropdown line with its subsequent raising of cbo_AfterUpdate. OR, as mentioned above, something completely different that results in the same thing without user intervention.

Thanks to all of you that will put in time and thought.

John

Reply | Quote

Replies

I think you need to requery it before you display it, try this:

Me![cboComposerNames].requery
Me![cboComposerNames].setfocus
Me![cboComposerNames].dropdown

Also >>>>Me![cboComposerNames] = Me![cboComposerNames].Column(1, 0) <<>>Column(1, 0) <<< I'd be supprised if you can do that!

Try just this: Me![cboComposerNames].Column(1)
or this:: Me![cboComposerNames].Column(0)

Or this might be what you want:
[cboComposerNames].BoundColumn = 1

You can also do this:
[cboComposerNames].ColumnCount = 5
[cboComposerNames].ColumnWidths = "0 in;1 in;1 in;.75 in;.75 in"

Reply | Quote

Rupert,
Concerning my use of the Column property, first see my reply to Hans above. Then second your suggestion to try Me![CboComposerrNames].Column(1) should also work. However when I leave out the row index which this does, I sometimes get an “Invalid use of Null” error message which I’ve never explained. That is why I always use both the column index and the row index even though the example in VBA Help says that a zero row is not needed.

John

Reply | Quote

It looks to me like you’re working too hard. Is this combobox bound to a data field or unbound and what do you mean by the application knowing the initial selection? Are you talking about a bound value or a default of some kind. You wouldn’t normally use the gotfocus event of the combobox to populate it, which is what you appear to be doing. What determines which composer names are included in the list?

Reply | Quote

Charlotte, thanks for jumping in,

Taking your questions out of order, The form containing this cbo is normally activated manually when there is need to edit composer data. It uses this unbound cbo to display the composer table (txtCompCode – ColumnWidth=0″ & txtCompName – ColumnWidth=4″) to let the user select the composer by name. It then uses the matching composer code as keys and foreign keys to data in other tables. That’s the normal operation.

In this special case, another part of the application (call it the Catalog) has discovered an error in some composer data that the user has to correct. Currently the user must shut down the Catalog part, activate the maintenance form and use this cbo to get to the data that needs work. I want to shortcut these steps by loading the maintenance form from within the Catalog to go directly to the data needing correction. Why in this case go through the cbo at all? So that the screen appears the same as it normally does with the composer name in the cbo list box. And why am I using gotfocus to get to the cbo? Because that control is actually second in the TabIndex list. First is an option group control used to narrow down the size of the composer list displayed. Because in this case that list is not displayed, that prior control is bypassed.

I hope that you can wade through all that. I found in my forty years of creating technical briefs that writings which were perfectly clear and simple to me were usually incomprehensible to anyone else. I look forward (with some dread) to your response.

John

Reply | Quote

Try

Me![cboComposerNames] = Me![cboComposerNames].ItemData(0)

or

Me![cboComposerNames] = Me![cboComposerNames].Column(0, 0)

The column index is zero-based.

Reply | Quote

Hans
Thank you Hans for your suggestions. They would both be valid if the field to be assigned to the cbo’s listbox was the composer code. What I want, however, is the composer name field which, as you can see from the SQL SELECT statement, is the second field (in the cbo’s properties ColumnWidth = 0″;4″). To test the validity of my Column(1, 0) reference, I stopped execution at the assignment statement in question and printed it in the Immediate window getting the following:

? Me![cboComposerNames].Column(1, 0)
Gershwin, George (1898-1937)

So the statement is right, it just won’t assign its value to the cbo listbox. And that’s what’s got me stumped.

John

Reply | Quote

If the Bound Column property of the combo box is 1, the value of the combo box corresponds to the composer code field, not to the name. So if you set the value to that in the first column in the first row, you are setting the composer code, but since that is hidden, you’ll see the composer name in the text box part of the combo box. Please try my suggestions.

If the Bound Column property is 2, the value corresponds to the composer name. In that case, I wonder why you have a hidden column with composer code in the combo box.

Reply | Quote

Hans,
Your last reply cleared the air some. I’ve got to sort out the fact that an unbound cbo can have a Bound Column. It is and it does. ( It is 1) I will experiment with this later today.

John

Reply | Quote

What I don’t understand is why you aren’t simply populating the combobox from a query. You could, in fact, use that option group as part of the criteria in the query and you could easily set the option group from the same code that opens the form. In any event, it’s more normal to have the combobox populated *before* it gets the focus, not after.

Reply | Quote