Rabu, 09 Maret 2016

RE: [MS_AccessPros] Help updating a record

 

Hi Rod

Your AfterUpdate procedure is way more complex than it needs to be.  Instead of updating the field in the table, just update the bound textbox.  The problem is that changing one of the other bound controls begins an edit on the record but does not complete it.  If you then change that record in the background then when the bound form tries to save the record, Access doesn't know which version to keep and which to discard.

Try this:

Private Sub Tbox_NewNotes_AfterUpdate()
   Dim strNewNotes As String

   On Error GoTo AfterUpdate_Error
      If MsgBox("Do you want to post new notes to Project Notes?", _
                vbYesNo + vbQuestion, "Post Notes") = vbYes Then
         strNewNotes = Now() & "        " & GetUserName() & vbCrLf & Tbox_NewNotes
         Else
         If Len(TboxNotes & "") <> 0 Then
            strNewNotes = strNewNotes & vbCrLf & conLine & vbCrLf & TboxNotes
         End If
     End If
      TboxNotes = strNewNotes
AfterUpdate_Exit:
   Exit Sub
AfterUpdate_Error:
   Call LogError(Err.Number, Err.Description, "frmProjects-New Notes AfterUpdate()")
   Resume AfterUpdate_Exit
End Sub

You can also delete these lines from Form_BeforeUpdate:

            If IsNull(TboxNotes) Or TboxNotes = "" Then
            Else
               TboxNotes = Now() & "        " & GetUserName() & vbCrLf _
                  & TboxNotes
            End If

Best wishes,

Graham Mandeno [Access MVP 1996-2016]

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, 10 March 2016 16:24
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Help updating a record

 

 

Need help resolving updating a record containing the following fields:
   4 comb-boxes bound
   4 text boxes bound
   1 checkbox bound
   1 text box (memo) bound
   1 text box (meno) unbound
The bound controls are associated to a table.
The unbound field is used to concatenate text to existing data.  An
afterupdate event is used to do the concatenation and save the new
memo to the field bound in the other memo field.
The issue is if the user changes one of the first three control types
and the unbound field.  The afterupdate events fire in the proper order
but the  bound memo field displayes "Deleted!"
When the record beforeupdate event fires an error message is displayed:

   Write error
   The record has been changed by another user since you started..
   
 The unbound control afterupdate event is:

 Private Sub Tbox_NewNotes_AfterUpdate()
   Dim db As DAO.Database
   Dim strNewNotes As String

   On Error GoTo AfterUpdate_Error
      Set db = CurrentDb
      If MsgBox("Do you want to post new notes to Project Notes?", _
                vbYesNo + vbQuestion, "Post Notes") = vbNo Then
         Tbox_NewNotes = ""
      Else
         If IsNull(TboxNotes) Or TboxNotes = "" Then
             strNewNotes = Now() & "        " & GetUserName() & vbCrLf _
                  & Tbox_NewNotes
         Else
             strNewNotes = Now() & "        " & GetUserName() & " " & vbCrLf _
                  & Tbox_NewNotes & vbCrLf & conLine & vbCrLf & TboxNotes
         End If

         db.Execute "UPDATE tblProjects SET strProjectNotes = '" & _
             strNewNotes & "' Where [lngTPTID] = " & tboxTPTID & ";"
         
         ' Must get Notes focus, and unlock to make the record dirty
         TboxNotes.SetFocus
         TboxNotes.Locked = False
         Me.TboxNotes.Requery
         ' After updating the record relock the Notes control
         TboxNotes.Locked = True
         Tbox_NewNotes = ""
      End If
AfterUpdate_Exit:
   db.Close
   Exit Sub
AfterUpdate_Error:
   Call LogError(Err.Number, Err.Description, "frmProjects-New Notes AfterUpdate()")
   Resume AfterUpdate_Exit
End Sub

The record beforeupdate event is :

Private Sub Form_BeforeUpdate(Cancel As Integer)
   'If the form data has changed a message is shown asking if
   'the changes should be saved. If the answer is no then
   'the changes are undone
   On Error GoTo BeforeUpdate_Error
      If MsgBox("Do you want to save this new record? (Projects)", _
                vbYesNo + vbQuestion, "Save Record") = vbNo Then
         Me.Undo
      Else
         If Me.NewRecord Then
            Me.lngTPTID = GetTPTID()
            If IsNull(TboxNotes) Or TboxNotes = "" Then
            Else
               TboxNotes = Now() & "        " & GetUserName() & vbCrLf _
                  & TboxNotes
            End If
            Call AuditChanges(Me, "lngProjectID", "NEW")
         Else
            Call AuditChanges(Me, "lngProjectID", "EDIT")
         End If
      End If
BeforeUpdate_Exit:
   Exit Sub

BeforeUpdate_Error:
   Call LogError(Err.Number, Err.Description, "frmProjects-BeforeUpdate()")
   Resume BeforeUpdate_Exit

End Sub


Any help would be greatly appreciated.
Using Access 2010 on Windows 10 and 7

Rod

 

__._,_.___

Posted by: "Graham Mandeno" <graham@mandeno.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)

.

__,_._,___

Tidak ada komentar:

Poskan Komentar