Solved

how to update a combo box based off values in another combo box?

Posted on 2011-02-28
3
335 Views
Last Modified: 2013-11-28
I am trying to make selecting information easier on my form so I'd like to have combo boxes that update after a selection has been made.

I have a combo box that displays names of audits. The row source has this query in it:
SELECT [tbl_Audit].[Audit_ID], [tbl_Audit].[Audit_Name] FROM tbl_Audit ORDER BY [Audit_Name];

I want a second combo box to be based off the tbl_audit.audit_id that a user selects. I want to be able to show
SELECT tbl_observation.obs_id FROM tbl_observation, tbl_audit WHERE tbl_observation.audit_ID = the value that the user selected from the combo box above

I was thinking of whether this could be done just using the after updates or before update spots in the Properties boxes or if this had to be done with VB?
0
Comment
Question by:jtovar3
  • 2
3 Comments
 
LVL 14

Accepted Solution

by:
pteranodon72 earned 500 total points
Comment Utility
This technique is called Cascading Comboboxes. It does require using VBA. There is a great tutorial here:
http://www.fontstuff.com/access/acctut10.htm

You will need to use code in the After Update event of the first combo box. You will also need to put code into the form's On Current event so that the correct list of choices is shown for previously entered data.

HTH,

pT72
0
 
LVL 14

Expert Comment

by:pteranodon72
Comment Utility
<Assuming that AuditID is stored as a number field (not text field, like in the above website) >

Private Sub Combo1_AfterUpdate()
If IsNull(Combo1) = False Then
    Combo2.RowSource = "SELECT tbl_observation.obs_id FROM tbl_observation, tbl_audit WHERE tbl_observation.audit_ID = " & Combo1
Else
    Combo2.RowSource = "SELECT tbl_observation.obs_id FROM tbl_observation, tbl_audit"
End If
End Sub

Private Sub Form_OnCurrent()
If IsNull(Combo1) = False Then
    Combo2.RowSource = "SELECT tbl_observation.obs_id FROM tbl_observation, tbl_audit WHERE tbl_observation.audit_ID = " & Combo1
Else
    Combo2.RowSource = "SELECT tbl_observation.obs_id FROM tbl_observation, tbl_audit"
End If
End Sub

Open in new window


If AuditID was stored as text in the underlying table, you'd need to surround its value with apostrophes.

HTH,
pT72
0
 

Author Closing Comment

by:jtovar3
Comment Utility
PERFECT!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 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

9 Experts available now in Live!

Get 1:1 Help Now