Need help - Filter listbox on subform based on selection in main form (1 Viewer)

[mort]

Hi,

I am currently working on a database that register recieved packages. I have a registration form (main form linked with a subform).

In FormA i have a listboxA containing "FormA_ID (primary key)", "arrived_date" and "Lot_number". In FormB i have a listboxB containing "FormA_ID", "type" and "serial_number".

The relationships between these tables are working good.

My problem:
I want to filter the items shown in listboxB (subform) based on the selection the user makes in listbox A (mainform). For example if the user in listboxA picks a package that arrived on a specific date, I want listboxB to show only the items contained in that selected package. Right now listboxB show all items in all the different packages.

I have searched all over and tried different WHERE conditions, but i cannot make this work.

Can someone out there please give me a helping hand? :banghead:

 

[Ranman256]

the query for the list box must include the list on Main form.

select field from table where [package]=forms!myMainForm!listBox

 

[mort]

the query for the list box must include the list on Main form.

select field from table where [package]=forms!myMainForm!listBox

I have tried that - several times in many different ways. It must be something i dont understand. Since the first list is on the main form it uses the main form ID (main_ID). The list on my subform uses sub_ID as primary key, but also it has the main_ID. I try to use this WHERE condition in the listbox on my subform: Main_ID=Forms!myMainForm!listbox

The result is that the listbox in my subform gets empty. I have also tried to requery the listbox in the subform in the after update event on my listbox in main form.

I really appreciate you taking the time to help me!

 

[informer]

Hi mort

A possible solution with parameter query

Create a storage procedure ( for this example myQuery) with parameter which will be called for populate your listboxB

PARAMETERS myParameter Long;
SELECT field1, field2, field3
FROM myTable
WHERE myId = myParameter

On Change event listboxA, add this code

Private Sub listboxA_Change()
 
   With CurrentDb.QueryDefs("myQuery")
        .Parameters("myParameter") = Me.listboxA.Value
        Set Me.listboxB.Recordset = .OpenRecordset
        .Close
    End With
 
    On Error Resume Next ´——> to ovoid error message if there are no items in listboxB
    With Me.listboxB
        .SetFocus
        .ListIndex = 0
    End With
 
End Sub

 

[informer]

Hi mort


An other solution is to set a query on change event listboxA

 Set Me.listboxB.Recordset = "SELECT.... WHERE id="  & Me.lisboxA.value

 

[June7]

Have seen this before. Query doesn't like referencing listbox. Have a textbox on main form with expression to pull listbox value: =[listboxA].[Column](0). Then the RowSource query in listboxB can reference the textbox. Can set the textbox not visible.

 

[informer]

Please, could you explain me why you use a textbox?

 

[June7]

I already stated why: Query doesn't like referencing listbox.

 

[mort]

Thank you everyone for taking the time to answer my question. I am sure that all suggestions are good, its just I cant make any of them work in my database. My biggest issue is that i cant understand why your suggestions wont work. I thing the problem must be in the query rowsource of my listboxB.

I will try to give i new explanation in the hope that one of you might see the problem.

I have 2 connected tables; countries and cities. ID fields are Country_ID and City_ID, with CountryID being the parent-child link between the two tables.

To register new posts i have a main form (countries) with a subform (cities). Right now there are 10 countries with 4 registered cities each in my database.

Now when i look at the registration form i have formA (countries) with 10 countries in listA and formB (cities) with 40 cities in listB. The parent-child link between the two forms are countryID.

The goal is to get listB to only show the cities in the chosen country (from listA).

The rowsource for listB (which i think is wrong) is:
SELECT tbl_Cities.CityID, tbl_Cities.CityName FROM tbl_Cities

The rowsource for listA (if it makes any difference) is:
SELECT tbl_countries.countryID, tbl_countries.CountryName FROM tbl_countries

I have tried many different rowsources, but I cant make any of them work. If anyone can help me I would be a very happy man!

I would also need input on the CBA code to use in the afterupdate event on listA (or whatever would work best).

 

[isladogs]

I haven't followed this from the start so my answer is just based on the last post

I have 2 connected tables; countries and cities. ID fields are Country_ID and City_ID, with CountryID being the parent-child link between the two tables.

Does the Cities table include a CountryID field?
You need to add & populate this if not already done

The goal is to get listB to only show the cities in the chosen country (from listA).

The rowsource for listB (which i think is wrong) is:
SELECT tbl_Cities.CityID, tbl_Cities.CityName FROM tbl_Cities

The rowsource for listA (if it makes any difference) is:
SELECT tbl_countries.countryID, tbl_countries.CountryName FROM tbl_countries

Create a query joining the 2 tables by CountryID field
Add fields CityID, CityName & CountryID
In the CountryID field criteria row enter Forms!YourFormName.ListA
You can make the width of the 3rd column=0 so its hidden if you wish

Now use this query as your row source for listB
It should give you the cities for the selected country only

If it doesn't work, try June's suggestion of setting a textbox (or a variable) equal to the CountryID from listA and use that in the filter criteria for listB instead

I would also need input on the CBA code to use in the afterupdate event on listA (or whatever would work best).

Don't understand what CBA code means: do you mean VBA?

 

[mort]

Yes, cities table includes CountryID field.
Yes, i meant VBA, not VCA (typo).

Thank you so much for your help Ridders, IT WORKS )

Now i need to find out how to refresh the subform so the listbox with cities updates each time a new selection is made in listA (countries). Anyone have any ideas?

 

[isladogs]

Yes, cities table includes CountryID field.
Yes, i meant VBA, not VCA (typo).

Thank you so much for your help Ridders, IT WORKS )

Now i need to find out how to refresh the subform so the listbox with cities updates each time a new selection is made in listA (countries). Anyone have any ideas?

VBA, CBA, VCA...it gets better!

Use Me.Requery or possibly Me.Subform.Requery in the AfterUpdate event for listA to refresh the display.
You would need the name of the subform control if doing the latter

 

[Pat Hartman]

Are you by any chance using table level lookups in either table. These will frequently confuse the issue in a query and so experts just don't use them. They are a crutch for people who don't know how to create a query with a join. Once you move to creating your own queries or writing VBA, they trip you up in subtle ways. If you are using them, remove them. You will probably need to change some queries to add the lookup table with a left join to the lookup table so you can pick up the text value and display it on your reports. Forms will generally use combos or listboxes so the RowSource takes care of them anyway.

There is NO problem with using listboxes or combos as criteria assuming you construct them as Access expects. Although Access allows you to define any column as the bound column, when referencing the control from a query, you will run into a problem whenever the bound colum is not the first column so you may need to change the RowSource query, the bound column property and the column widths property to hide the first column which will now be the PK which should be hidden.

 

[mort]

I would like to thank everyone for taking their time to help me. Everything is working perfectly! Only problem is that now i get a enter parameter error message when loading the form in my navigation form. But I will try to make my own navigation form instead.

I did a lot of googling before asking on this forum, and I know many other struggle with the same problem I did. So I will post a step by step solution. Hopefully this will other people on this forum.

In the explanation I will use a Country - City table. This is just so that explaining is easier.

My problem was this: I have a tblCountries (parent) and a tblCities (child). The data entry is done via frmCountries (main form) and frmCities (sub form). It is single form with data entry set to NO (so that people can go back and edit earlier registrations).

I had a listbox on the main form called listCountries and a listbox on the subform called listCities. There was 10 different countries, each with 5 cities, total 50 cities spread over 10 countries.

listCountries contained all 10 countries and listCities contained all 50 cities. I wanted listCities to filter based on what country the user had selected in listCountries.

SOLUTION

1. Use tblCountries and tblCities to create a query. Populate the query with the following fields from tblCities: CityID, CountryID, CityName (and whatever more fields you wish).

2. Create a unbound textbox in frmCountries (main form). Give the textbox a name. For future reference I called mine txtListValue. In the control source of this textbox set the following: =[listCountries].[column](0). Now every time you make a selection in listCountries the ID will show in txtListValue.

3. Give your subform a control name from the main form. I used SubCities.

4. In the listbox on the subform, listCities, set the RowSourceType to the query you made in step 1. Then click the ... button on rowsource.In the query builder set the following expression on the countryID: [Forms]![frmCountries].[txtListValue].

5. Now go to listCountries. If there allready is a macro in the after update event you should delete that, and instead write code. Write the following code:

Private Sub listCountries_AfterUpdate()

DoCmd.SearchForRecord , "", acFirst, "[CountryID] = " & Str(Nz(Screen.ActiveControl, 0))

Me.SubCities!listCities.Requery

End Sub

Works like a charm

 

[Pat Hartman]

As long as you are happy but one of the cascading combo solutions would probably have been easier.