Listbox SQL

[JIM]

Here's my code to fill a list box when a combo box is updated:
Private Sub cboCustomerName_AfterUpdate()
Dim sql As String
sql = "SELECT tblCustomer.CustomerName, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2 FROM tblCustomer INNER JOIN tblClientBuildings
ON tblCustomer.CustomerName = tblClientBuildings.CustomerName ORDER BY
tblCustomer.CustomerName, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2;"
Me!lstBuildings.RowSource = sql
End Sub

When I test a sample query with same sql it works perfect but the listbox is
just blank. The records are in the order desired but I don't know how to
just select the Buildings that are associated with the Customer Name that was
selected in the combo box. Any help appreciated.

 

[Al Campagna]

Jim,
You'll need a Where clause to criteria the query...
(aircode... use your own control names)
Where CustomerName = Forms!frmYourFormName!cboCustomerName
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[JIM]

Thanks Al. We've been busy closing out the year, sorry. Here's my code and
the listbox remains empty:

Private Sub cboCustomerName_AfterUpdate()
Dim sql As String
sql = "SELECT tblCustomer.CustomerName, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2 FROM tblCustomer INNER JOIN tblClientBuildings
ON tblCustomer.CustomerName = tblClientBuildings.CustomerName Where
tblClientBuildings.CustomerName = Forms!frmWorkOrders!cboCustomerName ORDER
BY tblCustomer.CustomerName, tblClientBuildings.JobAddress1,
tblClientBuildings.JobAddress2;"
Me!lstBuildings.RowSource = sql

End Sub

What am I missing?
TIA

 

[JIM]

Hi Al, I have it working. Thanks again. I forgot to specify Rowsource type
for the Listbox properties which is Table/Query.