Form Filter

[smikkelsen]

I am trying to filter a form based on a different form that is not open.



Example, I have a continuous form with names. A different form holds notes for these people, and are connected by ID.

Here is some sample code I am trying to get to work. I have something in the syntax wrong, please help!

 
 
strFilter = "forms![main_tracking_notes]![comment] like '*" & strSearch & "*' WHERE forms![main_tracking_notes]![Main_Tracking_ID] = " & Me.[ID]
Me.Form.Filter = strFilter
Me.Form.FilterOn = True

[dsmacs]

What form is not open? How is the ID selected?

[smikkelsen]

Sorry, i didn't explain well.

I have form [pr_Main] and form [main_tracking_notes]
pr_main has the button that fires the event to filter
main_tracking_notes is not open, yet I would like the filter to search for something on that form and open the matching ID on the pr_main form.

Let me give an example:
[pr_main] has fields <merchant> with value of "bob" and <ID> with value "500"
[main_tracking_notes] has fields <pr_main_ID> with value of "500" and <comment> with value of "nice guy"

My filter should look up id 500 from [pr_main] and return comments from [main_tracking_notes] where the id's match.

Hope that makes more sense. Thanks for the reply!

[dsmacs]

Further info required, 1. do you want a form to open with the comments when the user selects a ID or value or 2. do you want to open a details page which would includes a comments sub form?

1. sub merchant_onclick()

docmd.openform "Main_tracking_notes", "[ID] = " & me.ID, acNormal, , , acFormEdit, acDialog

end sub

2. sub merchant_onclick()

docmd.openform "Pr_Main_Detailed", "[ID] = " & me.ID, acNormal, , , acFormEdit, acDialog

end sub

Hope this helps
Cheers
Darren

[smikkelsen]

I actually do have a cmd button that opens a details page in which I can type comments for each person already. What I am trying to do is include those comments into the filter from the main form.

The main form has a search box. You can search by name, email, order number, phone number, etc. But as of now you cannot search for a string that was typed in one of the notes on the details page. I would like to be able to include that in the filtered results, even though I cannot see the details on the main form.

Thanks

[dsmacs]

Okay, attached may give you idea on how to accomplish;-