How do match combo box's selected value to text box's value in MC Access VBA?-CodePudding

I wanna change my combo box selected value to the same value as in text box upon button click. For example if I write "IT company" into text box I wanna change combo box selected value to the same as text box's which is "IT company" too.

I tried something like this:

private sub button_click()
Dim i As Long
Dim s As Long
with myCombo
For i = 0 To .ListCount
If .ItemData(i) = Me.txtBox.value Then
s = i
End If
Next i
Me.myCombo.Value = s
    end with

but my combo box ends up with no value selected.

CodePudding user response：

Ok, the way this works?

A combo (or listbox) often will have more then one column.

So, for example, lets fill out a combo box with "id" (the hidden PK value), and then the 2nd column (for display) is hotel name.

So, we have for the combo box

SELECT ID, HotelName FROM tblHotelsA ORDER BY HotelName.

We get this:

So the WHOPPER question then? Is what value do you have the combo box setup to save, and "return/use/see/have" from the above query?

do you have the combo box operate on "id" or does it operate on "hotelName" ???

The setting that controls this is this one:

(it is 1 based, not zero based).

so, what this means?

While the combo box displays the 2nd column, you HAVE to use the first column to set the value of the combo box (that is going to be Hotel "ID", and NOT HotelName).

So, you EITHER translate the given text into a "id", and use that, or you run a loop, and do it this way:

Dim i    As Integer

For i = 0 To Me.cboHotels.ListCount - 1
  
  If Me.cboHotels.Column(1, i) = Me.Text0 Then
      Me.cboHotels = Me.cboHotels.Column(0, i)
      Exit For
  End If
   
Next i

So we have to loop, match the "text part" (column 1 = 2nd column) - (zero based)

now, if the combo box does NOT have multiple columns, and ONLY had the ONE column of text hotel name, then you can directly assign the combo box to the text name you want selected.

So, VERY common setup in which a combo box displays a value, but will store/save/use a different column for the REAL value to be saved. And that REAL value is also what you must assign the combo box when you have a multi-column setup.

I mean, I suppose in place of a loop, the above code could be this:

cboHotels = DLookup("ID", "tblHotelsA", "HotelName = '" & Me.Text0 & "'")

However, in MOST cases, if we dealing with such data, then in our code we rare rare will have the "text" name, and we don't care, since in code behind, we are of course using/storing/dealing with related data - and thus our code behind in most cases should and will wind up using some PK "id" value to set the combo box, and also the value we get from the combo box will also be that PK value.

But, in summary:

The column you "choose" for the combo box to "be bound" to, it is the type and value you thus will use to get and set the combo box. If you need to set the combo box by SOME OTHER column? Then we have to use the above loop, or possible use a dlookup() to translate the given text value into the correct "id" value we are using behind the scenes for the combo box.