Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Filter a subform from a selected value in another subform

Status
Not open for further replies.

Bigwiggz

Technical User
Apr 8, 2013
5
US
Hello Everyone,
I am new at using VBA but I am trying to filter a subform based on the selected value of another subform. Currently, I have it in a button "btnTestFilterPersonell" but I am noticing 2 things in particular.


Private Sub btnTestFilterPersonell_Click()
Set db = CurrentDb
Set rs = db.OpenRecordset("qryfrmLinkBuildingPersonell")

Dim SelectedValue As Long

SelectedValue = Form_frmLink.subfrmPersonellLink.Form!ID.Value

Me.Filter = "[tblPersonell.ID]=" & SelectedValue
Me.FilterOn = True
Debug.Print Me.FilterOn
Me.Requery

Debug.Print rs.Fields("tblPersonell.ID")
Debug.Print SelectedValue
Debug.Print SelectedValue&; "=" & rs.Fields(3)
Debug.Print Me.Filter

Set rs = Nothing

End Sub



1) The "Me.FilterOn" status does not change to true. It stays false even after I change the FilterOn status.
2) The Filter "[tblPersonell.ID]=" & SelectedValue is not being applied.

Below are the results that I am getting from the debug.print window


Debug.Print Me.FilterOn--False
Debug.Print rs.Fields("tblPersonell.ID")--135
Debug.Print SelectedValue--93
Debug.Print SelectedValue&; "=" & rs.Fields(3)--93 =135 (Column 3 is tblPersonell.ID)
Debug.Print Me.Filter--[tblPersonell.ID]=93

I am sure it is a simple fix but I am completely stumped and will gladly accept any help you offer.

Thanks,
 
Looking at it, the only thing that I notice is that the Requery is not necessary... I am wondering if it is somehow clearing the filteron property.

Another approach would be to change the recordsource of the form to a SQL statement with appropriate criteria then requery it.

Sometimes when things don't work, I just go with a different methodology rather than fight it.
 
Thanks, lameid. I will try the .Recordsource method. Thanks for your help!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top