Filter Parent Form by Subform's Listbox Selection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CC - 23 Mar 2007 08:43 GMT

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

Reply to this Message

kingston - 23 Mar 2007 14:37 GMT

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?
>
[quoted text clipped - 21 lines]
>Sorry if there is some obvious solution. Could you please help.
>Pulling out all my last remaining hairs. o_O

Reply to this Message

CC - 26 Mar 2007 02:20 GMT

On Mar 23, 10:37 pm, "kingston via AccessMonster.com" <u27511@uwe>
wrote:

> 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:
[quoted text clipped - 32 lines]
>
> - Show quoted text -

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 & "'))"

Reply to this Message