468,167 Members | 1,968 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,167 developers. It's quick & easy.

Edit Record on a form using a popup form

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub resetThisForm(strAction As String)
  2.     On Error GoTo ErrorHandler
  3.  
  4.     Dim rst As DAO.Recordset
  5.  
  6.     Select Case strAction
  7.         Case "Swap"
  8.             Set rst = Forms!frmMain.Recordset
  9.  
  10.             With rst
  11.                 .Bookmark = Forms!frmMain.Bookmark
  12.                 .Edit
  13.                     !fkLocationID = Me.txtLocationPK
  14.                 .Update
  15.             End With
  16.  
  17.         Case "Add"
  18.             '...
  19.     End Select
  20.  
  21.     Me.Visible = False
  22.  
  23.  
  24. exitSub:
  25.     Set rst = Nothing
  26.     Exit Sub
  27. ErrorHandler:
  28.     MsgBox "Error No: " & Err.Number & vbNewLine _
  29.          & "Error Details: " & Err.Description & vbNewLine _
  30.          & "Error in Sub: frmMain_TableAdd\resetThisForm"
  31.     Resume exitSub
  32.  
  33. 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

Expand|Select|Wrap|Line Numbers
  1. Private Sub dresetThisForm(strAction As String)
  2.     On Error GoTo ErrorHandler
  3.  
  4.     Select Case strAction
  5.         Case "Swap"
  6.  
  7.             With Forms!frmMain.Recordset            
  8.                 .Edit
  9.                     !fkLocationID = Me.txtLocationPK
  10.                 .Update
  11.             End With
  12.  
  13.         Case "Add"
  14.             '...
  15.     End Select
  16.  
  17.     Me.Visible = False
  18.  
  19. exitSub:
  20.     Exit Sub
  21. ErrorHandler:
  22.     MsgBox "Error No: " & Err.Number & vbNewLine _
  23.          & "Error Details: " & Err.Description & vbNewLine _
  24.          & "Error in Sub: frmMain_TableAdd\resetThisForm"
  25.     Resume exitSub
  26.  
  27. End Sub
Mar 12 '21 #1

✓ answered by 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.

3 1184
NeoPa
32,042 Expert Mod 16PB
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.
Mar 12 '21 #2
Neruda
72 64KB
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
Mar 12 '21 #3
NeoPa
32,042 Expert Mod 16PB
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.
Mar 13 '21 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by vishal | last post: by
3 posts views Thread by scripteaze | last post: by
1 post views Thread by gcdp | last post: by
reply views Thread by kamranasdasdas | last post: by
reply views Thread by gcreed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.