populating a combo box based on selection in another combo box

[Brian Beck]

I've got a combo box in a form that queries tblDistrict to get a list of all
the districts and then displays them when the user clicks on that combo box
in the form. Below that I have another combo box that should list all the
campuses that are in whichever district was selected by the user in the
first combo box.

The query run from the District combo box has 2 fields in it, the district
ID (PK) and the district name.

The query run from the Campus combo box has 3 fields in it, the campus ID
(PK), the campus name and the district ID. I thought I could simply have
the query look to see what district ID was selected in the first combo box
and then only list those campuses that matched that campus ID...but I'm
getting hung up in the execution here.

1. What criteria do I need to use so that the campus query will successfully
pull the correct campuses? I'm thinking something like
forms!IncidentReport.DistrictComboBox.Column(0), since district ID is the
first column in the DistrictComboBox, but that doesn't seem to work.

2. Is there code I need to run for either combo box based on things like
AfterUpdate and OnEnter?

I know this shouldn't be difficult, but it's really giving me fits right
now.

-Brian

 

[SusanV]

Hi Brian,

This page should walk you through this:
http://www.fontstuff.com//access/acctut10.htm

 

[Brian Beck]

That link is a great starting point, but there is no example showing how to
program this when I'm dealing with 2 separate tables. I've got tblDistrict
and tblCampus instead of just one table that has the districts and their
campuses all together. From looking at the code example for example 2
(which seems most like what I would want) I'm having trouble figuring out
how to modify that for my needs. My best guess would be:

On Error Resume Next
cboCampus.RowSource = "Select tblCampus.CampusName " & _
"FROM tblCampus " & _
"WHERE tblCampus.DistrictID = '" & cboDistrict.Value & "' " & _
"ORDER BY tblCampus.CampusName;"My concern however is that this
doesn't seem to tell Access what value to get from cboDistrict. The user
will be selecting the name of the district from the combo box, but what I
want to match on is the actual DistrictID.

Is this making sense?

-Brian

 

[SusanV]

Are these 2 tables related? You'll need the JOIIN in your SQL...

 

[Brian Beck]

Yes, the 2 tables are related. There is a 1 to many relationship between
the DistrictID and the CampusID.

Can you be more specific about needing a JOIN in my SQL?

 

[SusanV]

The quickest, easiest way to get the SQL is to create your query in Query
design view, then change your view to SQL view. For info about joins in SQL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp