Listbox to display certain records only.

[BBUIE]

Hi, I'm new to the world of access, but I've managed to create and almost complete a database (thanks to the help of this forum). Now I have to ask for help. Here it goes...

I have a database that I use to enter information about equipment (serial#, description, vendor, etc) that I order and install for clients.

I am using a form (frmCSTINFO) with a subform (frmEQUIPMENT). In the Form I select a customer name from a combo box (cboCustomerName)and in the subform I enter all the details as mentioned above. This info is stored as a record in a table (tblCST_INVENTORY). Each piece of equipment entered is a single record. Each record has a field called (CST_ID) which is used throughout the database to identify customers.

Here's the problem....
I'm trying to add a listbox to either the form or the subform that will display all the equipment for that particular customer. Currently I am able to get the listbox to display all equipment for all customers (all records in tblCST_INVENTORY). But I need it to only display records which have a CST_ID field equal to the customer I'm viewing currently in the combo box (cboCustomerName). Column 2 in the combo box contains the CST_ID field that I need to reference.

I'm pretty sure this will require some VB to work properly.

Man I hope this makes sense!!

Thank you in advance

 

[Xbox]

First create your SQL query (which sounds like you already have)
Then create a "condition" based on your comboBox selection (cboCustomerName) as such:

  If Not IsNull(Me!cboCustomerName) Then
    strFilter = strFilter & " CustName = '" & Me!cboCustomerName & "'
  End If

Then throw the SQL result in the listbox like this ...

  Me!lstboxName.RowSource = yourSQL & strFilter

There's a great deal of other issues to deal with when you finally get it running. Like what happens if there are no records to show, double clicking on the selected record etc ... but this should send you in the right direction
HTH

 

[Pat Hartman]

I would do it differently. Add criteria to the query that is the rowsource for the listbox:

Where CST_ID = Forms!frmCSTINFO!CST_ID;

Then in the Current event of the form, requery the listbox.

Me.YourListBox.Requery

 

[Robert_Mc]

Hi

You have been offered 2 useful methods, & as I use a slightly different one, here it is:

Use the "on change" event of your combo box & enter the query SQL as the rowsource of the list box (eg. list1.rowsource = "SELECT.........."). In the SQL statement, use the WHERE clause to refer to the value in the combo box (eg. WHERE customer = cboCustomer). In this way, whenever you change the value in the combo box, the list box requeries to show only data for the customer selected.

Hopefully you'll find something that suits out of these!

Have fun

 

[BBUIE]

THank you to everyone for the information.

I went with the method as specified by Pat. Worked like a charm!

Thank you for your help, I was pulling my hair out!!