Populate comboboxes based on query

  • Thread starter Thread starter taxidermist
  • Start date Start date
T

taxidermist

I have a textbox on a form that user will enter a string into and
press a button. An example of what they will enter is "BD532313CW".
Once they press the button i take the first three numbers (532 in this
case) and assign them to a string variable named myString.

Next I have 2 combo boxes on a form named cmbAcct and cmbDisp. I have
a table named "Swbs" that has three fields in it Swbs.Acct, Swbs.Disp,
and Swbs.Num. There are duplicates in all fields. I need to populate
cmbDisp with the distinct data in Swbs.Disp where Swbs.Num =
myString. Likewise I need to populate cmbAcct with distinct data from
Swbs.Acct where Swbs.Num = myString.

How can I do this with VBA code?
 
Make the row source for the combo a query:

Dim myString As String
Dim strSQL As String

If Len(Me.[NameOfTextbox] & vbNullString) > 0 Then
myString = Left(Me.[NameOfText], 3)
strSQL = "SELECT DISTINCT Acct, Disp, Num " & _
"FROM Swbs " & _
"WHERE Num = " & myString
Me.NameOfCombo.RowSource = strSQL
End If

This assumes that Num is actually a numeric field. If it's a text field, use

strSQL = "SELECT DISTINCT Acct, Disp, Num " & _
"FROM Swbs " & _
"WHERE Num = '" & myString & "'"

Exagerated for clarity, that last line is

"WHERE Num = ' " & myString & " ' "
 
Use DISTINCT to return one occurance of each value:
This would be the row source for your combo

SELECT DISTINCT Swbs.Disp where Swbs.Num = myString;

It may be necessary to requery the combo in the after update event of
myString (If that is where you are enteing the value).

Me.cmbDisp.Requery
 
Back
Top