none
Multiple sources of data for combobox RRS feed

  • Question

  • Hi guys,

    Is it possible to set the data drawn for a combobox from different sources depending on the answer in another field.  For example, I have a field [site] where the user selects the building they want, I then have a field [room] where they select the room they want.  The [room] field currently draws data from one table that contains all the rooms for both sites; I would like to have two tables (one for each site) and the [room] field to draw its data from the appropriate table depending on which site was selected.

    Any help will be much appreciated.

    Wednesday, January 25, 2017 11:50 AM

Answers

  • I would like to have two tables (one for each site) and the [room] field to draw its data from the appropriate table depending on which site was selected.

    Hi Alan,

    I would opt for one table for the Rooms, with a FK to a record in the Site table. You then can select the Rooms per Site using something like:\

        SELECT * FROM Room_tbl WHERE Site_id = 1

    Imb.

    • Marked as answer by AlanCambs Thursday, January 26, 2017 3:26 PM
    Wednesday, January 25, 2017 12:52 PM
  • Hi AlanCambs,

    you can refer the example below.

    I have 2 tables.

    (1) tblbird

    (2) tbl animal

    I create a form and place 1 list box and 1 combobox.

    the data in combobox will be load based on the value of list box.

    code:

    Private Sub Form_Load()
    Me.List2.AddItem ("Birds")
    Me.List2.AddItem ("Animals")
    End Sub
    
    Private Sub List2_Click()
    Dim Recordset As Object
    
    If (Me.List2.Value = "Birds") Then
    Set Recordset = CurrentDb.OpenRecordset("SELECT * FROM tblbird", dbOpenSnapshot)
    Set Me.Combo0.Recordset = Recordset
    Else
    Set Recordset = CurrentDb.OpenRecordset("SELECT * FROM tblanimal", dbOpenSnapshot)
    Set Me.Combo0.Recordset = Recordset
    End If
    
    End Sub

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 26, 2017 3:14 AM
    Moderator

All replies

  • I would like to have two tables (one for each site) and the [room] field to draw its data from the appropriate table depending on which site was selected.

    Hi Alan,

    I would opt for one table for the Rooms, with a FK to a record in the Site table. You then can select the Rooms per Site using something like:\

        SELECT * FROM Room_tbl WHERE Site_id = 1

    Imb.

    • Marked as answer by AlanCambs Thursday, January 26, 2017 3:26 PM
    Wednesday, January 25, 2017 12:52 PM
  • Hi AlanCambs,

    you can refer the example below.

    I have 2 tables.

    (1) tblbird

    (2) tbl animal

    I create a form and place 1 list box and 1 combobox.

    the data in combobox will be load based on the value of list box.

    code:

    Private Sub Form_Load()
    Me.List2.AddItem ("Birds")
    Me.List2.AddItem ("Animals")
    End Sub
    
    Private Sub List2_Click()
    Dim Recordset As Object
    
    If (Me.List2.Value = "Birds") Then
    Set Recordset = CurrentDb.OpenRecordset("SELECT * FROM tblbird", dbOpenSnapshot)
    Set Me.Combo0.Recordset = Recordset
    Else
    Set Recordset = CurrentDb.OpenRecordset("SELECT * FROM tblanimal", dbOpenSnapshot)
    Set Me.Combo0.Recordset = Recordset
    End If
    
    End Sub

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 26, 2017 3:14 AM
    Moderator
  • Much depends on the whether you are using the 'correlated' combo boxes in a form in single form view, or one in continuous forms view, and on whether the second combo box's BoundColumn property refers to a hidden numeric 'surrogate' key, or to the visible column as a 'natural' key.  Correlating combo boxes like this is one situation where a natural key makes life much easier.

    You'll find an examples of how to build and use correlated combo boxes in various contexts in ComboDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    Thursday, January 26, 2017 12:21 PM
  • Cheers guys! Great advice :)
    Thursday, January 26, 2017 3:26 PM
  • Thanks Deepak,

    That works great.  I was just wondering if that code could be expanded, say for example you had 3 or 4 options in List2/tables, such as animals, birds, fish and insects?

    Friday, January 27, 2017 2:48 PM
  • Hi Alan,

    If you use one table for AnimalClasses and one table for Animals, with a FK to AnimalClasses, the possibilities are almost unlimited.

    Instead Animals and AnimalClasses you can also read Rooms and Sites.

    Imb.

    Friday, January 27, 2017 2:59 PM
  • Hi AlanCambs,

    you can expand it as per your requirement.

    there is no any limit.

    it just a simple if condition which check the selected text from the list box and fetch the data from relevant table.

    you just need to add "else if" condition to match the text and if text get match then execute that block of condition.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 30, 2017 6:26 AM
    Moderator