Endless form always jumps to the first recordset

[waldemar]

I guess this is an SQL problem, but maybe it sounds familiar to somebody.

Im am using an endless form imbedded in a single record edit form to display all recordsets like a list. Both forms show the same recordsource, but are not linked, since this would limit the list to the current recordset; I want to see the whole recordsource there. The idea is, when the user clicks on an entry in the list the main form moves to that recordset - very simple.

I use multiple forms this way, e.g. for Parents and Children.

In some forms, the behavior is normal, you click on an entry, the other form is updated and the list entry is the the currentrecord.

The problem: In some of these forms, after clicking on an entry in the list form, the cursor jumps to very first record in the list. Even when you are some screens lower in the list, it will move to the uppermost entry, scrolling all the way up again.

Does this sound familiar to anybody? How can I stay in the currentrecordset where the user clicked? There is no code whatsoever implemented like Requery etc...

Thanks in advance!
waldemar

 

[Steve101]

waldemar,

Something seems not correct the way you are explaining things above. If the forms are not linked AND their is no code, then they should behave totally independently, even though they have the same recordsource.

I suspect that there might be some code behind the Current event of the subform, and that this is the culprit. Suggest you go into the database window, select and view the Subform form in design view, select the View Code option, and confirm that their is/is not code. If there is (and it sounds like it), then the problem will be in here.

If you confirm the above, and publish any code behind the subform I will review and correct it (should only take 3 or 4 lines of code to synchronise the forms as you require.

Cheers,
Steve

 

[waldemar]

There is really no code behind *any* form events. Just some onclick events of the list entries, that move the main form to the right recordset:

Private Sub strCity_Click()
DoCmd.GoToRecord acDataForm, Me.Parent.Name, acGoTo, Me.CurrentRecord
End Sub

Regards
waldemar

 

[Steve101]

OK, so we have now established that there IS some code; I assume that this is the ONLY code associated with either subforms; please advise otherwise.

I have tested this code, and it seems to operate OK, when I use a simple table as the recordsource to each of the two forms. I am however suspicious of this technique should you try to Add, or Delete a record. In this case the record number positions of the two recordsources would go out of synch. You do need to consider this situation, by applying a Requery to the subform when an add or delete event is detected.

This still doesnt explain the problem you're having. Are the records being presented to the two forms using identical queries?

Does the main form have ANY code of its own??

Is either form Ordered or Filtered ??

What is/are the Primary key field(s) of the underlying table.

If the above questions do not yield any clues, we'll move to "Plan B"; ie. I'll show you how to synchronise the two forms another way. For this, you'll need to provide me with the Primary key field(s) as specified above. I also need to know if it (or they) are text and/or numeric.

Cheers,
Steve

 

[waldemar]

Hello Steve,

Yes, it is clear that I have to resynchronize at some points via Requery. Since you say you are suspicous about this technique, what alternative would you suggest?

[Are the records being presented to the two forms using identical queries?]:
Yes, they both use identical queries.

[Does the main form have ANY code of its own??]:
Yes, the main form has some minor after_update-events for some text fields; there is definitly no code that is called in the main form upon the DoCmd.GoToRecord from the sub form.

[Is either form Ordered or Filtered ??]:
Yes, they are both ordered and filtered (the same), but the problem also appears when I disable both.

[What is/are the Primary key field(s) of the underlying table]:
This is good question, since it is a query behind the scene. How do I set a primary key in a query? All the tables behind the query have very simple IDs in only one field. But even if I compress the query down to one table (so theres only one ID and no whatsoever key-problem) the problem occurs... I dont get this: It looks like a boolean option "Requery whenever you get your currentRecordset read"....?!?!?

What is plan B ?

Regards
waldemar

 

[Steve101]

"Plan B" as follows:

(a) Add the following code to the OnCurrent event of the subform:

Private Sub Form_Current()
Dim FMain As Form: Set FMain = Me.Parent
Dim FSub As Form: Set FSub = Me

Dim RS As Recordset: Set RS = FSub.RecordsetClone
RS.MoveFirst
RS.findfirst "f1 = '" & f1 & "'"
If Not RS.NoMatch Then
FMain.Bookmark = RS.Bookmark
End If
RS.Close
End Sub

(b) Modify the line above which reads:

RS.findfirst "f1 = '" & f1 & "'"

Change both "f1" entries above to refer to the field on both forms which refers to the unique key identifying field. If these entries are numeric (as opposed to text), then remove the two single quote characters as well.


(c) This code, rather than using absolute record positions between the two forms, logically searches for the first matching key entry. Note that Ive tied it to the OnCurrent event; you can move it to the strCity_Click() event if you like.

Give this a go, and see if it works OK,
Cheers,
Steve

 

[waldemar]

Hello Steve,

nice plan; I am not at all familial with bookmarks. Unfortunately I get the error 3159 (no valid bookmark) when trying to set FMain.Bookmark to anything... any form properties I have to keep in mind?

Regards
waldemar

 

[Steve101]

Waldemar,

Send me a copy of the program @ steve@lewycomputing.com.au and I'll have a look at it. Thats got to be the quickest way to resolve this.

Cheers,
Steve

 

[waldemar]

Thanks Steve,
I will try to compress the database a bit; right now this would be 6 mb to send.

Regards
waldemar