Filtering a form by a different table

[Guest]

G'mornin' all!

I've been trying to do this for a couple of days and I've had enough of
struggling so I'm hoping one of you has tried this before!


I've got a form with contact details of companies on it. It's based on a
table called 'MainData'.
I've also got a table with a list of product types, it's called
'ProductDimension'.

I want people to be able to filter the companies by the product type. I've
made a form that just has a drop down with product types, press a button and
the contact details form shoud be opened with a filter applied.
It never works! I always get an access prompt asking me for the product
type, but even if I enter it there it doesn't work.

Can anyone please help me!

Thanks very much in advance..
Pete

 

[Jeff Boyce]

Foss

Any chance your underlying data/tables use the "lookup" data type field?
This causes considerable confusion, as it stores something different than it
displays...

You told us about two tables, but didn't "connect the dots". How does
Access know which Companies are connected to which Products?

 

[Guest]

Jeff,

Thanks for the speedy response!
Sorry about the lack of info, I'm never sure what people need to know! :-S

There's another table that I didn't mention, it's called ProductList and
this contains the information connecting the other two tables. Here's how:
- MainData connects to ProductList using an autonumber field.
- ProductList connects to ProductDimension using the product code which is
an integer.

I believe that some fields in both MainData and ProductDimension have
lookups associated with them. By that I mean that to enter data into the
tables you can just use a drop down or you can type the value in.

Does that explain it any better?

Cheers,
Pete

Foss

Any chance your underlying data/tables use the "lookup" data type field?
This causes considerable confusion, as it stores something different than it
displays...

You told us about two tables, but didn't "connect the dots". How does
Access know which Companies are connected to which Products?

 

[Jeff Boyce]

Pete

Thanks for greater detail -- it's tough offering specific advise on general
questions!

First thing to consider is to work in forms, not directly in the tables
(tables store data, forms display it).

Take a quick scan through the tablesdbdesign newsgroup on the topic of
lookup fields. You'll find a STRONG consensus against using them in table
definitions, for the reason in my previous post.

I assume your Main and ProductList tables connect on a field that is a
LongInt. In Main, it is an Autonumber, right? But in ProductList, it is a
LongInt, right??

To do the filtering your original post suggesting, use a form. Put a combo
box in the form that lists Product types. In the AfterUpdate event of that
combo box, requery a listbox. The listbox uses a query that lists
Companies, but uses, as a criterion, a "product" ... which you get from
referring to the form! Use something like (your syntax may vary):

Forms!frmYourForm!cboYourComboBox

as the criterion for the query that underlies a listbox on frmYourForm.

 

[Guest]

Jeff,

Too true! Thanks for the assistance, it's much appreciated!

Right, I've taken off all the lookup things from any tables. They only
happen in forms now.

All the fields that are used in joins are longints, except the one
autonumber of course!

I'm not sure if your suggestion will fit what I'm trying to do. Although to
be honest, that may have a lot to do with the fact that I'm getting a little
over my head!!

I've got a form called ChooseType. All it's got on it is a drop down with
all the products, and a button.
What I'm trying to work towards, is that when that button is pressed, the
existing contact details form will open. The only difference should be that
only the companies dealing with the product are displayed.

So I suppose, come to think of it, I need a WHERE statement, that I can put
on the form. I've just given that a try but I can't get it right at all.
This is what I used as the form filter: KompassDimension.[ProductType]="Fruit"

Any idea why this doesn't work?

Cheers,
Pete

 

[Jeff Boyce]

Pete

Not sure I quite understand yet -- let me try this...

If a user selects a "Produce", you want them to see all Companies offering
that product.

If so, one way to do that is to put an unbound combo box in the header of
the form. This combo box only shows products. The form itself shows
Companies, but only shows Companies for the selected product. You do this
by building a query that uses a parameter for the Product, and in the
criterion, you "point" to the form's combo box to get the Product. The
query criterion looks something like:

Forms!frmYourForm!cboYourComboBox

Hope that helps!

Jeff Boyce
<Access MVP>

Jeff,

Too true! Thanks for the assistance, it's much appreciated!

Right, I've taken off all the lookup things from any tables. They only
happen in forms now.

All the fields that are used in joins are longints, except the one
autonumber of course!

I'm not sure if your suggestion will fit what I'm trying to do. Although to
be honest, that may have a lot to do with the fact that I'm getting a little
over my head!!

I've got a form called ChooseType. All it's got on it is a drop down with
all the products, and a button.
What I'm trying to work towards, is that when that button is pressed, the
existing contact details form will open. The only difference should be that
only the companies dealing with the product are displayed.

So I suppose, come to think of it, I need a WHERE statement, that I can put
on the form. I've just given that a try but I can't get it right at all.
This is what I used as the form filter: KompassDimension.[ProductType]="Fruit"

Any idea why this doesn't work?

Cheers,
Pete

Pete

Thanks for greater detail -- it's tough offering specific advise on general
questions!

First thing to consider is to work in forms, not directly in the tables
(tables store data, forms display it).

Take a quick scan through the tablesdbdesign newsgroup on the topic of
lookup fields. You'll find a STRONG consensus against using them in table
definitions, for the reason in my previous post.

I assume your Main and ProductList tables connect on a field that is a
LongInt. In Main, it is an Autonumber, right? But in ProductList, it is a
LongInt, right??

To do the filtering your original post suggesting, use a form. Put a combo
box in the form that lists Product types. In the AfterUpdate event of that
combo box, requery a listbox. The listbox uses a query that lists
Companies, but uses, as a criterion, a "product" ... which you get from
referring to the form! Use something like (your syntax may vary):

Forms!frmYourForm!cboYourComboBox

as the criterion for the query that underlies a listbox on frmYourForm.

--
Good luck

Jeff Boyce
<Access MVP>

which
is