listbox many to many which shows information based on ID

[littlemissdt]

Hi,
I'm new to the forum.

I have two tables, items and regions. the relationship is many to many. in my form, i have a listbox which shows which regions are connected to each item. my listbox currently displays all the regions available and not just the regions connected to each item. can you help me filter the listbox according to regions per item. the item id is stored in a textbox on the form.

thanks,

Deborah

 

[kasmax]

Hi, you can set the source of the region listbox to the query filtering based on what you select in Item listbox. for example on the click even of Item Listbox set the source of Region listbox to filtering query.

if you need help with how to do filtering query then can you tell me what fields you have in both tables.

 

[liddlem]

Hi. From reading your mail, I think that you may need to normalize your data. And create a third table.

By "normalize", I mean that the ITEMs table has only ONE record for Each item and likewise, the REGION table has only one record for each region.

Now the third table should contain at least two fields. Namely ITEM and REGION.
These fields should be combo boxes (drop down) list that get their data from each of the respective tables. Let's call this table TBlItemRegion.

Then you can start working towards making your form. . . . But first, you should make a query. The query should include all files in the TblItemRegion. (we will come back to this query in a minute)

Now create a form that gets its data from the query.
On the form, add a combo box. choose "search for data that is in another table or query".
Get the data from the REGION table. I will assume that this combo box is called "combo1"

Now go back to the query and open it in design mode.
Under the REGION field, set the criteria to forms!formname.combo1
So this tells the query to see what region you have selected in the combo1 box ans select all records the belong to that region.

Now you one thing left to do. On the form, edit the "after update" event. It must requery your query. You can use the macro wizard or you cane write the code yourself. Your choice.

 

[littlemissdt]

Hi,

thanks for your help. i already created the tblItemREgion. and i created a query

SELECT tblREgionItem.itemID, tblRegion.regionName
FROM (tblRegionItem INNER JOIN tblRegion ON tblRegionItem.regionID = tblRegion.regionID) INNER JOIN tblItem ON tblRegionItem.itemID = tblItem.itemID
WHERE (((tblRegionItem.itemID)=[Forms]![frmItems1]![itemIDValue]))
ORDER BY tblRegionItem.itemID;

the itemidValue is a textbox. i tried setting the critera but it doesn't work. i don't need two listboxes. i have a form which gets many items and gives a variety of data about the items - title, date etc. the items also have a region. which is stored in a many to many connection table.

if i run the query in sql view and change the forms!frmItems...to a value say 2, the query results are correct. i just can't get it to show the correct results in the listbox.