Access synchronizing sub forms 1

[AC9999]

I have created a form that contains 2 sub forms based on 2 different tables. The problem is trying to filter the records on the sub forms - including syncronizing them together, based on the information in the main form.

The catch:
- the main form is not bound to a table
- the sub forms must filter based on information entered in an unbound text box on the main form. The trigger is a find button.

I have tried setting the subform filter with
Forms!Mainform!subform!filter property - access does not recogize the object.
I have also tried using the on current property in the subform but since the main form is not bound to a record the on current property event does not trigger.

I am open to suggestions.

 

[blakezx1]

I've dealt with similar situations in the past. I am not sure of the most elegant method for doing this in code.

Here is one idea that might be the easiest though.

What your attempting will take some creativity but its very possible.

Lets start with the main form. You have a text box. You want to have them type a value and click "find" and go find matching records in your subforms.
Consider binding your mainform to a table that contains the values the user might be trying to "find". Here's the catch... You don't display them on the main form. You just use them to drive the "Child"/"Master" properties of the subforms. You drag the "Master" fields onto the main form but just make them all invisible.

Now the only thing left is add code to set up the "find" in the main form. This is accomplished using a roundabout method that microsoft somehow thought up. (I wish they would have thought of something less troublesome). You have to make a recordset clone of you recordset, locate the record you want to be current in the main form, set a bookmark in the clone and then set the bookmark of your actual recordset equal to the clones bookmark (thus locating your record). As soon as access moves to that record (behind the scenes) the subforms update to show any records matching the "Master"/"child" relationships between the master records and child records of the 2 subforms.

Believe me this is the only way to Jump to a specific record in a database table, or query result. If necessary I can post the code for the "find" button on monday. Let me know by posting a message if needed.
Blake

 

[AC9999]

Thanks to Blakezx1.
The idea of creating a recordset in the main form and seting the visible property to false was one that already occurred to me. I my mind that was sloppy because I was dealing with 2 different tables and the value in the find text box may be in one, both or none of the tables.

Your response has confirmed to me that the solution may be difficult or cause the application to work inefficiently. I am familiar with the recordset close property as well but that would not work unless the main form was bound to a table.

Thanks for the help.