Edit Record on a form using a popup form

[Neruda]

Hi
I need to edit a specific record on a main form using a popup form. this is the code:

Private Sub resetThisForm(strAction As String)
    On Error GoTo ErrorHandler
 
    Dim rst As DAO.Recordset
 
    Select Case strAction
        Case "Swap"
            Set rst = Forms!frmMain.Recordset
 
            With rst
                .Bookmark = Forms!frmMain.Bookmark
                .Edit
                    !fkLocationID = Me.txtLocationPK
                .Update
            End With
 
        Case "Add"
            '...
    End Select
 
    Me.Visible = False
 
 
exitSub:
    Set rst = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error No: " & Err.Number & vbNewLine _
         & "Error Details: " & Err.Description & vbNewLine _
         & "Error in Sub: frmMain_TableAdd\resetThisForm"
    Resume exitSub
 
End Sub

I was wondering though if declaring and setting the recordset is needed in this case. Shouldn't I avoid opening another recordset and just do the following, thanks

Private Sub dresetThisForm(strAction As String)
    On Error GoTo ErrorHandler
 
    Select Case strAction
        Case "Swap"
 
            With Forms!frmMain.Recordset            
                .Edit
                    !fkLocationID = Me.txtLocationPK
                .Update
            End With
 
        Case "Add"
            '...
    End Select
 
    Me.Visible = False
 
exitSub:
    Exit Sub
ErrorHandler:
    MsgBox "Error No: " & Err.Number & vbNewLine _
         & "Error Details: " & Err.Description & vbNewLine _
         & "Error in Sub: frmMain_TableAdd\resetThisForm"
    Resume exitSub
 
End Sub

[NeoPa]

Hi Neruda.

I assume this code is within the Form [frmMain]?

It's very difficult to help because your explanation of what the code is doing doesn't have any relation to the code itself. Unfortunately, the code (Both sets) is so full of things that don't make sense that it's hard to read it & even guess what it is you're trying to do.

So, How can I help? Maybe I can state a few facts that will give you some perspective on what it is you're actually doing/thinking about.

1. Forms have a Recordset object as well as a RecordsetClone object.
2. The Recordset object is what is used by the Form to manage the data you see on the Form. The current record will already match the data you see on the Form.
3. The RecordsetClone object is configured the same way as the Recordset - having the same fundamental set of data to work with - but it typically stays at the first record until moved.
4. Assuming the Form you're working within is actually named [frmMain] as expected, then Forms!frmMain is just a long way of saying Me. That only works for code within the [frmMain] Module though of course.
5. Using .Edit / .Update directly on a Form's Recordset is almost never necessary & it is prone to causing blocking (Where the data isn't available in one place because it's in use & locked in the other). You should never do it.
6. There may be a good reason why you might want to try that, but I very much doubt it. This just looks like very confused coding. If you want to set the value in the record then do so via the Control on the Form that's linked to that Field.
7. In this case it looks even worse than that as it looks like you're actually trying to set the value from itself.

I hope some of this helps & if you have any questions feel free to ask.

[Neruda]

I assume this code is within the Form [frmMain]?

No,
the code above is in a popup form. It edits a field (fkLocationID) on the main form.
I have decided to edit that record using the main form's recordset instead of a (UPDATE) sql statement. I dont want to directly edit the record on the main form. I have to use a pop up for that. Since there are many ways to update a record in forms I was wondering if the second block of code (above) makes more sense because the first opens yet another recordset .


thanks

[NeoPa]

Neruda:
I have decided to edit that record using the main form's recordset instead of a (UPDATE) sql statement.

Good luck with that.

Neruda:
I dont want to directly edit the record on the main form. I have to use a pop up for that.

That's a problem. If the main Form allows updates then I suspect you'll find you sometimes get clashes if you try that. If it doesn't then the Recordset object won't allow it either.

Neruda:
Since there are many ways to update a record in forms I was wondering if the second block of code (above) makes more sense because the first opens yet another recordset.

Well, the first block doesn't open another Recordset. It simply uses an extra (wasted) pointer to the same Recordset. I see little sense in either block if I'm honest but if the second makes more sense then it's only because it doesn't create an extra Recordset object which is merely a pointer to that of the main Form and not materially different in any way.

I can warn you away from such an approach - as I have done twice now - but only you can decide how you wish to continue.