Have the selection in one combo box limit the selections in another 1

[pduncan]

I was asked to make an Access (2003) application to help keep up with what our regional controllers are doing. I have a form that they would use for data entry - they select their name, the location at which they performed a task, the date, comments, etc.

I would like to have the selection in one combo box (their name)limit the selections available in another combo box (their locations). In other words, when "Jim" selected his name, the location combo box would only display Jim's locations.

cboRCNo is a list of names of regional controllers - here is the Row Source:
SELECT tblController.RCNo, tblController.RCName
FROM tblController
ORDER BY tblController.RCName;


cboLocationID is a list of locations that the controllers are responsible for - here is the row source:
SELECT tblLocations.LocationID, tblLocations.BranchNo, tblLocations.BranchName
FROM tblLocations
ORDER BY tblLocations.BranchNo;


I have a table (tblLocations) that associates the Branch Number to a controller - it has four columns:
LocationID (PK), Branch Number, Branch Name, and RCNo.

RCNo is a FK to tblController - which has 2 columns:
RCNo and Controller (Names).

All this is stored in a table called tblEvent - the combo boxes on the form are lookup fields in this table (which lookup values from the tables described above.)

Anyway - I have exhausted the usefulness (to me) of the help files, and have read several posts, but have not had any luck with this. Can someone Please point me in the right direction?




Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[PHV]

In the AfterUpdate event procedure of cboRCNo :
cboLocationID.RowSource = "SELECT LocationID, BranchNo, BranchName" _
& " FROM tblLocations WHERE RCNo=" & cboRCNo & " ORDER BY BranchNo"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Remou]

Hi
If you set the row source of the location combo box to include a reference to the regional controllers combobox.
For example, if you change the rowsource to:
[tt]="SELECT tblLocations.LocationID, tblLocations.BranchNo, " _
& "tblLocations.BranchName " _
& "FROM tblLocations " _
& "WHERE tblLocations.RCNo = " & Me!cboRCNo _
& "ORDER BY tblLocations.BranchNo"[/tt]
(I hope I have that syntax right)
You may need to add column references for cboRCNo, depending on the data that shows. The above assumes cboRCNo shows a number.

 

[pduncan]

PHV - that did it - Thanks!

Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.