How to use vba to select an item from the drop list of a combo box

[Paul]

How to use vba to select an item from the drop lost of a combo box. The drop
list is based on a table with two columns, ID field (PK) the first column
and the Name field the second column. The first column is the bound field
for the combo box and it is set to hidden by minimize the width of the
column from the user. Also I need to use the combo box itself as a parameter
on a query, so that it important that when assigning the value using vba to
the combo box it will pick up the ID bound field for the value. Thanks

 

[Ken Snell [MVP]]

You can set a combo box to a specific item in its list:

Me.ComboboxName.Value = Me.ComboboxName.ItemData(x)

replace x with a number from 0 to the (.ListCount - 1) value for the combo
box. 0 is the first item in the list.

 

[Paul]

Hi Ken:

The problem is that I would not know the number (location) of the item I
want to select in the drop list and it could be anywhere from 0 to 200 in
the drop list. However I do know the exact value (wording) of it at the
column 2 of the drop list. Can I instruct Access to filter out the item in
the column 2 of the drop list and select it for the combo box value once
found.

 

[Ken Snell [MVP]]

You could use the DLookup function for getting the DefaultValue if the
second column has unique values:

=DLookup("NameOfFieldInColumn1", "TableName", "NameOfFieldInColumn2='" &
[ComboboxName].[Column](1) & "'")