Results 1 to 11 of 11

Thread: Creating a Form

  1. #1
    Join Date
    Jul 2012
    Posts
    13

    Unanswered: Creating a Form

    Hey everyone,

    I am somewhat new to Access and have been given the challenge of creating a database. I have it all completed except for one specific entity. I work in the automotive sector. I need a form that has Make, Model, Year etc.

    I need it to work so that when I choose, let's say Ford, a manufacturer, that the next box only gives me Ford models, and then from the model it gives me the year.

    I have a spreadsheet with ALL the makes and models from the 40's thru 2013, Ijust don't know how to make this form.

    HEEEEEELP!

    Thank you

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The concept is called cascading combos:

    Cascading Combos
    Paul

  3. #3
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Just out of curiosity, is there a reason why you are going Make, Model, Year instead of Year, Make, Model?

    Does your spreadsheet have ID's set for the Makes & Models or just the names?

    So, in it's simplest view your table contains Year, Make, Model (or Make, Model, Year! The order won't matter here).

    You fill the first combo with a query like:

    Code:
    select distinct Make from tblVehicle order by Make;
    then a selection there will trigger the setting of the Model combo with a query like:

    Code:
    select distinct Model from tblVehicle 
    where Make = 'Ford' order by Model
    and finally, a selection there will set the Year combo to something like:

    Code:
    select distinct Year from tblVehicle 
    where Make = 'Ford' and Model = 'F150' order by Year
    A much better design is to split Makes and Models out to separate tables to avoid data duplication. You can connect Models to a Make in the Models table. The concept for the combo boxes is the same as before but you just join the tables together.

    For instance, the Model query might look something like:

    Code:
    select ModelName from Model 
    Join Make on Model.MakeID = Make.MakeID 
    where Make.MakeName = 'Ford'
    order by ModelName;
    You don't need the distinct on this one since the Model table already handles this detail. Of course, if you are using ID's and you have this from the Make combo, you can just simply query the Model table:

    Code:
    select ModelName from Model
    where MakeID = 54 
    order by ModelName;

    Steve

  4. #4
    Join Date
    Jul 2012
    Posts
    13
    Steve, Paul,

    Thank you for the response, now that I know the correct terminology I can do a bit more research. Is this a task that would take a person who knows what they are doing very long?
    I use AAIA data and that is how they display the data, MMY. It seems to narrow the vehicles down a bit better.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by aftermarket_auto View Post
    Is this a task that would take a person who knows what they are doing very long?
    Nah; I'd be done already.
    Paul

  6. #6
    Join Date
    Jul 2012
    Posts
    13
    haha Paul. Was that an invitation? I've been stuck on this last portion of this database for about 2 days now lol.

    Again, thanks for the info! I think I can have it done in about a week if I'm lucky

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, it was an answer to your question. Certainly if you have trouble integrating one of the methods, post back and we'll figure out what's going wrong.
    Paul

  8. #8
    Join Date
    Jul 2012
    Posts
    13
    Paul,

    This is where I am at; I have created a spreadsheet with Make and Model only. I am trying to keep it simple for my sanity Under Make I have the 41 makes that we use, and under model I have all of the models across from the corresponding make.

    Here is the code I have:

    Option Compare Database

    Private Sub cboboxMake_AfterUpdate()
    On Error Resume Next
    cboboxModel.RowSource = "Select Make-Model.model " & _
    "FROM tblMake-Model " & _
    "WHERE tblMake-Model.Make = '" & cboboxModel.Value & "' " & _
    "ORDER BY Make-ModelAll.Model;"
    End Sub


    When I go back to the form, I click on the combo box but no data shows up...I'm feeling very stupid right now so hopefully you can help me out

    Thank you in advance
    Attached Thumbnails Attached Thumbnails DB_example.JPG  

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You're telling the model combo to refer to itself for the make. I would expect you to be referring to the Make combo.
    Paul

  10. #10
    Join Date
    Jul 2012
    Posts
    13
    Well I started over in the simplest form. Just lloking to do make and model...year is a 4 digit number that we can just enter.

    SELECT DISTINCT Make

    FROM MakeModeltable by Make




    Private Sub cboMake_AfterUpdate()


    On Error Resume Next

    cboModel.RowSource = "Select distinct Model " & _
    "FROM MakeModelTable " & _
    "WHERE Make = '" & cboMake & "' " & _
    "ORDER BY Model"

    End Sub


    and now I am getting an error.
    Attached Thumbnails Attached Thumbnails db-forum.JPG  

  11. #11
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by aftermarket_auto View Post
    SELECT DISTINCT Make

    FROM MakeModeltable by Make
    You can't have this part just sitting out there in the code. You need to have it inside a method and set to something. Is this supposed to be the row source for the Make combo?

    Use one of your form startup events, like On Open, and inside that sub put something like this:

    Code:
    cboMake.RowSource = "Select distinct Make " & _
    "FROM MakeModelTable " & _
    "ORDER BY Make"
    This will populate the Make combo which you can then use as your source for the Models.

    Steve

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •