Filter subform via listbox on main form

[Camp]

I am new to access and struggling to get my sub-form to filter on a record i am selecting from a listbox on my main form.

my main form is called frmInput, my sub-form is called frmSubform

the two tables tblContractor and tblDetails are associated with each other via a one to many relationship, and i have a them linked together on the frmInput form i use for data input.



I have tried various combinations of code to try and filter the sub-form based upon a value from a list box when i click on a row in the list box.

The main form does filter when I click a row from the listbox (list29), however I cannot get the subform to filter.

the list box has "SELECT tblContractor.ID, tblDetails.idTravel as the first two columns of the list box, i can assign the listbox values to variables.


i can filter the main form using the following:

[code here]

Private Sub List29_Click()

' Trip ID
Text45 = Me.List29.Column(1)


X = Me.List29.Column(0)
Me.FilterOn = True
Me.Filter = "tblContractor.ID =" & X


'when i attempt to filter the subform

y = Me.List29.Column(1)

Me!frmsubform.Form.Filter = "[tbldetails].[idtravel] =" & y
Me!frmsubform.Form.FilterOn = True

[/code]


it fails

i have attempted many variation of the subfrom filtering code to no eval, access keeps telling me it cant find frmsubform



any help would be greatly appreciated

[ranman256]

This should work if your col#s are correct.

try adding a requery...

Me!frmsubform.Form.Filter = "[tbldetails].[idtravel] =" & y
Me!frmsubform.Form.FilterOn = True
frmsubform.requery

if still not working, I sometimes use 2 queries.
unfiltered: frmsubform.rowsource = "qsAllRecs"

after list update

Text45= Me.List29.Column(1)
if isnull(lst29) then
   frmsubform.rowsource = "qsAllRecs"
else
   frmsubform.rowsource = "qsFilteredRecs"    'this uses sql based on text45
endif

[Camp]

I get an error message stating that access can't find the field 'frmsubform' referred to in your expression.

[Camp]

I tried to just re-query the subform and i get the same can't find frmsubform error.

Forms![frminput]![frmSubform].Form.Requery

it acts like it cant find the subform, i have checked the spelling, it is correct.

[ssanfu]

Let me see if I understand correctly.

The master form is named "frmInput" ; the record source is table "tblContractor" (or a query based on tblContractor)
The sub-form is named "frmSubform"; the record source is table "tblDetails" (or a query based on tblDetails)

The main form and sub form are linked by the "Link Master field" and the "Link Child Fields".
So the "Link Master field" is the PK field of the table "tblContractor" and the "Link Child Fields" is the FK field in table "tblDetails".

When you select a record in the main form, the sub form changes records. Good so far?



Now you want to use a list box to filter the main form.
You have

X = Me.List29.Column(0)
        Me.FilterOn = True
        Me.Filter = "tblContractor.ID =" & X

You cannot set the filter on, then set the filter expression.

The code behind the list box should be:

Private Sub List29_Click()

   Me.Filter = "ID =" & Me.List29
   Me.FilterOn = True

End Sub

That is all it takes...... IF I understood correctly.

(You should take the time to rename the objects. "List29" is a poor name as is "ID" for a field name)

You should also have a button to remove the main form filter.

Private Sub btnRemoveFilter_Click()
    Me.Filter = ""   '<<-- not really needed, but I like to keep it clean
    Me.FilterOn = False
End Sub

[ranman256]

You must use the name of YOUR subForm. I don't know it.

[ssanfu]

If the main form and the sub form are linked by the "Link Master/Child" fields, filtering the main form will cause the sub form to show the related records. No filtering has to be done to the sub form.

[Camp]

Let me see if I understand correctly.

The master form is named "frmInput" ; the record source is table "tblContractor" (or a query based on tblContractor)
The sub-form is named "frmSubform"; the record source is table "tblDetails" (or a query based on tblDetails)

The main form and sub form are linked by the "Link Master field" and the "Link Child Fields".
So the "Link Master field" is the PK field of the table "tblContractor" and the "Link Child Fields" is the FK field in table "tblDetails".

When you select a record in the main form, the sub form changes records. Good so far?



Now you want to use a list box to filter the main form.
You have

X = Me.List29.Column(0)
        Me.FilterOn = True
        Me.Filter = "tblContractor.ID =" & X

You cannot set the filter on, then set the filter expression.

The code behind the list box should be:

Private Sub List29_Click()

   Me.Filter = "ID =" & Me.List29
   Me.FilterOn = True

End Sub

That is all it takes...... IF I understood correctly.

(You should take the time to rename the objects. "List29" is a poor name as is "ID" for a field name)

You should also have a button to remove the main form filter.

Private Sub btnRemoveFilter_Click()
    Me.Filter = ""   '<<-- not really needed, but I like to keep it clean
    Me.FilterOn = False
End Sub

The main form and sub form are linked by the "Link Master field" and the "Link Child Fields".
So the "Link Master field" is the PK field of the table "tblContractor" and the "Link Child Fields" is the FK field in table "tblDetails".

What do PK and FK mean ?

When you select a record in the main form, the sub form changes records. Good so far?

Yes, this works, but the subform shows all the sub records, dozens for every record on the main form. What i am trying to do is get the Subfrom to go to the record I am interested in via clicking on the list box on the main form.

I want to filter the sub form, based on the filtered main form via the list box which i use to filter the main form and the corresponding subform



My code below:

Private Sub List29_Click()

' Trip ID
Text45 = Me.List29.Column(1)

X = Me.List29.Column(0)

Me.Filter = "tblContractor.ID =" & X
Me.FilterOn = True

y = Me.List29.Column(1)

'frmSubform.Requery
Me!frmSubform.Form.Filter = "[tbldetails].[idtravel] =" & y
Me!frmSubform.Form.FilterOn = True


Call HomeVisit


End Sub



X and Y return the desired values from the list box.

the main form filters properly based upon the X value obtained from the list box

But, when the code reaches the following line:

Me!frmSubform.Form.Filter = "[tbldetails].[idtravel] =" & y

I get the following error

Run-time error '2465'

Microsoft Access can't find the field 'frmsubform' referred to in your expression.

thank you for helping

[orange]

Camp,

Watch these videos to get a handle on concepts
Relational concepts
Data modelling