Hi,

I have a problem when trying to use cascading list on a form.

I have a first combo box named "Hoofdgroep" wich takes his list directly from a table "tblHoofdgroep". The "tblHoofdgroep" is joined with a table "tblSubEnHoofdgroepBA06" where I have 2 fields "Hoofdgroep" (that comes from the "tblHoofdgroep" table) and "Subgroep". Both are text type fields.

What I would like on the form is that when in the first combo box "Hoofdgroep" I select one of the values, the combo box "Subgroep" on the form only shows the values for wich it finds values in the "tblSubEnHoofdgroepBA06" table.

I'm using the following code for this:

private sub Hoofdgroep_afterupdate()

on error resume next
Subgroep.rowsource = "select tblSubEnHoofdgroepBA06 " & _
"from tblSubEnHoofdgroepBA06 " & _
"where tbl.SubEnHoofdgroepBA06.Hoofdgroep = '" & Hoofdgroep.value & "' " & _
"order by tblSubEnHoofdgroep.Subgroep;"

end sub

This code however does not work... Is there something wrong with it or am I just forgetting something?

Here is an easy way to do this:

Use the combo box wizard to select the field Hoofdgroep from tblHoofdgroep. This combo box should also select the PK for the records, but hide that and use it as the bund column, unless Hoofdgroep IS the PK for this table. Let's call this cboHoofdgroep.

Create a second combo box for Subgroep looking up in the tblSubEnHoofdgroepBA06 table, and include the Hoofdgroep FK field in the fields in the list (the fields should be the OPK field for tblSubEnHoofdgroepBA06, Subgroep, and Hoofdgroep FK). Let's call this cboSubGroep.

Then open the properties dialog box for cboSubGroep, and selec the data tab. Click on the Row Source, and open the query designer. In the column for the Hoofdgroep FK field, add this line in the Criteria:

[Forms]![frmYourFormName].[cboHoofdGroep]

Then Uncheck the Show check box. Don't click the save button, just close the query designer, and check yes to save the changes.

Go to the Format tab, and remove the last ";1" from the Column Widths list.

Then go to the After Update event of the cboHoofdGroep, and add this code:

Me.cboSubGroep.Requery

Did that work for you?



mmcdonal

"bund" = "bound"
"OPK" = "PK"
"selec" = "select"
Fat fingering everything this morning.

mmcdonal

It seems to work fine, but I still have a little question...

When I insert a record and move to a next record to insert, than the "Subgroep" combo box on the form for the new record still shows the "Subgroep" data of the previous inputted record (if I do not select a "Hoofdgroep" first). Is it possible to insert a code somewhere that when I move to a new record, that the "Subgroep" combo box should be emptied again?

You might put a requery event for both combo boxes on the On Current event of the form. Requery the first one, then the second. If that doesn't work I will work on a solution.

mmcdonal

This works fine, thanks very much