Field determining other fields

[tomiko61]

I have a table wiht field Brand & Model in a table for cars. I have another table with values of Brand/Models records. I want to choose a brand and then the models for that brands in the car table unsing the values of the other table(Combo box). I tried some queries and it doesn't work

 

[WildHare]

The "MODELS" combo box that you probably envision should use a query that pulls MODELS from your Brand/Model table with a BRAND criteria.
Example:

Select [Model] from [BrandModels] where [BrandModels]![Brand] = "Chevrolet"

would return "Chevettte", "Camaro", "Cavalier", "Caprice", "Corvette", etc etc etc.

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[james33]

Dear Jim,

What would be the Syntax if you had a combobox that had the brand selection in it that you chose from?
i.e. After
where [BrandModels]![Brand] = ?

Thanks in advance
Regards Jim

 

[WildHare]

Jim - Not sure I understand your question.

Brands
------
FORD
CHEVROLET
CHRYSLER
PEUGEOT
VAUXHALL
YUGO
CITROEN
MORRIS-MINOR


BRAND/MODELS
---------------------
CHEVROLET / CORVETTE
CHEVROLET / CAMARO
CHEVROLET / CHEVETTE
FORD / EXPLORER
FORD / PINTO
FORD / EDSEL
....

Combo box that returned the BRAND alone would then be the criteria for the select statement that pulls from the Brand/Models table:

Select MODEL from BRAND/MODELS where BRAND/MODELS.BRAND = ' " & myCombo1 & "'"

Is this what you're asking?
Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[tomiko61]

I have two tables
CAR (ID,TAG,COLOR,BRAND,MODEL,ect...)
BRAND(BRAND,MODEL)

I'm getting the values of brand in CAR from Brand field in BRAND(Combo box) that's working

but

If I choose a FORD I only wnat to see the models for a FORD in the combo for Models in CARS table

the way BRAND looks is:

FORD MUSTANG
FORD TEMPO
FORD EXPEDITION
MAZDA RX-7
MAZDA MX-6
ect...

 

[WildHare]

So your [CAR]![MODEL] combo wants to pull from BRAND table where [BRAND]![BRAND] = [CAR]![BRAND]

Right? Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[james33]

Yes Jim that was kind of what I was asking for, but I didn't phrase my question very well.

The arrangement that I was looking for was one where you have 2 comboboxes and when you have chosen an entry from the first you are only shown the relevant entries in the second.

Thank you.

Regards Jim

 

[WildHare]

James33 - the query that populates the 2nd combo box should have as a criteria the value from the first combo box. In the query grid for the 2nd combo's query, for the field that is being picked in Combo1, refer to the first combo thusly: (assuming they are both on a form called "frmPickStuff":

=Forms!frmPickStuff!Combo1

The AFTER UPDATE event of Combo1 should issue a COMBO2.REQUERY method, and then bounce to combo2:

Sub Combo1_After_Update(Cancel) as integer
With Me
.combo2.requery
.combo2.setfocus
.combo2.dropdown
End With
End Sub

I have placed a sample A97 database on my site to illustrate this.

http://www.jmhare.com/databases/twoCombos.zip

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com