Rabu, 09 Maret 2016

[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: desertscroller@cox.net
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)

.

__,_._,___

Tidak ada komentar:

Posting Komentar