Thanks Graham, that works great just over thinking what needs to be done.
Rod
---In MS_Access_Professionals@yahoogroups.com, <graham@...> wrote :
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: desertscroller@cox.net
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar