Filter the main form using the double clicked item in subform

  • Thread starter Thread starter Kirsty
  • Start date Start date
K

Kirsty

Hi

I have a form (frmInsufficientCrew) which contains a
subform (fsubInsufficientCrewToday).

The main form is mainly for data entry into the table
(tblInsufficientCrew). The subform is based on a query
filtered on date(qryInsufficientCrewToday) which displays
a subset of the fields in the main form.

I want to be able to double click on an item in the
subform query and the main form should jump to that record.

The obvious way to me seemed to assign the following macro
to the double click property of the fields in the subform:

Action:ApplyFilter
Where Condition: [Forms]![frmInsufficientCrew]![IC_ID]=
[Forms]![fsubInsufficientCrewToday]![IC_ID]

However, this results in a pop up to Enter Paramater Value
Forms!fsubInsufficientCrewToday!IC_ID

What am I doing wrong or is there a better way of doing
this? (I am still very inexperienced, so sorry if its very
trivial!)

Thanks in advance to anyone who can help!
Kirsty
 
Kirsty said:
Hi

I have a form (frmInsufficientCrew) which contains a
subform (fsubInsufficientCrewToday).

The main form is mainly for data entry into the table
(tblInsufficientCrew). The subform is based on a query
filtered on date(qryInsufficientCrewToday) which displays
a subset of the fields in the main form.

I want to be able to double click on an item in the
subform query and the main form should jump to that record.

The obvious way to me seemed to assign the following macro
to the double click property of the fields in the subform:

Action:ApplyFilter
Where Condition: [Forms]![frmInsufficientCrew]![IC_ID]=
[Forms]![fsubInsufficientCrewToday]![IC_ID]

However, this results in a pop up to Enter Paramater Value
Forms!fsubInsufficientCrewToday!IC_ID

What am I doing wrong or is there a better way of doing
this? (I am still very inexperienced, so sorry if its very
trivial!)
Click to expand...

A form being displayed within a subform control is not considered "open"
and is therefore not a member of the Forms! collection (which only contains
open forms).

You have to use the following syntax...

Forms!NameOfParentForm!NameOfSubformControl.Form!ControlOrFieldName

Two notes here:

The name of the subform *control* is usually the same as the form contained
within, but that is not always the case. The syntax above requires the
name of the control, not the form.

Take note of the "dot-Form" after the name of the subform control. Easy to
miss and it has to be there.
 
Thanks for that Rick, thats got rid of the 'Enter
Parameter' pop up... However... now it gives you a new
record in both the main form and in the subform!

This is my revised where condition:
[Forms]![frmInsufficientCrew]![IC_ID]=[Forms]!
[frmInsufficientCrew]![fsubInsufficientCrewToday].[Form]!
[IC_ID]
 
Thanks for that Rick, thats got rid of the 'Enter
Parameter' pop up... However... now it gives you a new
record in both the main form and in the subform!

This is my revised where condition:
[Forms]![frmInsufficientCrew]![IC_ID]=[Forms]!
[frmInsufficientCrew]![fsubInsufficientCrewToday].[Form]!
[IC_ID]
Click to expand...

Going to a new record is Access' normal behavior when applying a filter
with no matches found so it appears that there is still a problem with the
syntax. It's possible you need to delimit the filter string so it uses the
value found in the subform rather than using the subform reference
directly. I would do this in code rather than a Macro and I am not
well-versed in Macro usage. The code would look like...

Me.Parent.Filter = "[IC_ID]=" & Me![NC_ID]
Me.Parent.FilterOn = True
 
Back
Top