Move to Record in subform

[RobGMiller]

Access 2003,

When a form containing a subform is opened it must set the content of a
control on the parent form to link the SubForm\Subreport control Parent and
child fields, then it sets the Subform Filter and OrderBy. Then it must find
a record and move to that record. The parent form is unbound and the subform
is bound to a table.

All this code is in the parent form onOpen event and it all works well. The
record is found and you can see the pointer on the record selectors moving to
the correct record as the form opens but then it moves back to the first
record.

My guess is that the form is somehow refreshed after the pointer is moved.

In the parentform.OnOpen event the code is performed in the following
summarized manner.

Me!ParentChildLinkControlName = "xxx"
Me.SubformContainer.Form.Filter = "ColumnX = 'Data'"
Me.SubformContainer.Form.OrderBy = "fieldname asc"
Set rst = frm.Recordset
rst.FindFirst "Reference = '" & Data & "'"
If Not rst.NoMatch Then frm.Bookmark = rst.Bookmark
Set rst = Nothing

This is pretty straight forward.

I've tried it in the OnLoad and OnOpen of the parent form as well as the
subForm.

I've tried it as part of the code that opens the parent form using a button
in a different form.

I've tried putting the filtering and sorting in the form and ran the code to
find the record outside the form after it opens.

If there is no filtering the Bookmark section does move to the correct
record and stay there. This does not work if the Bookmark code is outside the
form but it does work from the OnOpen event of the parent form.

Can someone tell me if the method is sound in this case. If not, where can
the filtering stuff be put so that everything is stable by the time the
bookmark is set.

Thanks in advance for your help.

 

[Allen Browne]

That sounds like a really convoluted way to get a result. I'm not surprised
it has the timing problems you describe.

How about taking an alternative approach.
Omit the LinkMasterFields/LinkChildFields.
Omit the Filter an OrderBy properties.

Instead, just set the subform's RecordSource property so that it is limited
to the value from the LinkMasterFields text box, and to the values specified
in the Filter, and sorted according to what you had in OrderBy.

You may even be able to set the RecordSource to just one record, and so
avoid the FindFirst as well. That would be really efficient, but if it's not
suitable, you should now be able to FindFirst in the subform's Load event.

In fact, you may be able to leave the main form out of it too, and just use
the Form Header/Footer of the subform for whatever you need there.

 

[RobGMiller]

Thanks for pointing out the alternative method Allen,

I normally filter and sort subform records using the subform RecordSource
and have moved the record cursor accurately using that method. In this case,
the dynamic filtering is complicated that I thought I'd try to use multiple
Parent-Child Link fields, Filter and OrderBy to save work in programmatically
constructing very complex RecordsSource SQL strings. Its too bad, it all
works well except for the Find-Bookmark issue.

However, I recognize your expertize and if you dont think trying to figure
out the exact details of the timing to make it work this way is worth it,
then it's probably more effort than sticking to what works.

Thanks for your input.

 

[Allen Browne]

I think the issue you posted about is one of timing.

The events should work like this: the subform's Open should fire first, then
the main form's Open event. At this point, you are running your code, and
finding the desired record. After Form_Open, the main form figures out its
content, examines the content of the LinkMasterFields, matches the data to
the fields named in LinkChildFields, and then loads the subform's data to
match.

That last step is the timing problem, i.e. this runs after your code in
Form_Open, and hence it flips back to the first record after the load based
on LinkChildFields. That's why I think it needs an alternative approach.

Hope that makes sense of the suggestion.

 

[RobGMiller]

To clarify: The main form has a button that launches the Separate form.

Your last comment suggests that a call to move to a record on the Separate
form located after DoCmd.OpenForm on the button that opens the form from the
main form should work. As indicated earlier, it does not.

I put the call to move to the correct record on a different test button on
the main form. I use the original button to open the Separate form and the
test button to move to the record. That works.

In response to the success of that test, I placed a 1 second timer before
the call to move to the record in the OnClick event of the Button that opens
the Separate form.

Now the form opens, waits once second, the cursor moves to the correct
record and then bounces back to the first record.

So I put a stop in the Button that launches the Separate form before the
code that moves the cursor to the record. Stepping throught he code works.

Increasing the time to 2 seconds produced the same bounce back result.

I thought that it might only work if a different form has the focus but if
the Test button is used to move the cursor to the correct record and then the
focus is switched to the Separate form, the cursor doesn't move back to the
first record weather I click on the Separate form or its subform.

This comes so tantalizingly close to working that I hate to give it up.

Can you shed any light on this seemingly irrational behavior.

 

[Allen Browne]

Somethign else must be happenign here, Rob.
Not sure what to suggest.