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

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?
stuckp1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pteranodon72Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stuckp1Author Commented:
Thanks for your quick feedback.
I'll try to implement it and will let you know how things turn out.

Regards,
Peter
stuckp1Author 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.