Results 1 to 10 of 10

Thread: Combobox help

  1. #1
    Join Date
    Jun 2013
    Posts
    10

    Unanswered: Combobox help

    i have been working to set up a database in access 2007 x86 and i have been trying to get the combo boxes to work without much luck.

    Watched a few videos and links but it doesn't seem to be working for me, just gets me more confused.

    Any "quick" set up guides available, as I have not had any luck getting this to work.

    Any help/suggestions appreciated

    thanks in advance

  2. #2
    Join Date
    Mar 2015
    Posts
    27
    Quote Originally Posted by fastforded View Post
    i have been working to set up a database in access 2007 x86 and i have been trying to get the combo boxes to work without much luck.

    Watched a few videos and links but it doesn't seem to be working for me, just gets me more confused.

    Any "quick" set up guides available, as I have not had any luck getting this to work.

    Any help/suggestions appreciated

    thanks in advance
    You are going to have to provide a whole lot more information on what you want to do, like are you wanting to just load them with a specific, unchanging list of values? Or do you want them loaded with data from a table? Just what do you mean when you say they are not working. Is the problem in creating them and loading them with your data or do you not know how to get the users selections back to use them?

  3. #3
    Join Date
    Jun 2013
    Posts
    10
    Quote Originally Posted by rodrich1954 View Post
    You are going to have to provide a whole lot more information on what you want to do, like are you wanting to just load them with a specific, unchanging list of values? Or do you want them loaded with data from a table? Just what do you mean when you say they are not working. Is the problem in creating them and loading them with your data or do you not know how to get the users selections back to use them?
    thanks for getting back...i am trying to create a form where used cars can be searched.

    main database will have all the cars listed

    i made fields of the make, model, and sub model. These fields will need to be updated every time the search is run, as we will be adding/removing models as the years go by. I would also later like to have this access form available online, so clients/employees can search from a mobile device, but thats down the road.

    I am having issues getting the "model" to link to the "make"

    i made the year independent, but want to limit the choices of "models" based on what the user defines in the "make" or "mfgr" field in the drop down box.

    My "mfgr" drop down does show the models i listed in that table.

    But i am not sure how to get the "model" choices to be based on what the user selects in the "mfgr" field. Now when i select a "mfgr" the "model" list is empty.

    i was trying to assign a "mfgrid" number to each manufacturer, and use that "mfgrid" number to determine why choices become available in the next combo box called "model"

    It should allow them to now pick models based on the mfgr, kind of like the "city/state/country" combo boxes I have seen in these youtube videos...

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so youwill probably want to usew linked / cascading dombo or list boxes. probably list boxes would be my guess
    list box 1 select manufacturer
    once thats selected then populate listbox2
    list box 2 select model
    once thats selected then popuilate listbox 3
    list box 3 select buildstandard (eg No Doors, trim etc)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2015
    Posts
    27
    Ok, try this. Replace my example combobox, table and field names with your real ones and you should get the results you are looking for. Basically, the whole deal should look a sort of like this with this code being in the code module for the form the comboboxes are on.
    Code:
    Private Sub Form_Load()
        Me.cboManfufacturer.RowSource = "SELECT MfgrID, Make FROM tblManufacturers ORDER BY Make ASC;"
    End Sub
    
    Private Sub cboManfufacturer_AfterUpdate()
        Me.cboModel.RowSource = "SELECT ModelID, Model FROM tblModels WHERE MfgrID = " & cboManufacturer.Value & " ORDER BY Model ASC;"
        Me.cboModel.Requery
    End Sub
    
    Private Sub cboModel_AfterUpdate()
        Me.cboSubModel.RowSource = "SELECT SubModelID, SubModel FROM tblSubModels WHERE ModelID = " & cboModel.Value & " ORDER BY SubModel ASC;"
        Me.cboSubModel.Requery
    End Sub

  6. #6
    Join Date
    Jun 2013
    Posts
    10
    Quote Originally Posted by rodrich1954 View Post
    Ok, try this. Replace my example combobox, table and field names with your real ones and you should get the results you are looking for. Basically, the whole deal should look a sort of like this with this code being in the code module for the form the comboboxes are on.
    Code:
    Private Sub Form_Load()
        Me.cboManfufacturer.RowSource = "SELECT MfgrID, Make FROM tblManufacturers ORDER BY Make ASC;"
    End Sub
    
    Private Sub cboManfufacturer_AfterUpdate()
        Me.cboModel.RowSource = "SELECT ModelID, Model FROM tblModels WHERE MfgrID = " & cboManufacturer.Value & " ORDER BY Model ASC;"
        Me.cboModel.Requery
    End Sub
    
    Private Sub cboModel_AfterUpdate()
        Me.cboSubModel.RowSource = "SELECT SubModelID, SubModel FROM tblSubModels WHERE ModelID = " & cboModel.Value & " ORDER BY SubModel ASC;"
        Me.cboSubModel.Requery
    End Sub
    thanks for the code..had a few questions...

    in first part, where does cboManfufacturer come from? is that from the "control source" box on "data" tab or is that the "name" box from "other" tab?

    and not sure where am i pasting the code?

    heres a few screen shots...

    Click image for larger version. 

Name:	form.JPG 
Views:	4 
Size:	32.5 KB 
ID:	16265

    Click image for larger version. 

Name:	table1.JPG 
Views:	2 
Size:	25.3 KB 
ID:	16266

    Click image for larger version. 

Name:	tblmfgr.JPG 
Views:	1 
Size:	22.7 KB 
ID:	16267

    Click image for larger version. 

Name:	tblmdl.JPG 
Views:	2 
Size:	24.7 KB 
ID:	16268

    Click image for larger version. 

Name:	tbltrim.JPG 
Views:	2 
Size:	19.7 KB 
ID:	16269

    also attached copy of database

    test.zip

    thanks for the help
    Last edited by fastforded; 03-28-15 at 13:37.

  7. #7
    Join Date
    Mar 2015
    Posts
    27
    Quote Originally Posted by fastforded View Post
    thanks for the code..had a few questions...

    in first part, where does cboManfufacturer come from? is that from the "control source" box on "data" tab or is that the "name" box from "other" tab?

    and not sure where am i pasting the code?
    Ok, I modified my example to fit your sample database.

    Code:
    Private Sub Form_Load()
        Me.cboManufacturer.RowSource = "SELECT mfgrid, mfgr FROM tblmfgr ORDER BY mfgr ASC;"
    End Sub
    
    
    Private Sub cboManufacturer_AfterUpdate()
        Me.cboModel.RowSource = "SELECT ID, mfgrmdl FROM tblmdl WHERE mfgrid = " & cboManufacturer.Value & " ORDER BY mfgrmdl ASC;"
        Me.cboModel.Requery
        Me.cboTrim.RowSource = "SELECT ID, trim FROM tbltrim WHERE mfgrid = " & cboManufacturer.Value & " ORDER BY trim ASC;"
        Me.cboTrim.Requery
    End Sub
    In your test.accb, I did this: I added the code above to the code page for your form. I removed your row sources and control sources on the data tabs for each of the three drop downs, and I renamed the three dropdowns to cboManufacturer, cboModel, and cboTrim repectively by changing the default names on the Other tab for each dropdown. On the format tabs of each of the drop downs, I changed the column count property from 1 to 2 and added 0";1" to the column width property on each of them as well. With Form selected in the box at the top of the properties column, I set the On Load event of the form to [Event Procedure] by clicking the down arrow to the right of the On Load box on the forms events tab. I selected cboManufacturer in the same way and set it's After Update event in the same manner. You will now find that when you select a manufacturer that the contents of the model and trim dropdowns will be changed to reflect the correct choices for that manufacturer as defined in the related tables.

    BTW, you really need to name your tables and fields in a more clear way, such as tblManufacturers vs tblmfgr and so on. It will make your whole project much easier to understand and maintain.

    Your updated test.accdb file is attached as test1.zip

  8. #8
    Join Date
    Mar 2015
    Posts
    27
    BTW, this code is ONLY a working example to demonstrate linking the cascading combo boxes using your sample data as the contents. Its point is to demonstrate a way to have one combo box filter another, not tell you how your database should be designed, your combo boxes named and linked to control sources, etc as there are many ways to accomplish those depending on your design and goals.

  9. #9
    Join Date
    Jun 2013
    Posts
    10
    Quote Originally Posted by rodrich1954 View Post
    BTW, this code is ONLY a working example to demonstrate linking the cascading combo boxes using your sample data as the contents. Its point is to demonstrate a way to have one combo box filter another, not tell you how your database should be designed, your combo boxes named and linked to control sources, etc as there are many ways to accomplish those depending on your design and goals.
    thanks for the help!

    i was testing and it seems like changes made to form1 do not sync changes to table1. how can i get these form entries/changes to sync with table1?

    i am gonna take some time and see if i can understand the changes you made and how they effect the form/table...

    i was wondering, i could end up with a lot of tables for each "variable" such as mfgr, model, trim, etc.

    Is it better to make one table for say "all car info" and have each field on that one table be all the mfgr, model, trim etc? (or) maybe one table for say "chevy" that would have all the fields on one table for the chevy "options", like models, trims, engine sizes, rim sizes, custom options?

    or is it better to keep them all separate and just end up with as many separate tables as it takes? thinking about some engine sizes that vary depending on domestic (chevy 350 v-8) vs import (BMW inline 4 cyl. 2011–current – 2.0 L N20)?


    thanks for all the time,
    Last edited by fastforded; 03-30-15 at 08:22.

  10. #10
    Join Date
    Mar 2015
    Posts
    27
    You never asked about posting your results to table1, only how to make the dropdowns sync I would suggest adding a button for the user to click when he or she has selected the desired fields from all the dropdowns and then just read the values from each dropdown and use those to pull the values from the related tables and update the fields in your table1. Your table1 appeared to use the text data from the lookups versus the lookup codes used to sync between the dropdowns. Given that, you could go back to the lookup tables to pull that text using the ids you got from the dropdown .values but I would just pull the text from the dropdowns since it is already there. So in your event handler for the button you could do something like this...

    Code:
    ' column zero of the cboManufacturer combo box has the mfgrid and column 1 has the text name of the manufacturer and ListIndex is the row the user selected from the dropdown
    ' the other combo box dropdowns were loaded with data in the same fashion so...
    
    strManufacturer = Me.cboManufacturer.Column(1, Me.cboManufacturer.ListIndex)
    strModel = Me.cboModel.Column(1, Me.cboModel.ListIndex)
    strTrim = Me.cboTrim.Column(1, Me.cboTrim.ListIndex)
    And so on for all your combo boxes, then add the collected data to your table1 record fields. I leave that code task to you.

    And yes, it t would likely be better to take information that is common between many different vehicles and place them in separate lookup tables but without looking at sets of real data, it is difficult to suggest what would be best as far as breaking that out into the best layout and relationships.

Posting Permissions

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