Cascading combo box and enter data through query (1 Viewer)

[hilian]

I need a set of cascading combo box on my form. The user will be entering data about cases belonging to social service agencies. The user needs to enter the name of the agency and then the name of a program run by that agency. The names of the agencies and their programs are in a table called tblAgyPgm. The field names are AgencyName and ProgramName. The combo boxes are called cboAgency and cboPgm. The form runs off a query that combines a main table containing the basic case information with a related table that holds the program information (many to one) and one related to it that holds agency information (one to many) for each case.

I set the control source for cboAgency as the AgencyName field on the form and for cboPgm as ProgramName. In the cboAgency combo box I set the row source as:

SELECT DISTINCT tblAgyPgm.AgencyName FROM tblAgyPgm ORDER BY tblAgyPgm.AgencyName;

For After Update, I entered the following code:

Private Sub cboAgency_AfterUpdate()
On Error Resume Next
cboPgm.RowSource = "Select tblAgyPgm.ProgramName " & _
"FROM tblAgyPgm " & _
"WHERE tblAgyPgm.AgencyName = '" & cboAgency.Value & "' " & _
"ORDER BY tblAgyPgm.ProgramName;"
End Sub

I tested this with records that are already in the table. The cboAgency combo box populates with the name of the agency in the Agency Name field, and when I click the down arrow, it displays the list of agencies. I set the control source for the cboPgm combo box to Program Name, and it shows the name of the program. But when I click on the down arrow, it doesn’t show a list of programs.

Access automatically starts the code with: Option Compare Database. I’ve tried both and without this line. Neither way works.

The other part of the problem is that I need to populate the agency and program information fields, but neither are fields in the main table, only lookups to the separate, related tables that store this information.


Many thanks for any help.

 

[liddlem]

Does it help to do the following?
Create a query that gets its parameter from CboAgency.
Set the CboAgency After Update event to refresh the query.
Then the rowsource for CboPgm is set to the query.

 

[Pat Hartman]

Rather than constantly replacing the RowSource, use a parameter that references the previous combo:

for combo2:
Select .. From .. Where somefield = Forms!frmyourform!combo1
for combo3:
Select ... From ... Where somefield = Forms!frmyourform!combo2
etc.

Then in the AfterUpdate event of each combo, requery the dependent combos:
AfterUpdate for combo1
Me.combo2.Requery
Me.combo3.Requery
etc.
AfterUpdate for combo2
Me.combo3.Requer
etc.

 

[hilian]

Does it help to do the following?
Create a query that gets its parameter from CboAgency.
Set the CboAgency After Update event to refresh the query.
Then the rowsource for CboPgm is set to the query.

Thanks Liddlem. I tried it , and unfortunately I couldn't make it work. I created a query called qryAgy; I set the parameter to the combo box, and I set I set the cboAgency After Update event to refresh the query with me.Requery. Then I set the rowsource for cboPgm to the query. I was able to populate the first combo box, cbo Agency, but nothing appeared in cboPgm. Then I thought, "there's nothing in the query that would give me program names," so I added the ProgramName field to qryAgy from the query that feeds the form and specified this field in the rowsource for cboPgm with SELECT DISTINCT qryAgy.ProgramName. Still nothing.

Do you see anything I might have missed?

 

[hilian]

Pat, thanks. I changed the row source in the second combo box (there are only two) to:

SELECT tblAgyPgm.ProgramName FROM tblAgyPgm WHERE AgencyName=Forms!frmCases!cboAgy;

In the After Update event for the first combo box, cboAgy, I used:

Private Sub cboAgy_AfterUpdate()
Me.cboPgm.Requery
End Sub

Unfortunately, it didn't work. The cboAgency combo box populated, but the cboPgm stayed empty.

Did I misunderstand the instructions or could something else be missing?

 

[Pat Hartman]

Add a requery for the second combo in the form's current event.

Is this a continuous form?

 

[hilian]

Thanks, Pat. Do I keep the requery in the after event, and is the syntax for the after event correct? Is the syntax the same for the current event?

This isn't a continuous form.

 

[Pat Hartman]

Yes. You need to requery when the the controlling combo is changed so the AfterUpdate event of the first combo takes care of that. The requery in the Current event takes care of existing records.

 

[hilian]

Pat,
Thanks. First I tried my original code, which didn't work when I based the form on a query. When I based it directly on a table it worked. There doesn't seem to be any reasobn for it, but that's what happened. Then I tried the code you gave me. It also worked. As I think about it, I'm not sure I need the query, anyway.

On to the next problem,

Henry