Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Angry Unanswered: Cascading Combo Boxes (again!)

    Hi,

    I'm new to the forum and fairly new to Access. Writing code is not my strong point!

    I know this Cascading Combo Box thing has been written about a lot here, but I can't seem to find a solution that matches my problem.

    I have a form that enters data with combo boxes into a table that contains a log of ICT equipment, with fields such as Room Number, Department, Category etc. This table is called tblAssets. I also have tables called tblRoom, tblDept etc, which my combo boxes use as look-up lists. These tables are updated when a new room number or whatever is entered.

    At the bottom of the form I require a subform that can be used to view already entered records, but I need the form to be filtered. I would like the subform to contain two more combo boxes. The first should show a list of sorting options (to sort by room, department etc). I have another table containing this list. The second combo box should then (for example) provide a list of room numbers if the first box was set to Room. Finally (continuing with the "sort by room" example) the subform should update to show the records that the tblAssets table contains that include the room selected with the second combo box.

    I hope this makes sense! I can manage the code to update the second combo box (I think!). I expect I have probably gone about this the wrong way, and I am quite prepared to start again if need be.

    Any suggestions would be gratefully received.

    Many thanks

  2. #2
    Join Date
    Dec 2003
    Posts
    18

    Re: Cascading Combo Boxes (again!)

    Hello.

    I'm not an expert at this, but I have encountered the same problem you describe. I can't give you specifics, but maybe I can point you in the right direction.

    First, I'm not sure you need the subform. Maybe you want it for appearances or organization, but I don't think it's necessary.

    Then, get your first two combo boxes working. The first combo box determines the list of the second combo, right? Okay, then the second combo determines the list of the third combo, no? I might have misunderstood your goal.

    Once you get the idea of coding one combo to determine another combo, the rest of your work should be a lot easier.

    Also, I assume your room name and room number are separate fields. Otherwise, you will probably have to use some code to identify the part of the field data you want displayed. . . a bit more tricky, at least for me.

    I hope this helps get you started or past your current hurdle. Let me know if I can be of any further help.

    Amadea

  3. #3
    Join Date
    Dec 2003
    Posts
    3

    RE. Cascading Combo Boxes (Again)

    Thanks a lot for your reply Amadea.

    My main problem was how to get the results of the first combo box to indicate which table should be used to provide a list for the second combo box.

    However I have since started again and included all the lists on one table, which so far seems to be working out OK.

    I would still be interested to know if it can be done with multiple tables (preferably without VB), so if you or anyone else has any ideas please let me know.

    Thanks again Amadea.

  4. #4
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51

    Re: RE. Cascading Combo Boxes (Again)

    L Booth,
    If you want to avoid coding you can try somthing like,
    use the query builder for the rowsource in the second combo box and in that query use the expression builder to set the criteria for the common field to the value of the first combobox.

    If this does not fix your problem then you will need to use code to assign the rowsource of combobox 2 after combobox 1 is selected.

    Somthing like
    ---------------------------------------------------------------
    Private Sub Combo1_AfterUpdate()
    Dim strSQL As String

    '-- If the output of combo1 is the table name then
    strSQL = Me.Combo1.Value

    '-- If you need a SQL Query then
    strSQL = "Query string ..." & Me.Combo1.Value & _
    " ... Rest of SQL string"

    Me.Combo2.RowSource = strSQL
    Me.Combo2.Requery
    End Sub
    ---------------------------------------------------------------

    I hope this helps.
    Bob

  5. #5
    Join Date
    Dec 2003
    Posts
    3

    re. Cascading Combo Boxes (again)

    Hi Bob,

    Thanks for that info. Looks very promising. I'll try it out later.

    Thanks again.

  6. #6
    Join Date
    Nov 2009
    Posts
    3

    Cascading (or Synchronizing) Combo Boxes

    Hello, everyone--Access 2007 newbie here and I'm trying to throw a computer equipment tracking DB together for my unit.

    I am a new Access 2007 user and have almost no VB knowledge. I have followed MANY, MANY tutorials and tips, hints, and tricks, but they've all been unsuccessful (even the downloadable Sampe DB from Microsoft...). Unfortunately I'm not able to post the database, but here goes...



    I have four tables:



    tblComputers

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

    Device Type -- Number (FK for PK_Device_ID)

    Make -- Number (FK for PK_Make_ID)

    Model -- Number



    tblMake

    -------

    PK_Make_ID -- AutoNumber

    Make -- Text



    tblModel

    --------

    PK_Model_ID -- AutoNumber

    Model -- Text

    MakeID -- Number (FK for PK_Make_ID)



    tblDeviceType

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

    PK_Device_ID -- AutoNumber

    Device Type -- Text

    ModelID -- (FK for PK_Model_ID)



    The "Model" field in "tblComputers" is a number b/c it had been a FK for PK_Model_ID, but I have since broken "tblModel"'s relationship with "tblComputers" and plugged it straight into "tblMake."



    I am trying to do a (supposedly) simple synchronized combo box setup. A user selects "Device Type" from its combo box (cboDevice), and the models are limited to a certain item (i.e. laptop, printer, etc.). Then the user selects "Make" in a combo box (cboMake), and the choices in the "Model" combo box (cboModel) are limited to that particular manufacturer.

    I'm sure the "Device Type" portion is all messed up as I was just trying to get at least the "Make" and "Model" part working, but nothing yet.

    I have struggled for over 12 hours to get this thing to work without success. Can someone please help me out here? I'd greatly appreciate it!



    Chris

  7. #7
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51

    re: Cascading (or Synchronizing) Combo Boxes

    Chris, It appears that you need to revisit your table design. From what I can tell by your descriptions device type should report to models not models to device type. If your goal is to identify each piece of equipment by category then you would want to remove ModelID from tblDeviceType and add DevID to tblModel. You could then have a combo box that selects the device type (printer, PC, notebook...), you would then use the value of that combo box to filter another combo box that would select the model. The same logic could be applied to a selection by Make. In this case there is no reason for the Computers table unless you want to add more information such as serial #, owner,...

    I hope this makes sense.

    Bob
    Bob

  8. #8
    Join Date
    Nov 2009
    Posts
    3

    Re:

    Yeah, I do have some other pieces of data to capture, so the tblComputers isn't superfluous.

    Thanks for the tip on the tblDeviceType--although I'm getting a little ahead of myself on that one since I'd be happy at this point to get just "make" and "model" working.

    I think I've figured out where I'm going wrong (sort of). When I enter the following VB code into the AfterUpdate for cboMake...:

    me.cboModel.RowSource = "SELECT Model FROM" & _
    "tblModel WHERE MakeID = " & _
    Me.cboMake & _
    " ORDER BY Model"
    me.cboModel = me.cboModel.ItemData(0)

    ...the query that SHOULD be generated here is NOT passed into the RowSource value of cboModel. I don't know enough about Access or VB to understand why, but this code is copied character-for-character from the numerous Microsoft tutorials out there, with only the combo box names changed, so there shouldn't be any reason for it not to work.

  9. #9
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51
    If all of your field names are correct then the only problem I see is that if this is the exact code then you need to add a space after the From.

    your first line should read - me.cboModel.RowSource = "SELECT Model FROM " & _

    Notice the space after the FROM?
    With out this your SQL would read "SELECT Model FROMtblModel WHERE MakeID = " & _
    Bob

  10. #10
    Join Date
    Nov 2009
    Posts
    3

    re:

    Ah, good catch, there, but that still didn't solve it...it's still refusing to pass the VB query code into the cboModel RowSource...

    OK, so I can't attach my original DB, but I went ahead and put together a bare bones version of it with the relevant parts that are malfunctioning. I saved it in Access 2003 and Access 2007 formats. Could you please take a look at it and see what I'm doing wrong?

    Thanks so much for any help you can give me!

    Chris
    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2012
    Posts
    7

    casecade combo box

    hellloooo...

    m new to access 2007...n making database of spares.which includes part no,main eqpt,nomenclature quantity and location. all these info is in main table(T_db1).i hav made a form..to issue any spare..it includs:
    part no
    main eqpt
    nomen
    location
    quantity to b issued

    i want ,,whenevr i enter part no,..main eqpt,nomen,location shud b automaticlly come..i hav tried alot in cascading these combo boxes..bt :/

    2nd problem is i want ,whenvr i issue any spare...this info is also save to anothr table...

    neeed ur helpp..
    thanxxx

Posting Permissions

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