Best way using vba to record value of table field, then later use it to GoTo that same record

[Jerry Call]

In a form whose record source is a table, one field is ItemName. Suppose one record has ItemName = "Root Beer". Within a vba module I need to temporarily move away from that record, but I would like to return to it via vba. So if my
code results in the record pointer ending up on the first record of the field, or some other place, how can I return focus to "Root Beer"? There seems to be an amazing number of ways to do this, but they all seem very complicated; I'm stumped at finding one that seems to fit my case (which it would seem to me must come up all the time). It would be easy enough to store the "Root Beer" value of ItemName into a variable. But now I need a command or commands in my code block that
says "Return focus to "Root Beer". Not up one record, down one, go to top, ...heaven knows what else! Very simple: Put the record pointer on "Root Beer" in the ItemName field.

[CJ_London]

you need to be clearer about the situation you are describing - what does this mean?

I need to temporarily move away from that record

To where? another record on the form? another form? another app?

But now I need a command or commands in my code block that says "Return focus to "Root Beer"

perhaps use the form filter property?

me.filter="itemName='" & varItemName & "'"
me.filteron=true

or perhaps you need to use find? https://docs.microsoft.com/en-us/off...cmd.findrecord

or perhaps recordsetclone?

maybe using a button to initiate the action?

what have you tried? what doesn't work? why not?

not enough information to provide a more focused response

[moke123]

I'm losing a little in the translation.
Record pointer generally refers to a recordset.

I need to temporarily move away from that record, but I would like to return to it via vba.
record pointer on "Root Beer" in the ItemName field.
Return focus to "Root Beer"

This can be read a few different ways.

One is moving to another record and back and the other is moving to another control and back.

To move to another record and back you would need to store the primary key of that record either in a global variable or a tempvar. You could then use a findfirst to return back to the original record.
It also depends where you are doing this. If you are using a global Variable it needs to remain in scope, a tempvar doesn't.

If your moving between controls that again youd have to either store the control name and use YourContolName.SetFocus to return or perhaps Screen.PreviousControl.SetFocus.

Find first method might look like

Dim rs as DAO.Recordset
set rs = me.RecordsetClone

rs.FindFirst "YourPrimaryKeyFieldName = " & YourStoredPrimaryKey

if not rs.NoMatch then
     Me.BookMark = rs.BookMark
end if

[Jerry Call]

Here's my full code for the Update event of the FullUnitTally. I've noted the problem areas. Everything works fine in terms of loading the correct ItemName into variable RecoverRec (= what you call YourStoredPrimaryKey).
But the FindFirst line at the bottom crashes. I need more explanation of the syntax.

Code for the FullUnitTally AfterUpdate event.zip

[CJ_London]

you can just copy paste the code and then surround with the code tags (highlight the code and click the # button

Private Sub FullUnitTally_AfterUpdate()
 
    Dim rs As DAO.Recordset
   
    Dim FullUnitTally As Control
    Dim NoInFull As Control
    Dim CumTally As Control
       
    Dim lFullUnitTally As Long
    Dim lNoInFull As Long
    Dim lStartCumTally As Long
    Dim lCumBuffer As Long
    Dim lCumUndoBuffer As Long
    Dim lEndCumTally As Long
    Dim RecoverRec As String      ‘ I added this
           
    Set rs = Me.RecordsetClone      ‘ I added this
           
    DoCmd.GoToControl "FullUnitTally"
       
    lFullUnitTally = Screen.ActiveControl.Value
   
    DoCmd.GoToControl "NoInFull"
    lNoInFull = Screen.ActiveControl.Value
   
    lCumBuffer = lFullUnitTally * lNoInFull
   
'   Store the value of lCumBuffer into variable lCumUndoBuffer.
'   Then update the textbox CollectUndo with that value, so we can
'   can access it if the user clicks cmdUndo.
 
    lCumUndoBuffer = lCumBuffer
    Forms!frmSodaTallyNorthMachines!CollectUndo.Value = lCumUndoBuffer
       
    DoCmd.GoToControl "CumTally"
    lStartCumTally = Screen.ActiveControl.Value
    lEndCumTally = lStartCumTally + lCumBuffer
   
    Screen.ActiveControl.Value = lEndCumTally
   
    DoCmd.GoToControl "FullUnitTally"
    Screen.ActiveControl.Value = 0
   
    DoCmd.GoToControl "ItemName"    ‘ Here’s where I get value of RecoverRec
    RecoverRec = ActiveControl.Value
   
    DoCmd.GoToControl "FullUnitTally"
    DoCmd.Requery        ‘ This will end up on the wrong record
   
    rs.FindFirst "ItemName" & RecoverRec     ‘ this one crashes
         
End Sub

However you haven't answered most of the questions asked so we are not much further forward

My only comment regarding findfirst is you are missing an =, itemName implies text and RecoverRec is dimmed as a string so your code should be

rs.FindFirst "ItemName='" & RecoverRec & "'"

[moke123]

Much of what you've coded appears unneccessary. (if i'm following it correctly.)

DoCmd.GoToControl "NoInFull"
    lNoInFull = Screen.ActiveControl.Value

can simply be written

lNoInFull = Me.NoInFull

note that .value is the default property so it is usually not needed.

Assuming this is within the forms module you can make use of the me keyword.

lCumUndoBuffer = lCumBuffer
    Forms!frmSodaTallyNorthMachines!CollectUndo.Value = lCumUndoBuffer

can be written

    Me.CollectUndo  = lCumBuffer

rs.FindFirst "ItemName = '" & RecoverRec & "'"

wont move to the record on the form without setting the bookmark property.

[moke123]

The shorter version of your code may look like this, but i'm not really sure of the point of it.

    Set rs = Me.RecordsetClone      ‘ I added this
    
    lFullUnitTally = Me. FullUnitTally
       
    lNoInFull = Me.NoInFull
   
    lCumBuffer = lFullUnitTally * lNoInFull
   
    Me.CollectUndo = lCumBuffer 
        
    lStartCumTally = Me.CumTally
 
    lEndCumTally = lStartCumTally + lCumBuffer
   
    Me.CumTally = lEndCumTally
   
    Me.FullUnitTally = 0
       
    RecoverRec = me.ItemName
   
‘I’m not sure what this is for
    DoCmd.GoToControl "FullUnitTally"
    DoCmd.Requery        ‘ This will end up on the wrong record
   
    rs.FindFirst "ItemName = ‘" & RecoverRec & “’”    
          If not rs.NoMatch then
              Me.BookMark = rs.BookMark
          End if

this part confuses me. Whats it for?

    DoCmd.GoToControl "FullUnitTally"
    DoCmd.Requery        ‘ This will end up on the wrong record

[Jerry Call]

You're right about the two lines. In my confusion I forgot that I had already in my code gone back to the FullUnitTally control.
And the Requery, it turns out, was what was messing up the record in the first place because it always ends up back on
the first record.

The real pure gold in your response, which I shall carefully file away in my notes, is the correct syntax for the FindFirst command,
along with the practice of using a recordset clone in cases like this. All those single and double quotes look just like the ones
I used in some SQL statements. I guess the same principle applies in both cases, but it hadn't occurred to me.

I think we're in the clear on this now. Thanks for your patient help for a novice.