Solved

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

Posted on 2012-03-12
3
470 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?
0
Comment
Question by:stuckp1
  • 2
3 Comments
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 500 total points
ID: 37711671
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
0
 

Author Comment

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

Regards,
Peter
0
 

Author Closing Comment

by:stuckp1
ID: 37712045
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
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now