Criteria for a lookup

[Robbie G]

I am trying to create a database with product categories and sub categories
and want to use lookups to populate these 2 columns. Product categories maybe
a Brand (e.g. Ford, Nissan, Toyota) and then the sub categories the
particular model.

I have created a table that links brands and models.
I've got a lookup for the Brand and i can create a lookup for the model but
this lists all models for every brand (so you could select a Ford Corolla for
example).

I want to limit the options in the lookup dropdown list so that once the
brand is selected only the models for that brand are shown in the drop down
list. I have tried dlookup in a form without success and i have tried using
SELECT ... FROM... WHERE... but this asks for a manual paramter entry when i
open the table rather than doing this on a record by record basis.

Any suggestions welcomed.

Thanks

 

[Dale Fye]

Robbie,

The way to do this in a form is to have two combo boxes. The first combo
box (I'll call it cbo_Brand) should have a query that looks something like
(assumes you have one table that contains the BrandID, BrandName, ModelID,
and ModelName:

SELECT BrandID, BrandName
FROM yourTable
GROUP By BrandID
ORDER BY BrandName

The Second query for the second combo box (cbo_Model) should look something
like:

SELECT ModelID, ModelName
FROM yourTable
WHERE BrandID = me.cbo_Brand
ORDER BY ModelName

Set the default value of both of these controls to NULL.

Then, in the AfterUpdate event of cbo_Brand, add a line of code to requery
the model combo box. It would look like:

Private sub cbo_Brand_AfterUpdate

me.cbo_Model.requery

end sub

HTH
Dale

 

[Robbie G]

Thanks for this Dale.

However, after i have entered the brand and click the model dropdown arrow i
get a message box "Enter Parameter Value". the parameter it is after is
me.cbo_brand. If i enter the brand ID number the dropdown list is populated
correctly. Is there something not right with the requery?

thanks,
Robbie

 

[Andy Hull]

Hi Robbie

The Second query for the second combo box (cbo_Model) needs a slight change.

me.cbo_Brand needs to be something like...

Forms![MyFormName]![cbo_brand]

So the second query for the second combo box will be...

SELECT ModelID, ModelName
FROM yourTable
WHERE BrandID = Forms![MyFormName]![cbo_brand]
ORDER BY ModelName


(Obviously, replace MyFormName with the actual name of your form)

hth

Andy Hull

 

[Robbie G]

Thanks all - works now.

also found this: http://office.microsoft.com/en-gb/access/HA011730581033.aspx

Cheers,

Robbie

Hi Robbie

The Second query for the second combo box (cbo_Model) needs a slight change.

me.cbo_Brand needs to be something like...

Forms![MyFormName]![cbo_brand]

So the second query for the second combo box will be...

SELECT ModelID, ModelName
FROM yourTable
WHERE BrandID = Forms![MyFormName]![cbo_brand]
ORDER BY ModelName


(Obviously, replace MyFormName with the actual name of your form)

hth

Andy Hull

Thanks for this Dale.

However, after i have entered the brand and click the model dropdown arrow i
get a message box "Enter Parameter Value". the parameter it is after is
me.cbo_brand. If i enter the brand ID number the dropdown list is populated
correctly. Is there something not right with the requery?

thanks,
Robbie

 

[Dale Fye]

Good catch Pieter