Requery/bookmark

[Skunk]

I open Invoice form from a Receipts form, updating Invoice form. Both are
open.

OnClose Invoice form I return to the Receipts form record, after Receipts
form requery.

My code is:
Forms![2frmPRReceipts].Requery
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "forms![2frmPRReceipts].form![txtInvNum] = " & Me![txtInvNum]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

It is with high confidence that my issue is the FindFirst line.

I want to point to the Receipts form displaying the record that has the same
InvNum as the Invoice form I am leaving.

Will someone show me the correct syntax? Or what I have in error?

Thanks in advance.

 

[Marshall Barton]

I open Invoice form from a Receipts form, updating Invoice form. Both are
open.

OnClose Invoice form I return to the Receipts form record, after Receipts
form requery.

My code is:
Forms![2frmPRReceipts].Requery
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "forms![2frmPRReceipts].form![txtInvNum] = " & Me![txtInvNum]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

It's better to use RecordsetClone instead of
Recordset.Clone, even if they are logically the equivalent.
The former is a built in recordset, while the latter must be
constructed each time it is used.

Assuming the txtInvNum text box control is bound to the
InvNum field, try using:

Set rs = Me.RecordsetClone
rs.FindFirst "InvNum = " & Me![txtInvNum]

The reason being that FindFirst searches the record source
records for a matching value in a **field**. It is not
aware of the controls in the form.

 

[Skunk]

Thanks for responding, Marshall.

I didnt' think that would help (it didn't) as I am returning to prior form
still open.

Recall that user is on Receipts form, opens Invoice form, updates, then
closes Invoice, returning - after requery - to the Receipts form. The first
record is visible now that is has been requeried, not the record I left.

I want to return to the Receipts record user was on when Invoice form was
open. The code is in my OnClose event for the Invoice form.

This code surely works, I have it several places, but I am on only one form
then. This is two forms.

Can you help me?

I open Invoice form from a Receipts form, updating Invoice form. Both are
open.

OnClose Invoice form I return to the Receipts form record, after Receipts
form requery.

My code is:
Forms![2frmPRReceipts].Requery
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "forms![2frmPRReceipts].form![txtInvNum] = " & Me![txtInvNum]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

It's better to use RecordsetClone instead of
Recordset.Clone, even if they are logically the equivalent.
The former is a built in recordset, while the latter must be
constructed each time it is used.

Assuming the txtInvNum text box control is bound to the
InvNum field, try using:

Set rs = Me.RecordsetClone
rs.FindFirst "InvNum = " & Me![txtInvNum]

The reason being that FindFirst searches the record source
records for a matching value in a **field**. It is not
aware of the controls in the form.

 

[Marshall Barton]

Maybe the record you were on in the Receipts form is not
identified by the InvNum field??

OTOH, on closer inspection, I see that you used wizard
generated code so you can't tell if the find failed to
locate the desired record. I also (finally) noticed that
you are using find on the wrong form's recordset.

Let's try changing the code to use the receipts form

With Forms![2frmPRReceipts]
.Requery
Set rs = .RecordsetClone
rs.FindFirst "InvNum = " & Me![txtInvNum]
If Not rs.NoMatch Then .Bookmark = rs.Bookmark
End With
--
Marsh
MVP [MS Access]

I didnt' think that would help (it didn't) as I am returning to prior form
still open.

Recall that user is on Receipts form, opens Invoice form, updates, then
closes Invoice, returning - after requery - to the Receipts form. The first
record is visible now that is has been requeried, not the record I left.

I want to return to the Receipts record user was on when Invoice form was
open. The code is in my OnClose event for the Invoice form.

This code surely works, I have it several places, but I am on only one form
then. This is two forms.

I open Invoice form from a Receipts form, updating Invoice form. Both are
open.

OnClose Invoice form I return to the Receipts form record, after Receipts
form requery.

My code is:
Forms![2frmPRReceipts].Requery
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "forms![2frmPRReceipts].form![txtInvNum] = " & Me![txtInvNum]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

It's better to use RecordsetClone instead of
Recordset.Clone, even if they are logically the equivalent.
The former is a built in recordset, while the latter must be
constructed each time it is used.

Assuming the txtInvNum text box control is bound to the
InvNum field, try using:

Set rs = Me.RecordsetClone
rs.FindFirst "InvNum = " & Me![txtInvNum]

The reason being that FindFirst searches the record source
records for a matching value in a **field**. It is not
aware of the controls in the form.

 

[Skunk]

Thanks so much, Marshall. It works! I see (clearly) now what my issue was.

Thanks again.

Maybe the record you were on in the Receipts form is not
identified by the InvNum field??

OTOH, on closer inspection, I see that you used wizard
generated code so you can't tell if the find failed to
locate the desired record. I also (finally) noticed that
you are using find on the wrong form's recordset.

Let's try changing the code to use the receipts form

With Forms![2frmPRReceipts]
.Requery
Set rs = .RecordsetClone
rs.FindFirst "InvNum = " & Me![txtInvNum]
If Not rs.NoMatch Then .Bookmark = rs.Bookmark
End With
--
Marsh
MVP [MS Access]

I didnt' think that would help (it didn't) as I am returning to prior form
still open.

Recall that user is on Receipts form, opens Invoice form, updates, then
closes Invoice, returning - after requery - to the Receipts form. The first
record is visible now that is has been requeried, not the record I left.

I want to return to the Receipts record user was on when Invoice form was
open. The code is in my OnClose event for the Invoice form.

This code surely works, I have it several places, but I am on only one form
then. This is two forms.

Skunk wrote:

I open Invoice form from a Receipts form, updating Invoice form. Both are
open.

OnClose Invoice form I return to the Receipts form record, after Receipts
form requery.

My code is:
Forms![2frmPRReceipts].Requery
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "forms![2frmPRReceipts].form![txtInvNum] = " & Me![txtInvNum]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


It's better to use RecordsetClone instead of
Recordset.Clone, even if they are logically the equivalent.
The former is a built in recordset, while the latter must be
constructed each time it is used.

Assuming the txtInvNum text box control is bound to the
InvNum field, try using:

Set rs = Me.RecordsetClone
rs.FindFirst "InvNum = " & Me![txtInvNum]

The reason being that FindFirst searches the record source
records for a matching value in a **field**. It is not
aware of the controls in the form.