[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2012-03-12
3
Medium Priority
?
492 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
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…

649 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