Criteria for a lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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 all - works now.

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

Cheers,

Robbie


Andy Hull said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top