Solved

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

Posted on 2012-03-12
3
477 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

My experience with Windows 10 over a one year period and suggestions for smooth operation
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

809 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