Requery and Find Record

[Berny]

Is there a way to requery and return to the next or same record?

I'm trying to save the value of the primary key field into a variable, run
the requery, and use the variable to return to the record using the find
record

But I get the following Error:
"A macro set to one of the current field's properties failed because of
an error in a FindRecord action argument"

Can anyone tell me what I doing wrong?

Private Function VolCboLstUpd()
On Error GoTo VolCboLstUpd_Err

Dim strX As String

DoCmd.GoToRecord , , acNext

strX = Me!idsVolRecID (this is the Primary key field)

DoCmd.Requery

DoCmd.FindRecord strX,,,,,acAll ( I'm sure the problem is I'm using
this incorrectly)

VolCboLstUpd_Exit:
Exit Function

VolCboLstUpd_Err:
MsgBox Error$
Resume VolCboLstUpd_Exit

End Function

Any help is greatly appreciated

 

[Sandra Daigle]

Hi Berny,

First off why are you moving to the next record before you save the id? No
problem if this is what you want but it seems confusing.

The Findrecord method searches for a record with the matching value in the
current control. So for this to work, you have to setfocus to the control
that holds the value you are seeking. However, the docmd methods are really
not ideal for this purpose. A better way is as follows:

Dim strX As String
DoCmd.GoToRecord , , acNext
'If you move to the next record first, what should happen if there is no
next record?
strX = Me!idsVolRecID (this is the Primary key field)
with me.recordsetclone
.findfirst "idsVolRecId=""" & strx & """"
if not .nomatch then
me.bookmark=.bookmark
endif
end with

The business with the extra quotes above is required when doing string
comparisons.

 

[Berny]

Hi Sandra,

First, Thank you for your help

The reason I'm trying to move to the next record is that I'm trying to do a
requery to remove records that no longer meet my criteria.

I'm moving to the next record in the event the current record no longer
meets my criteria.

Is there a better way of doing this?

I also noticed your use of Bookmark, I'm new at this and was unaware of this
property.

I guess I'll do some reading on this and I'll try your suggestion below.

Thank you again

Hi Berny,

First off why are you moving to the next record before you save the id? No
problem if this is what you want but it seems confusing.

The Findrecord method searches for a record with the matching value in the
current control. So for this to work, you have to setfocus to the control
that holds the value you are seeking. However, the docmd methods are
really not ideal for this purpose. A better way is as follows:

Dim strX As String
DoCmd.GoToRecord , , acNext
'If you move to the next record first, what should happen if there is no
next record?
strX = Me!idsVolRecID (this is the Primary key field)
with me.recordsetclone
.findfirst "idsVolRecId=""" & strx & """"
if not .nomatch then
me.bookmark=.bookmark
endif
end with

The business with the extra quotes above is required when doing string
comparisons.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Is there a way to requery and return to the next or same record?

I'm trying to save the value of the primary key field into a
variable, run the requery, and use the variable to return to the
record using the find record

But I get the following Error:
"A macro set to one of the current field's properties failed
because of an error in a FindRecord action argument"

Can anyone tell me what I doing wrong?

Private Function VolCboLstUpd()
On Error GoTo VolCboLstUpd_Err

Dim strX As String

DoCmd.GoToRecord , , acNext

strX = Me!idsVolRecID (this is the Primary key field)

DoCmd.Requery

DoCmd.FindRecord strX,,,,,acAll ( I'm sure the problem is I'm
using this incorrectly)

VolCboLstUpd_Exit:
Exit Function

VolCboLstUpd_Err:
MsgBox Error$
Resume VolCboLstUpd_Exit

End Function

Any help is greatly appreciated