Filtering on a field in a table that the form is not based on

[bvdahl]

Hello,

I have a form based on 'tableA'. I also have a 'tableB' that is related to
'tableA' via an ID field. What is the best way of filtering the form on a
value in 'tableB' ?

Baard

 

[Keith Wilby]

Hello,

I have a form based on 'tableA'. I also have a 'tableB' that is related to
'tableA' via an ID field. What is the best way of filtering the form on a
value in 'tableB' ?

Baard

You could have a combo box with a row source from 'tableB' and use that to
filter on your field in 'tableA'. The combo box wizard will do the hard
work for you.

HTH - Keith.
www.keithwilby.com

 

[bvdahl]

Sorry, but I am going to need a bit more than that. I can't see how the
wizard helps me with that. If I choose the option that says 'Find a
record.......', it forces me to use values from 'tableA'. I can't see how the
other options would help me either, but maybe I have overlooked something.

I have a field in 'TableB' called town. I would like the rowsource in the
combo box to be "select distinct tableB.town from tableB;" Then I would like
to find all the record's in tableA based on the town I select in the combo
box.

Baard

 

[Stefan Hoffmann]

hi Baard,

I have a form based on 'tableA'. I also have a 'tableB' that is related to
'tableA' via an ID field. What is the best way of filtering the form on a
value in 'tableB' ?

Change the data source from your form. Use both tables, e.g.

SELECT tableA.*, tableB.FieldsToFilter
FROM tableA
INNER JOIN tableB
ON tableA.ForeignKey = tableB.PrimaryKey

You can then simply use the Filter and FilterOn properties of your form.


mfG
--> stefan <--

 

[bvdahl]

brilliant, that was what I was missing.

I am relatively new to access, and I didn't think you could combine two or
more tables in the record source

Baard