list lookup query

[gully300]

i'm trying to setup a conditional lookup in a query, i have table

Catagories(PCVCatagory,CatNo,Options)

and query

[Classifier Synnex](1,2,PCVCatagory,Cat1,Cat2,.....,item)

what i'm trying to achieve is lookup tables for Cat1-Cat5, the lookup lists coming from

Catagories.Options

when

Catagories.PCVCatagory=[Classifier Synnex].PCVCatagory

and

Catagories.CatNo=[Classifier Synnex].(1 for Cat1, 2 for Cat2......)

what i have so far

SELECT Catagories.Options
FROM Catagories
WHERE Catagories.CatNo="1" And Catagories.PCVCatagory=[Classifier Synnex]![PCVCatagory];

whats not happeneing is [Classifier Synnex]![PCVCatagory] isn't getting the value of Synnex.PCVCatagory for the current row i'm workin on, i'v been running in circles and no matter what i google or how i phrase it wont work.

any help greatly appreciated

[June7]

Include a JOIN clause of the table Catagories and query Classifier Synnex

INNER JOIN Catagories.PCVCatagory=[Classifier Synnex]![PCVCatagory]

I am guessing INNER will work to properly relate these objects. No need for this expression in the WHERE.

BTW, correct spelling is Category

[gully300]

I'm not quite sure where the INNER is ment to go but i dont think thats quite what i need either, the expression works fine if i specify the variable manually, like cpu...

SELECT Catagories.Options
FROM Catagories
WHERE Catagories.CatNo="1" And Catagories.PCVCatagory="CPU";

it just isn't pulling it out of the query, also a bit more info on the table its layed out

CPU,1,1156,##
CPU,1,1366,##
CPU,1,1155,##
CPU,2,2.6G,##
CPU,2,2.8G,##
CPU,2,3.0G,##
## are the autonumber and "primary key"

[June7]

That works because you offer the query a literal value as the criteria.

When you have =[Classifier Synnex]![PCVCatagory] the criteria is a field of table, not a specific value.

After looking at your query again, I can now see that a join probably won't accomplish anything. The PCVCatagory value must be a parameter input, by reference to a field in current record or prompt to user.

[gully300]

ok so how do i reference a field in the current record, because thats one of the things i was and still am chasing in circles

[June7]

I don't normally setup lookups in tables or queries, only in combo and list boxes on forms and that is how I was approaching this. I finally realized you must be trying to do this directly in query so I gave it a try and it doesn't work. I set up a form bound to Classifier Synnex, created combobox bound to PCVCatagory and named it cbxPCVCatagory, created combobox bound to Cat1 and RowSource of:
SELECT Option FROM Catagories WHERE CatNo="1" AND PCVCatagory=[cbxPCVCatagory];
That works.

Will need code (I use VBA only) to requery Cat1 combobox (and the other comboboxes dependent on cbxPCVCatagory) after selection of value in PCVCatagory combobox.

[ssanfu]

You could try:

SELECT Catagories.Options
FROM Catagories 
WHERE Catagories.CatNo="1" And Catagories.PCVCatagory = FORMS![Classifier Synnex].[PCVCatagory];

(Note the period)

(or maybe FORMS![Classifier Synnex]![PCVCatagory])

If you were trying to create a query in the query design dialog, you would need to fully qualify the reference to the control "[PCVCatagory]".

[gully300]

thanks for the help i have done it through a form as sudjested and it works great.