Displaying car models depending on make

[hobman]

I am trying to create a form that a user can enter car information. instead of typing the car info such as make and model, I wanted a drop down that they can select from. I have a car make/model table that is like:

Make Model1 Model2 Model3 Model4 Model5
Audi 82 81 XH DD Quatro
Honda Accord Civic Oddesy
Toyota orola Camery 4Runner

I also have car info table which has the make and model and other details. I when a user enters a new car, I want them to select it from drop down menu that will be populated from the make/model table.

My problem is, I don't know what kink of simple code that will allow on the form when a make is selected, the model combo box gets populated with the models related to the selected make. Please help

hobman

 

[dhookom]

Having fields like Model1...5 is not a good structure. Each model should create its own record ie:
Make Model
Audi 82
Audi 81
Audi XH
etc.

If you normalize you tables, your question will be easier to solve. Is the table change possible?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[TheAceMan1]

How are ya hobman . . . . .

At your leisure, have a look at the following:

Normalizing Tables

Table Relationships

Fundamentals of Relational Database Design

See Ya! . . . . . .

 

[deekydoo]

Hi,

You need to look at the real life entities and design your database and form(s) accordingly. See car makers and then see that there are many models associated with each maker.

So make a table for Car makers: Audi, Ford, Honda etc. Make a table for the individual models and link the makers to the models in a one-many relationship. Then you populate one combo box fom the manufacturers and write vb to populate the model combo box once the manufacturer has been selected. I do this sort of thing all the time to build search forms.

Hope this helps,

Deek

 

[willir]

Well here is a head start...

tblManufacture
ManuShortName - primary key, string
ManuName - full name of manufacture, string

tblModel
ModelID - autonumber
ManuShortName - foreign key to tblManufacture
ModelYear - text, string, 4
ModelName - text

Use the Relationship tool to establish a relationship between the Manufacture and Model tables. From the menu, "Tools" -> "Relationships". Add above tables. Select the field ManuShortName in the tblModel and drag it to the same field in tblManufacture. You will be prompted for referential integrity. Alway enforce referenctial integrity.

Now create a form for the tblModel. From the database window, select "Forms" and then use the wizard to create a form. Edit the design. Right click on the control field for ManuShortName. From the list of options, select "Change to", and then select "Combo box".

Make sure the properties window is open. (From the menu, select "View" -> "Properties"). Make sure the ManuShortName combo box is selected.

In the properties window, select the data tab. Then select the Row Source field. From the drop down list, select the manufacturing table, tblManufacture, and click on the "..." command button that appears to the right. This opens up the Query builder. Double click on the ManuShortName field to add the field to the query. You may wish to add "Ascedning" for the sort option. Close the query builder and save.

Repeat the above for the Model field. This time, instead of a combo box, choose ListBox, when the query builder pops up, add the field Model.

Reselect the ManuShortName combo box. In the properties window, select "Events". You probably don't want to sue the more commonly used event for this process, AfterUpdate event. Instead, for the walk-through, let's use the DoubleClick event. Select the DoubleClick event, and start typing [Event Procedure] - the system will automatically fill this out for you. Now select the "..." elipse command button that appears to the right. This will open up the VBA programming window.

So what we want to do is when a user double clicks on the Manufacturer combo box, only models for the selected manufacturer will appear the Model list box.

Add the following code...

Dim strQ as String, strManu as String, strSQL as String

strQ = Chr$(34)
strSQL = "SELECT Model From Model Where ManuShortName = "

strManu = Nz(Me.Manufacture, "")

If Len(strManu) Then
   Me.Model.RowSource = strSQL & strQ & strManu & strQ
   Me.Model.Requery
End If

Assuming you have data in the Manufacturing and Model tables, what should happen after you select a Manufactuer in the combo box and double click the combo box, the list box for models will display the models for the manufacturer.

As you can see, the code is pretty simple. Most of the above was setting things up so "we" were on the same page.

Richard