Filter data on a form from another control on the same form

[WonkeyDonkey]

Not sure its the exact description I want but here goes.



I have 2 tables setup like so:

Table 1 (Locations):

Location_ID (Primary Key)
Location_Name
Location_Addr_1
Location_Addr_2
etc etc etc

Basically an address file with a location ID.

Table 2 (Resources):

Location_ID (Primary Key)
Resource_ID
Resource_Description

Essentially a list of resources with the location ID for each one.

Now, I have my main form displaying primarily the information from table one.

What I now need to do is add a listbox displaying the resource information from Table 2; this needs to be filtered so that it only shows the resource information relevant to the particular location being displayed.

Ive tried adding a listbox, and setting its Control Source to Location_ID, but it still shows ALL of the resources from the entire Table 2, instead of just for the location that is being displayed.

A sub-form does work in a fashion, but I have the feeling a sub-form is a little overkill for my needs. I dont need to edit any of the data, just browse it.

I did try to setup a query, but I cant find a way to reference Locations.Location_ID from the main form as a criteria for the query.

Can anybody help please ?

:?:

[lfpm062010]

One way to do it is change the "Row Source" of listbox to filter the record based on the "Location_ID" from the main form (table Location).

The "Row Source" would look something like this.

Row Source = SELECT Location_ID, Resource_ID, Resource_Description FROM Resources WHERE Location_ID = Forms![MainFormName]![Location_IDNameFromMainForm]

[Dal Jeanis]

lfpm got it right. Also, you should be able to refer to the location ID's control on the form directly as Me.Controlname, so the rowsource could look something like this:

"SELECT [Resource_ID], [Resource_Description] FROM [MyTable2] WHERE [Location_ID] =" & Me.MyLocationIDControlName