Nested form filtering

[Jarda Beran]

Hi,
I have a problem with nested form filtering.

Problem description (simplified a little bit):

I have table "EVENTS" in which a data about events are stored - fields are
EVENT_ID (automatically generated primary key), EVENT_NAME and PROCESS (in
this field user can via combo-box select process from another table called
PROCESSES).

Then I have table called EVENT_PROCESS_STEPS, where user can select several
process steps for an event. This is done again via combo-box - process steps
are loaded into combo-box from table called PROCESS_STEPS. In
EVENT_PROCESS_STEPS table I have fields RECORD_ID (automatically generated
primary key), EVENT_ID (supplied by Access engine through relations),
PROCESS_STEP_ID (combo-box selection from PROCESS_STEPS table).

Table PROCESSES has fields PROCESS_ID (automatically generated primary key),
PROCESS_NAME, PROCESS_DESCRIPTION.

Table PROCESS_STEPS has fields PROCESS_STEP_ID (automatically generated
primary key), PROCESS_ID (selected from PROCESSES table via combo-box),
PROCESS_STEP_DESCRIPTION.

Tables EVENTS and EVENT_PROCESS_STEPS have relationship 1:many through
EVENT_ID fields. Tables PROCESSES and PROCESS_STEPS have relationship 1:many
through PROCESS_ID fields.

I have form frmEVENT, with fields from table EVENT and on this form is
nested datasheet form frmEVENT_PROCESS_STEPS with fields from table
EVENT_PROCESS_STEPS.

I need to filter form frmEVENT_PROCESS_STEPS so in combo-box PROCESS_STEP
user can select only process steps belonging to process selected in combo-box
PROCESS on parent frmEVENT.

Everything I have tried was not successfull, so I will be gratefull for any
advice.

Regards

Jaroslav

 

[Maurice]

How about creating a query for your combo Process steps and set the criteria
in this query to your parent_id field in the main form. That way whenever the
parent_id changes you requery the form and your combo should be filtered to
the options linked to the parent_id.

What did you came up with so far in other words what did you try for
starters...

 

[Jarda Beran]

Hi, Maurice,

thank you very much for advice - now I have this in PROCESS_STEP field:
SELECT PROCESS_STEPS.PROCESS_STEP_ID, PROCESS_STEPS.PROCESS_STEP_DESCRIPTION
FROM PROCESS_STEPS WHERE
(((PROCESS_STEPS.PROCESS_STEP_ID)=Forms!frmEVENT!PROCESS));

It works on existing events, but when I change parent combo value, it does
not requery, and also it does not work for new record. Do I have to set some
code for parent combo Change or AfterUpdate to force sub-form requery?

Before posting my question I've tried to set filters or data sources for
whole subform, but it did not work at all.

Again - thank you very much for your help.

Regards

Jaroslav

 

[Maurice]

Hi Jaroslav,

Good to hear we are on the right track. You have to indeed requery the combo
on the subform after you have changed the value in the combo on the main
form. In the after update of the combo in the main form you should place
something like:

forms!mainform!subformname!comboboxname.requery

where you replace the various controlnames to your own.

Keep in mind though that when requerying that you form is a single form and
not a continous form because in that case all the records will get a
different source for the combo in the subform. But reading your post i
believe that this isn't the case.

The new record option can be caught as well and there you also place the
requery option.