We help IT Professionals succeed at work.

Access 2010 Visual Basic: How to have “upstream” combobox choice restrict/filter “downstream” combobox choice

Medium Priority
531 Views
Last Modified: 2012-03-12
Background:

•      I have two comboboxes that take their data from linked SQL Server tables.
•      The first combobox Temporary_Or_Permanent has the user choose either “Temporary” or “Permanent” as a value. Temporary_Or_Permanent combobox choices
•      The second combobox DB_Type should show only data that matches data from the choice made in Temporary_Or_Permanent field once the User clicks to activate the dropdown of the DB_Type field. Db_Type combobox and underlying database table

The Problem:
•      I’m struggling to find a way in Visual Basic, in the  After Update event, to have the choice of Temporary_Or_Permanent field figure in some kind of filter.  
•      Do I use a WHERE clause query?
•      Is there some specialized function?
•      How do I populate a Combobox?
Comment
Watch Question

In the AfterUpdate event of the first combo, insert the following code:

If IsNull(Combo1) = False Then
    Combo2.RowSource = "SELECT DISTINCT Db_Type FROM [dbo.DB_Type] WHERE [Temporary_or_Permanent]=" & Chr(39) & Me.Combo1 & Chr(39)
Else
    Combo2.RowSource = "SELECT DISTINCT Db_Type FROM [dbo.Db_Type]"
End If

Hope this helps,
pT72
You'll want to insert this code into the Form_Current event as well, so that the droplist is kept in sync when you navigate records, not just change the current one.

HTH,
pT72

Author

Commented:
Thanks for your quick feedback.
I'll try to implement it and will let you know how things turn out.

Regards,
Peter

Author

Commented:
This was very helpful and worked with a minor glitch on my part, I had to use the Access link table equivalent of dbo.Db_Type, namely, dbo_Db_Type.
Again, many thanks!
Peter

Explore More ContentExplore courses, solutions, and other research materials related to this topic.