Filter Parent Form by Subform's Listbox Selection

[CC]

Hi, I'm at my wits end with this. Could someone please help out?

I have a MainForm which contains a SubForm. The SubForm contains a
Listbox (single select, not multiselect).
I can return the value selected in the SubForm Event OnClick.
The MainForm is bound to a table as it's RecordSource.

I would like to take the value selected in the listbox on the SubForm
and use it to filter the results
on the MainForm.

My Current SubForm Event OnClick Code:
Private Sub StuffList_Click()
Forms![FM_TB_MainForm]!TestStuff = Me.StuffList 'Returns
selected item to TextBox on MainForm.
Forms![FM_TB_MainForm].Form.Filter = "((TB_Table.[Part-No]= " &
me.StuffList & "))"
Forms![FM_TB_MainForm].Form.FilterOn = True
End Sub

Running this code I get a 'Run-Time Error '2465': Microsoft Access
can't find the field 'Forms' referred to in your expression'.

Sorry if there is some obvious solution. Could you please help.
Pulling out all my last remaining hairs. o_O

 

[kingston via AccessMonster.com]

Use the ListBox's AfterUpdate event to create a criteria string (a WHERE
clause without the WHERE). Then set the MainForm's filter and apply it:

Me.Parent.Form.Filter = "[Field123]='" & Me.Listbox & "'"
Me.Parent.Form.FilterOn = True

Hi, I'm at my wits end with this. Could someone please help out?

I have a MainForm which contains a SubForm. The SubForm contains a
Listbox (single select, not multiselect).
I can return the value selected in the SubForm Event OnClick.
The MainForm is bound to a table as it's RecordSource.

I would like to take the value selected in the listbox on the SubForm
and use it to filter the results
on the MainForm.

My Current SubForm Event OnClick Code:
Private Sub StuffList_Click()
Forms![FM_TB_MainForm]!TestStuff = Me.StuffList 'Returns
selected item to TextBox on MainForm.
Forms![FM_TB_MainForm].Form.Filter = "((TB_Table.[Part-No]= " &
me.StuffList & "))"
Forms![FM_TB_MainForm].Form.FilterOn = True
End Sub

Running this code I get a 'Run-Time Error '2465': Microsoft Access
can't find the field 'Forms' referred to in your expression'.

Sorry if there is some obvious solution. Could you please help.
Pulling out all my last remaining hairs. o_O

 

[CC]

Use the ListBox's AfterUpdate event to create a criteria string (a WHERE
clause without the WHERE). Then set the MainForm's filter and apply it:

Me.Parent.Form.Filter = "[Field123]='" & Me.Listbox & "'"
Me.Parent.Form.FilterOn = True

Hi, I'm at my wits end with this. Could someone please help out?

I have a MainForm which contains a SubForm. The SubForm contains a
Listbox (single select, not multiselect).
I can return the value selected in the SubForm Event OnClick.
The MainForm is bound to a table as it's RecordSource.

I would like to take the value selected in the listbox on the SubForm
and use it to filter the results
on the MainForm.

My Current SubForm Event OnClick Code:
Private Sub StuffList_Click()
Forms![FM_TB_MainForm]!TestStuff = Me.StuffList 'Returns
selected item to TextBox on MainForm.
Forms![FM_TB_MainForm].Form.Filter = "((TB_Table.[Part-No]= " &
me.StuffList & "))"
Forms![FM_TB_MainForm].Form.FilterOn = True
End Sub

Running this code I get a 'Run-Time Error '2465': Microsoft Access
can't find the field 'Forms' referred to in your expression'.

Sorry if there is some obvious solution. Could you please help.
Pulling out all my last remaining hairs. o_O

Actually, I found the problem. I wasn't putting the " ' " around the
listbox in the above statement.

So my statement should be:
Forms![FM_TB_MainForm].Form.Filter = "((TB_Table.[Part-No]= '" &
me.StuffList & "'))"