Requery a Form and Return Certain Record

[Bee]

Hi,

I want to requery a form from time to time; however, when that happens I want to return the record that was displayed at the time before the requerying. Now when I requery, it goes back to the first record.

Any suggestions will be very much appreciated.
Thanks,
B

 

[Pat Hartman]

You need to save the primary key of the record you want to move back to - NOT it's bookmark since bookmarks are recreated when the RecordSource is requeried. After the requery, use the find method to find the record matching the ID you saved.

 

[Bee]

I am not sure I understand what you mean Pat Hartman. I have looked under properties of the PK on form and table desing levels; however, I could not see any option about bookmarks.

I have also done a search in the help section of access and all I got was help about creating hyperlinks. I wonder if you can elaborate please?

 

[RoyVidar]

air code

dim MyPk as long
MyPk = Me!txtMyPk.Value [COLOR="SeaGreen"]' save the PK from form control[/COLOR]
Me.Requery
With Me.RecordsetClone
    .FindFirst "ThePkField = " & MyPk [COLOR="SeaGreen"]' use saved PK to find record[/COLOR]
    If Not .Nomatch Then Me.Bookmark = .Bookmark [COLOR="SeaGreen"]' assign/move to if found[/COLOR]
End With

 

[Pat Hartman]

RoyVidar has given you sample code to do what I suggested. But based on your question, it sounds like you don't know what a primary key is. A primary key is a field (or combination of up to 10 fields) that are used to uniquely identify a record. If your table doesn't have a primary key, there is no way you will be able to reposition to a specific record because you will not have any way to identify it.

 

[Bee]

I do understand what a PK is! What I meant was I did not understand how to bookmark a record in my recordset. I was not sure whether it is done through properties of the PK field or through code.

RoyVidar generously supplied an example which I still got to try, but thanks to both of you.

 

[Bee]

Answer to this Urgently needed please!!!

I have tried your code RoyVidar; however, it did not work. I got two different erros.

Error 1: The Microsoft Jet database engine does not recognize 'ThePkField' as a valid field name or expression. Error 1 was generated when I used the following code:

Private Sub cmdRefresh_Click()
Dim MyPK As Long
MyPK = Me!HouseID.Value ' save the PK from form control
Me.Requery
With Me.RecordsetClone
    .FindFirst "ThePkField = " & MyPK ' use saved PK to find record
    If Not .NoMatch Then Me.Bookmark = .Bookmark ' assign/move to if found
End With
End Sub

Error 2: Invalid argument. This error was generated when I used the following code:

Private Sub cmdRefresh_Click()
Dim MyPK As String
MyPK = Me!HouseID.Value ' save the PK from form control
Me.Requery
With Me.RecordsetClone
    .FindFirst MyPK ' use saved PK to find record
    If Not .NoMatch Then Me.Bookmark = .Bookmark ' assign/move to if found
End With
End Sub

Do I need to have my recordset of type snapshot or dynaset to be able to get this to work? Can you please advise? Can you give a working example please such as DB attachment?

 

[CraigDolphin]

Erm...have you tried actually substituting the name of your pk field where the code says 'ThePKField'?

 

[Bee]

Yes I have done that in my second code listing (Error2).

 

[Rabbie]

Yes I have done that in my second code listing (Error2).

Bee, You need to have a conditional statement like

.FindFirst "yourPKfieldname = " & MyPK

where yourPKfieldname is the name of your PK field and MyPK is the actual value in that field for the record you want to return to

Hope this helps.

 

[Bee]

Hi Rabbie,

Thanks for your help. Unfortunately, I did not know that the part that said "yourPKfieldname = " was a condition. Because it was wrapped in quotes, I thought that can be any text!!! I shall try it later.

Thanks,
B

 

[Bee]

This code has worked well with records that already exist. But if I add a new record, it does not work. I think it does not work because at the time I am pressing the button that has this code, the table has not generated a PK for my new record yet.
Any suggestions on how to save the new record please?

 

[CraigDolphin]

You could try saving the current record prior to running the rest of the code.

Docmd.Runcommand acCmdSaveRecord

 

[boblarson]

This code has worked well with records that already exist. But if I add a new record, it does not work. I think it does not work because at the time I am pressing the button that has this code, the table has not generated a PK for my new record yet.
Any suggestions on how to save the new record please?

Are you using a bound form? If so, a quick

DoCmd.RunCommand acCmdSaveRecord

should do the trick

or If Me.Dirty Then Me.Dirty = False

could work too.

 

[Bee]

I tried to save the record before running the rest of the code using DoCmd.RunCommand acCmdSaveRecord. But it did not help. It is a form and subform scenario where the main form is linked to the subform through the same PK. When I add a new record, the record gets added to the subform and it does not appear in the main form until the main form is requeried. So the part of the code that says:

Me!MyPK.value should reference the subform. But it appears to reference the main one because when I run the code, it opens the record that was displayed just before I added the new record. I want it to return the new record after the requery. Any suggestions please?

 

[Bee]

I have actually managed to get it to partially work now. The error was being generated because I was using Me!MyPk.value and that was somehow being assigned the main form's PK's value where it should have been assigned the subform's PK's value. So I just used the subform name instead of Me and that solved it.

subformsName.MyPK.value

But, if the user presses the add new button to add a new record, then he/she changed their mind and run this code. The DB will return 'Invalid use of Null' because the subform's PK is empty at that point.

This is the only case where it return an error now. The rest works fine.

 

[CraigDolphin]

Can you test first for a null before allowing the code to proceed....

If not isnull(subformsName.MyPK.value) then

'code here

End if

 

[Bee]

That's solved it. It just needed IsNull() and a couple of If Statments.

Private Sub cmdRefresh_Click()
Dim MyPK As Long
DoCmd.RunCommand acCmdSaveRecord ' save record
If IsNull(qryHouse2!HouseID.Value) And Not IsNull(Me.HouseID.Value) Then
    MyPK = Me!HouseID.Value ' save main form PK from form control
ElseIf Not IsNull(qryHouse2!HouseID.Value) Then
    MyPK = qryHouse2!HouseID.Value
Else:
    Me.Requery
End If
Me.Requery
With Me.RecordsetClone
    .FindFirst "HouseID = " & MyPK ' use saved PK to find record
    If Not .NoMatch Then Me.Bookmark = .Bookmark ' assign/move to if found
End With
End Sub