By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,479 Members | 1,601 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,479 IT Pros & Developers. It's quick & easy.

Combobox Rowsource

Alireza355
P: 86
Dear friends,

I have 2 comboboxes in a form. I want the first combobox to show all records of my table if the second combobox is null.

But if the second combobox is not null, I want the first combobox to show only information which contains the selected item in the second combobox.

Example:

field1 field2
alfa 1
bravo 1
charlie 1
david 2
echo 2
fox 2
golf 2
honey 3
item 3
jack 3

I want the first combobox to show all the "field1" items if the second combobox is null, and if the second combobox is for example "2", I want the first combobox to show only "david", "echo", "fox" and "golf"

Thank you.
Jun 27 '09 #1
Share this Question
Share on Google+
2 Replies


P: 13
This process is called cascading combo boxes. There is a tutorial here but I have made my own tutorial specific to your needs which is listed below:
  1. Firstly create two tables:

    Expand|Select|Wrap|Line Numbers
    1. TableNumbers 
    2. [ID] – AutoNumber, PK 
    3. [Number] – Number, This is your 1, 2, or 3
    4.  
    5. TableWords
    6. [ID] – Autonumber, PK
    7. [Word] – Text, This is your Alfa, Bravo, Charlie, ect 
    8. [NumID] – FK, Used to link the word to the number
    Now enter your values you stated before (1, 2 & 3 into TableNumbers, and Alfa, Bravo, Charlie, ect with the corresponding numbers into the TableWords).


  2. Then on the form where you want the combo boxes, you need to create the combo box containing the numbers. Create a combo box (without wizard) called "Combo1":

    Row Source – TableNumbers

    Column Count – 2 (We use 2 columns because we want to include both ID and Number)

    Column Widths – 0cm;2cm (We set the first column to 0” because the user does not need to see the ID)

    Bound Column – 1 (We bind it to the first column so that when we refer to [Combo1], it will return the ID rather than Number)

    Then create another combo called "Combo2" but leave this one blank the coding later will put the data into it.


  3. What we want is to change the Row Source of [Combo2] whenever the user makes a change of selection to [Combo1].

    So, in the After Update event property of [Combo1], you’ll want to change it to [Event Procedure].

    Then, click on the "..." button to bring up the Visual Basic Editor. In the module, put in the following code:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Combo1_AfterUpdate()
    2.   With Me![Combo2]
    3.       If IsNull(Me!Combo1) Then
    4.       .RowSource = "SELECT [Word] " & _
    5.                    "FROM TableWords "
    6.       Else
    7.       .RowSource = "SELECT [Word] " & _
    8.                    "FROM TableWords " & _
    9.                    "WHERE [NumID]=" & Me!Combo1
    10.     End If
    11.     Call .Requery
    12.   End With
    13.   Forms!Form1!Combo2 = ""
    14. End Sub

Now when 1 is selected in Combo1, Alfa, Bravo & Charlie will be in Combo 2.

When 2 is selected, David, Echo, Fox & Golf will appear.

When 3 is selected Honey, Item & Jack will appear.

When Combo 1 is blank, all of the values appear in Combo2.
Jun 27 '09 #2

Alireza355
P: 86
Thanx a lot.

It works...
Jun 28 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.