Filling A Listbox with a Query in VBA

[bplantes]

Hi All,

I am new to Access programming and it has been a long time since I have done any programming at all, so I am a bit rusty. I am building a form in Access 2002 that displays a list of Customers.

When the form first opens, all Customers are displayed in a Listbox with the Address1, Address2, City, State, Zip, and Customer Number in Textboxes that populate when you select the customer. The listbox has the property "Row Source" set to "qryFillListBox."

This form has a search box and option buttons to search on Name, Address, City, State, or Zip. I cannot figure out how to connect my query to fill the listbox. Here is the code I have so far:

Private Sub cmdSearch_Click()
 
strSQL As String
 
strSQL = "SELECT CustomerMasterTemp.CUSTOMERSYSTEMID, CustomerMasterTemp.CUSTOMERNAME, CustomerMasterTemp.ADDRESS1, CustomerMasterTemp.ADDRESS2, CustomerMasterTemp.CITY, CustomerMasterTemp.STATE, CustomerMasterTemp.POSTALCODE FROM CustomerMasterTemp "
 
Select Case Me.optSearchIn
Case 1
strSQL = strSQL + "WHERE CustomerMasterTemp.CUSTOMERNAME LIKE '%" & Me.txtSearch & "%' "
Case 2
strSQL = strSQL + "WHERE CustomerMasterTemp.ADDRESS1 LIKE '%" & Me.txtSearch & "%' or CustomerMasterTemp.ADDRESS2 LIKE '%" & Me.txtSearch & "%' "
Case 3
strSQL = strSQL + "WHERE CustomerMasterTemp.CITY LIKE '%" & Me.txtSearch & "%' "
Case 4
strSQL = strSQL + "WHERE CustomerMasterTemp.STATE LIKE '%" & Me.txtSearch & "%' "
Case 5
strSQL = strSQL + "WHERE CustomerMasterTemp.POSTALCODE LIKE '%" & Me.txtSearch & "%' "
End Select
 
strSQL = strSQL + "ORDER BY CustomerMasterTemp.CUSTOMERNAME"
 
......... THIS IS THE PART I'M NOT SURE OF ...........
 
End Sub
 

I want to run the query, clear the list box, and put the new data into the listbox.

I would appreciate your help.

Thanks,

BPLANTES

[Stewart Ross]

Hi BPlantes. All you need now is to set the rowsource property for your listbox to be strSQL then requery the listbox to fill it. Add

[listbox control name].RowSource = strSQL
[listbox control name].Requery

substituting for[listbox control name] with the real name of your control.
-Stewart

** Edit **
Fixed using the [ CODE ] tags (and adding an extra CRLF).
Removed the comment explaining (I get what you meant now though ;)).

[bplantes]

This worked great! Thanks for the help. Trying to get back into the developer's mindset :)