Microsoft Access form combobox limiting other fields?

I'm trying to create an order form. Right now I have a series of combo boxes with a car make, model, year, part, part name, price. I want to be able to make it so when you select a make, only models with this make are available in the the model combobox. Then when a model is selected only available years and lastly, only show available parts once all make/model/year is selected. Then the part description and price should update to match the part selected. I haven't even gotten past the first make to model part yet. I have a table with a bunch of different records each with a make model and year. Some of the makes are the same. The combobox is showing numerous values of each make, one for each record.

How do I get it to only show once for each make and then you pick a model and year the same way?

my table is set up like this

modelnum ---------make---------model---------------year

-------------------------------------------------------------------------------------------------------------------------------------

1 -----------------------a---------------abc---------------1999

2-----------------------a---------------abc ---------------2003

3-----------------------a---------------xyz---------------1985

4 -----------------------a---------------ghi ---------------2005

5 -----------------------b--------------- jkl ---------------1999

6 -----------------------b---------------asdf ---------------2004

7-----------------------b --------------- asdf--------------- 2008

I want it to only show each make (a,b) once in the combo box, then show each model once in it's combo only if it has the same make as the chosen make from the make combo box and then the same for between year and model. Any help would be greatly appreciated. Thanks in advance.

2 Answers

Relevance
  • 8 years ago
    Favourite answer

    The best way to go about this is to normalize your data, then use cascading combo boxes. Normalizing your data means eliminating duplication by using multiple related tables. For instance

    Table: Makes

    MakeID

    Make

    Table: Models

    ModelID

    MakeID

    Model

    Table: Years

    YearID

    ModelID

    Year

    Please note that Models are related to Makes through the foreign key "MakeID" in the Models table. Also, Years are related to Models through the foreign key "ModelID" on the Years table.

    Normalizing has eliminated duplication. There is now only one record for each Make. There is one record for each Model of a given Make. Same for Years.

    You can bring all this normalized data together again using a join query.

    You can create a combo box on your form for Makes based on the Makes table, this should be pretty straightforward. You may want to call it "cboMakes".

    You can create a related (cascading) combo box for Models by specifying a recordsource of "Select ModelID, Model from Models where MakeID = Forms!MyFormName!MakeID;", where "MyFormName" is the name of your form.

    The last trick is to requery the related combo box control after the cboMakes is updated, and when you navigate to a different form record. To handle this, put cboMakes.requery (or cboMakes.refresh, I forget which) in the cboMakes AfterUpdate event procedure, and in the form OnCurrent event procedure.

    Use this same procedure to handle the other cascading combo boxes for Years, Price or whatever.

    Hope this helps,

    Peter De Baets

    http://www.peterssoftware.com/

    Source(s): Self. 30 years of experience designing database applications
  • pina
    Lv 4
    4 years ago

    In get right of entry to, there's a pattern database referred to as Northwind investors (or the like). Pull it up & try how the varieties artwork. you will see there would properly be numerous tables used via a sort. you're able to have a table of shoppers related to a table containing their orders, for occasion. The "link" is the shopper call or type that seems in the two tables. you go with a typical field with a view to link the tables mutually. in case you place up your tables good, (and if I understand the question good) you're able to have the skill to do what you describe. the type itself, is secondary to installation the tables and the links between tables. wish this facilitates!

Still have questions? Get answers by asking now.