Can't select the record after requery

[123Archie]

This is giving me a headache. I cant find an answer after a day mining the internet.

I have a main form with two combo's and a subform (as as datasheetform) and another subform (also as datasheet), so a 3-level form. With buttons on the main form I transfer data from combo to combo and these events trigger a requery of both comboboxes. Now of course, as a result of the requery the record pointer of the lowest subform moves to the first record in the datasheet. I expected this and I wanted to go back to the original position by storing the ID of a (keyindex) field of the pointer before the requery and do a findfirst method on the cloned recordset after the requery of the comboboxes followed by a bookmark method. Part of this last code looks like this:

Public Sub setrecordpointer()

ActionIDpointer = Me!frmlProfilesubform!frmActionssubform.Form!.ActionID
Set rst3 = Me!frmlProfilesubform!frmActionssubform.Form!.RecordsetClone
With rst3
.FindFirst "ActionID=" & Str(ActionIDpointer)
If .NoMatch Then
MsgBox "no record found"
End If
End With

Me!frmlProfilesubform!frmActionssubform.Form!.Bookmark = rst3.Bookmark

End Sub

For some reason this doesnt work at all. The pointer is still at the first record. I've inserted some msgboxes in the code to read the status of the variables and then the code works. Of course I dont want these msgboxes in the end product. Does anyone have a solution??

 

[PHV]

Set the value of ActionIDpointer BEFORE the requery !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[123Archie]

The requery is for the comboboxes on the main form. I've watched the ActionIDpointer value during code running and it takes the proper value that I selected. Anyhow I've done what you said and it still doesnt work. I have the feeling that the requery procedure of the comboboxes is not completely finished when the setrecordpointer procedure starts. If I slow down the code by including messageboxes to read out variables I can see that the pointer is set at the proper position but that after the last messagebox it just goes to to first record of the subsubform. I can also see that the requery has done it's job before the setrecordpointer procedure starts as the combobox content has changed. I'm confused....

 

[123Archie]

I've done some testing and I'm getting more confused. I've included the following 2 lines of code between End if and End with:

Public Sub setrecordpointer()

ActionIDpointer = Me!frmlProfilesubform!frmActionssubform.Form!.ActionID
Set rst3 = Me!frmlProfilesubform!frmActionssubform.Form!.RecordsetClone
With rst3
.FindFirst "ActionID=" & Str(ActionIDpointer)
If .NoMatch Then
MsgBox "no record found"
End If

Me!frmlProfilesubform!frmActionssubform.Form!.Bookmark = rst3.Bookmark
MsgBox Me!frmlProfilesubform!frmActionssubform.Form!.ActionID

End With

Me!frmlProfilesubform!frmActionssubform.Form!.Bookmark = rst3.Bookmark

End Sub

The first bookmark runs without error, the next msgbox gives me the correct value. The second bookmark, which is identical code as the first bookmark! gives me the 3021 error: no current record???????? How is it possible that two lines before it works okay en now it fails????