Synchronizing Two Forms with Filtered Records

[MartyBoy]

Hi Again
I have a form that passes the filter created by a "Filter by Form" to a second form, where additional fields from the same table are displayed for the records selected by the filter.

I can't synchronize the two forms, so that the second form opens to the same record as the first form. Users switch back and forward between the two forms, but the second form always goes back to the first record of the recordset created by the filter, irrespective of the actual record that the user was on when the command button is pressed to go to the second form.

I can't specify a common field between the two forms because that affects the filter and only shows a single record.

Any ideas out there?

 

[TheAceMan1]

How are ya MartyBoy . . . . .

[blue][purple]I can't specify a common field between the two forms because that affects the filter[/purple] and only shows a single record.
[/blue]

[blue] You don't have to specify a common field in a filter to have it included in the [purple]RecordSource[/purple] of the forms your trying to synchronize.

Remember a Filter is just Criteria used against a recordset to select a specific subset of that recordset. [purple]No matter how many fields it contains![/purple][/blue]

Also be aware . . . . . just because a field is included in the [purple]RecordSource[/purple] of a form, [purple]doesn't mean it has to be on the form in order to be used[/purple]. It just has to be included in the recordset.

As an an example, none of my [blue]Primary Keys[/blue] are included on form, but there used for synchronizing and everything else I need them for. As a matter of practice [blue]Primary Keys[/blue] are always included in the [purple]RecordSource[/purple] of forms. I really can't see working without them.

So . . . go ahead . . . .include that common field in your [purple]RecordSources[/purple] and do your synchronizing . . . .

See Ya! . . . . . .

 

[MartyBoy]

Hi there TheAceMan

Sorry, I suspected my question was going to cause a misunderstanding. My fault for putting that last sentence in my question and not explaining the problem better.

I know a filter is a collection of criteria for selecting subsets of a recordset. I will try to clarify my problem, cause I know you will have the answer....

Form1 allows users to Query by Form to select a subset of the recordset. I then pass the filter created by the user's Query by Form (which usually contains multiple criteria) to a second form (Form2).

The OnOpen subroutine for Form2 contains code to the effect:

Me.Filter = Form1.Filter

Form2 contains the rest of the fields from the same Table that are displayed on Form1. The filter may create a recordset of 30 - 50 records to be viewed on the second form.

This works fine in selecting the records to be displayed on Form2.

The problem is, if the user moves away from the first record on Form1 after having created the filter, and then opens Form2, Form2 will show the fields for the FIRST record in the recordset created by the filter, which is not the same record that the user was on in Form1. This gives me grief because the user may make changes to the fields on Form2 thinking (but not checking) that they are editing the same record that they were looking at on Form1.

The user can arrive at the same record they were looking at on Form1 using the record navigation tool to flick through the filtered recordset on Form2, but because they are working the system at high speed and usually talking to a client on the phone at the same time, they often edit the wrong record.

I need to find a way to synchronize the two forms so that whichever record is displayed on Form1 is the same record that is displayed by default on Form2. In other words, I need to be able to synchronize two forms that each use a filtered recordset. Or, in other words, how do I bookmark a record displayed on Form1 that becomes a bookmark to open a specific record within a filtered recordset on Form2?

I'm sorry for the length of this question. Also, I can't use a Tab Control to put all the data on one form cause the users are very specific about field layout, and ease and speed of use. Thanks for reading and trying to understand this.