Requery subform and retain current record

[Mus' via AccessMonster.com]

Hi

I can't seem to fathom this one.

I have a form (frmClients) and in a tabbed page their is a subform
(fsubDetails) 1 client can have many details.

fsubDetails shows, amongst other things, the current value and date of
valuation. To update these values the user open a popfrm and enter the latest
valuation and date (added to tbl of previous valuations). On closing (using
OnClose event) the popfrm I requery the subform to update the valuation
details, however, it always go back to the first record on fsubDetails.

How can I do so, whilst 'retaining' focus on the current record?

I have tried capturing the fsub PK in a variable, but can't seem to setfocus
on to the subfrm and findfirst ID that matches.I have tried suggestions from
previous threads but have yet to achieve the results I desire.

Often when trying to setfocus to the subfrm it says that it cannot find the
'field' of that name(?!)

Code so far (in popfrmValues):

Private Sub Form_Close()
On Error GoTo Form_Close_Err

Dim lngID As Long

lngID = [Forms]![frmClients]![qryDetails subform].[Form]!DetailID.Value

'Debug.Print lngID ' Just to check

[Forms]![frmClients]![qryDetails subform].[Form].Requery
' To update value details

'Need something else here?

Me.Recordset.FindFirst "DetailID=" & lngID

lngID = Empty



Form_Close_Exit:
Exit Sub

Form_Close_Err:
MsgBox Error$
Resume Form_Close_Exit

P.S. Using Acc2000 format.

 

[Arvin Meyer [MVP]]

You need to capture the current record's ID (or PK value) to a variable.
Then do your requery. Now using a bookmark of the recordsetclone, move to
the variable.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Mus' via AccessMonster.com]

Hi Arvin

I've already captured the subfrms current record ID to a variable (lngID in
above code), and carried out the requery.

Now using a bookmark of the recordsetclone, move to

It is this next part that I really need help on, thanks.

 

[Arvin Meyer [MVP]]

Post the code you have so far, and I'll see what I can do to fill it in for
you.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Mus' via AccessMonster.com]

Thanks, code so far is as in the initial post above.

 

[Arvin Meyer [MVP]]

Thanks, code so far is as in the initial post above.

Sorry, I didn't read far enough. Instead of:

Me.Recordset.FindFirst "DetailID=" & lngID

Try:

Me.[qryDetails subform].Form.Recordsetclone.FindFirst "DetailID=" & lngID

You were trying to find the form's DetailID instead of the subform's.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Mus' via AccessMonster.com]

Hi Arvin, thanks for your continued help on this one.

I now get an error message "can't find field '|' referred to in your
expression"

 

[Arvin Meyer [MVP]]

It would be easier if the '|' in question was named. I think that Access
can't find the variable lngID, or that it thinks that it is a field. If you
haven't done so already, add Option Explicit to your Declarations section,
to make sure that you haven't mis-spelled a variable name.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Mus' via AccessMonster.com]

I have done a debug.print lngID to check that it has captured the variable
and I get the correct result.

I guess, effectively this variable acts as a bookmark. It is getting the
FindFirst to marry up to this that fails.

I have tried:

Me.RecordsetClone.FindFirst "[DetailID]=" & lngID
Me.Bookmark = Me.RecordsetClone.Bookmark

This does not work but does not through up an error either. Have I missed
something in the setting of the recordsetclone?

Also, as an alternative, I set focus to the DetailID and tried:
DoCmd.FindRecord lngID, acEntire, True, acSearchAll, False, acCurrent, True

but then I get...

" A macro set to one of the field's current properties failed because of an
error in a FindRecord action arguement."

 

[Arvin Meyer [MVP]]

This should work:

Me.RecordsetClone.FindFirst "[DetailID]=" & lngID

If Me.RecordsetClone.NoMatch Then
MsgBox lngID & " not found."
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

I can't understand why not unless it isn't finding it, or there is a type
mismatch. Turn off any error handling that has Resume Next in it, and step
through your code to get the values line by line.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

I have done a debug.print lngID to check that it has captured the variable
and I get the correct result.

I guess, effectively this variable acts as a bookmark. It is getting the
FindFirst to marry up to this that fails.

I have tried:

Me.RecordsetClone.FindFirst "[DetailID]=" & lngID
Me.Bookmark = Me.RecordsetClone.Bookmark

This does not work but does not through up an error either. Have I missed
something in the setting of the recordsetclone?

Also, as an alternative, I set focus to the DetailID and tried:
DoCmd.FindRecord lngID, acEntire, True, acSearchAll, False, acCurrent, True

but then I get...

" A macro set to one of the field's current properties failed because of an
error in a FindRecord action arguement."

It would be easier if the '|' in question was named. I think that Access
can't find the variable lngID, or that it thinks that it is a field. If you
haven't done so already, add Option Explicit to your Declarations section,
to make sure that you haven't mis-spelled a variable name.

 

[Mus' via AccessMonster.com]

EUREKA!!! I new it would be something silly. The problem was in the 'Me.'
(and perhaps me myself)

As previously mentioned, the trigger was in a a separate pop up form, and
although I tried setting the focus to the intended subform the 'me.' was
still picking up on the current pop up form.

I have now reproduced the code fully naming each object and it works. All's
well that ends well.

Many thanks for all of your support, and apologies for the confusion. It's
been a painful baptism using recordsets and bookmarks for the first time.

)