Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

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

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
jtovar3
Asked:
jtovar3
  • 2
1 Solution
 
pteranodon72Commented:
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
 
pteranodon72Commented:
<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
 
jtovar3Author Commented:
PERFECT!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now