Filter Records in a Subform from a ComboBox within a Parent Form

  • Thread starter Thread starter Joe Coulter
  • Start date Start date
J

Joe Coulter

Hi group

Hope someone can help me with this one. its been driving me crazy

I hope that the description I have annotated below is adequate:-

I have a continuous form “FRM_REASONS†which shows records with 3 fields in
each
1 = “Type_Nameâ€
2 = “Reason_Codeâ€
3 = “Reason_Descriptionâ€

On the Header of the Form “FRM_REASONS†I have a ComboBox “Select_Typeâ€
which contains values from another Table “Tbl_Types†which has 2 fields
1 = “Type_Codeâ€
2 = “Type_Nameâ€

On the AfterUpdate Event of ComoBox “Select_Typeâ€, I have the following Code:-

Private Sub Select_Type_AfterUpdate()
DoCmd.ApplyFilter , "Typ_Code = Forms!Frm_Reasons!Select_Type"
End Sub

This works fine and filters the records within the form to the value
selected within the ComboBox

Problem
I have created a Form “FRM_HEADER†to use as a Navigation Form on which I
have a tab Control “TAB01†With 3 tabs, one of these Tabs “REASONS†holds the
Form “Frm_Reasons†as a Sub Form.

Now that the Form “Frm_Reasons†is these, the ComboBox “Select_Type†no
longer works.
The Values still Appear but cannot be selected.

I have tried Modifying the AfterUpdate Event of the ConboBox as follows:-

Added the Name of the Navigation Form “FRM_HEADER†to the Path within the Code
DoCmd.ApplyFilter , "Typ_Code =
Forms!FRM_HEADER!Frm_Reasons!Select_Type"

Added the Name of the Navigation Form “FRM_HEADER†and TAB “TAB01†to the
Path within the Code
DoCmd.ApplyFilter , "Typ_Code =
Forms!FRM_HEADER!TAB01!Frm_Reasons!Select_Type"

Both the changes failed to work, the values within the ComboBox
“Select_Type†can be seen when clicked, but none of them can be selected,
it’s as if the ComboBox was Locked.


Thanks in anticipation, Any Suggestions will be greatly appreciated
 
Hello Joe.

Joe said:
[...]
On the AfterUpdate Event of ComoBox "Select_Type",
I have the following Code:-

Private Sub Select_Type_AfterUpdate()
DoCmd.ApplyFilter , "Typ_Code = Forms!Frm_Reasons!Select_Type"
End Sub

This works fine and filters the records within the form to the value
selected within the ComboBox

Problem
I have created a Form "FRM_HEADER" to use as a Navigation Form on
which I have a tab Control "TAB01" With 3 tabs, one of these Tabs
"REASONS" holds the Form "Frm_Reasons" as a Sub Form.

Now that the Form "Frm_Reasons" is these, the ComboBox "Select_Type"
no longer works.
The Values still Appear but cannot be selected.

I have tried Modifying the AfterUpdate Event of the ConboBox
as follows:-

Added the Name of the Navigation Form "FRM_HEADER" to the Path within
the Code
DoCmd.ApplyFilter , "Typ_Code =
Forms!FRM_HEADER!Frm_Reasons!Select_Type"
[...]

Try to replace Frm_Reasons in your expression by the name of the
subform control on your tab page. To find out the name of that control:
Draw a rectangle with the mouse on the tab from outside the subform
into the subform such that only the subform control is selected. Then
read the name from the properties window.
 
Hello Wolfgang

Thank you for your reply, I tried your suggestion, unfortunately that did
not work, I also tried removing the Tab Control and placing the Form
Frm_Reasons directly onto the FRM_HEADER, this also failed, so frustrating
lol.

Anyway I will continue to Persevere with this, If I come up with an answer,
I will post it.

Again, Thanks

Joe

Wolfgang Kais said:
Hello Joe.

Joe said:
[...]
On the AfterUpdate Event of ComoBox "Select_Type",
I have the following Code:-

Private Sub Select_Type_AfterUpdate()
DoCmd.ApplyFilter , "Typ_Code = Forms!Frm_Reasons!Select_Type"
End Sub

This works fine and filters the records within the form to the value
selected within the ComboBox

Problem
I have created a Form "FRM_HEADER" to use as a Navigation Form on
which I have a tab Control "TAB01" With 3 tabs, one of these Tabs
"REASONS" holds the Form "Frm_Reasons" as a Sub Form.

Now that the Form "Frm_Reasons" is these, the ComboBox "Select_Type"
no longer works.
The Values still Appear but cannot be selected.

I have tried Modifying the AfterUpdate Event of the ConboBox
as follows:-

Added the Name of the Navigation Form "FRM_HEADER" to the Path within
the Code
DoCmd.ApplyFilter , "Typ_Code =
Forms!FRM_HEADER!Frm_Reasons!Select_Type"
[...]

Try to replace Frm_Reasons in your expression by the name of the
subform control on your tab page. To find out the name of that control:
Draw a rectangle with the mouse on the tab from outside the subform
into the subform such that only the subform control is selected. Then
read the name from the properties window.
 
Hello Joe.

Joe said:
Hello Wolfgang

Thank you for your reply, I tried your suggestion, unfortunately that
did not work, I also tried removing the Tab Control and placing the
Form Frm_Reasons directly onto the FRM_HEADER, this also failed, so
frustrating
lol.

Anyway I will continue to Persevere with this, If I come up with an
answer, I will post it.

You could try to use this cide instead (no matter where tho form is
placed):

Me. Filter = "Type_code=" & Me.Select_Type
Me.FilterOn = True

(If Type_Code is of type text, it should be:
"Type_code='" & Me.Select_Type & "'")
 
Thanks again Wolfgang

I will try your suggestion and let you know the result.

Regards

Joe
 
Hello Wolfgang

Works a Treat

Many Thanks

Joe


Joe Coulter said:
Thanks again Wolfgang

I will try your suggestion and let you know the result.

Regards

Joe
 
Back
Top