Run two sets of code on 'Before Update' (1 Viewer)

B

bigalpha

Registered User.
Local time
Today, 14:10
Joined
Jun 22, 2012
Messages
415
I have a set of code to keep an audit trail that calls a module:

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditTrail(Me, CurrentCYIDPK)
End Sub

Calls

Code: 
Option Compare Database

Const cDQ As String = """"

Sub AuditTrail(frm As Form, recordid As Control)
  'Track changes to data.
  'recordid identifies the pk field's corresponding
  'control in frm, in order to id record.
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName As String
  Dim strSQL As String
  On Error GoTo ErrHandler
  'Get changed values.
  For Each ctl In frm.Controls
    With ctl
    'Avoid labels and other controls with Value property.
    If .ControlType = acTextBox Then
      If .Value <> .OldValue Then
        varBefore = .OldValue
        varAfter = .Value
        strControlName = .Name
        'Build INSERT INTO statement.
        strSQL = "INSERT INTO " _
           & "Audit (EditDate, User, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & cDQ & Environ("username") & cDQ & ", " _
           & cDQ & recordid.Value & cDQ & ", " _
           & cDQ & frm.RecordSource & cDQ & ", " _
           & cDQ & .Name & cDQ & ", " _
           & cDQ & varBefore & cDQ & ", " _
           & cDQ & varAfter & cDQ & ")"
        'View evaluated statement in Immediate window.
        Debug.Print strSQL
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
      End If
    End If
    End With
  Next
  Set ctl = Nothing
  Exit Sub

ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub

I also am trying to keep track of modified information via a macro. I converted the macro to VBA and was given this:
Code: 
'------------------------------------------------------------
' LastModified
'
'------------------------------------------------------------
Function LastModified()
On Error GoTo LastModified_Err

    With CodeContextObject
        .ModifiedDate = Date
        .ModifiedTime = Time()
    End With


LastModified_Exit:
    Exit Function

LastModified_Err:
    MsgBox Error$
    Resume LastModified_Exit

End Function

I'm a VB beginner and haven't figured out how to run both of these events on Form_BeforeUpdate.

I'd appreciate any help!
 
Cronk

Cronk

Registered User.
Local time
Tomorrow, 07:10
Joined
Jul 4, 2013
Messages
2,774
You don't have to do anything to trigger the event, except update the form.

Access provides a number of "hooks" into events such as when the mouse is clicked on a button or a control is updated.

In the case of when you are about to update the record on a bound form by pressing a save button or moving to the next record, the Form_BeforeUpdate will be triggered and execute any code that is there in the sub routine.

Your LastModified function looks like it is updating a global variable but does nothing in terms of your audit trail in the other code you have posted.
 
missinglinq

missinglinq

AWF VIP
Local time
Today, 17:10
Joined
Jun 20, 2003
Messages
6,423
bigalpha said:
...haven't figured out how to run both of these events on Form_BeforeUpdate...
Click to expand...
Maybe I'm not understanding your question, but you can place as many lines of code/commands as you like in the Form_BeforeUpdate event.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
  Command1
  Command2
  Command3
End Sub
Linq ;0)>
 
B

bigalpha

Registered User.
Local time
Today, 14:10
Joined
Jun 22, 2012
Messages
415
missinglinq said:
Maybe I'm not understanding your question, but you can place as many lines of code/commands as you like in the Form_BeforeUpdate event.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
  Command1
  Command2
  Command3
End Sub
Linq ;0)>
Click to expand...

I tried to tack the code that tracks modified time/date right after the code for the audit trail and it didn't work. Maybe I messed it up when I combined them. I'll retry it and see what happens.
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 22:10
Joined
Sep 12, 2006
Messages
15,666
are you getting a problem with the audits?

i think in the audit trail code, you are adding cdq ("") around every field - however, there issues. if the value being audited is a number or a date, then cdq is not the correct way to wrap the value. I think in that case, the docmd statement will just fail without you being aware.

better is to use (and error trap) currentdb.execute, I think, (and to get the field masking correct.)

note also that if you are in a locale that does not use US format dates, you ALSO need to manage the format of dates more carefully, or some dates will be stored incorrectly.
 
B

bigalpha

Registered User.
Local time
Today, 14:10
Joined
Jun 22, 2012
Messages
415
gemma-the-husky said:
are you getting a problem with the audits?

i think in the audit trail code, you are adding cdq ("") around every field - however, there issues. if the value being audited is a number or a date, then cdq is not the correct way to wrap the value. I think in that case, the docmd statement will just fail without you being aware.

better is to use (and error trap) currentdb.execute, I think, (and to get the field masking correct.)

note also that if you are in a locale that does not use US format dates, you ALSO need to manage the format of dates more carefully, or some dates will be stored incorrectly.
Click to expand...

I'm not having any problems with my audits, but I haven't fully implemented it yet. I got the code from HERE. I still have to add in the portion of the code to account for combo boxes and such. Also, I'm in the US and use US format dates.

I don't know the significance of the problem with the use of cDQ in the code...
 
B

bigalpha

Registered User.
Local time
Today, 14:10
Joined
Jun 22, 2012
Messages
415
missinglinq said:
Maybe I'm not understanding your question, but you can place as many lines of code/commands as you like in the Form_BeforeUpdate event.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
  Command1
  Command2
  Command3
End Sub
Linq ;0)>
Click to expand...

Ok, I still can't figure this out. I think that the converted macro to VBA is not working. This is the code I have for updating the 'Last Modified' data:
Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
Function LastModified()
On Error GoTo LastModified_Err

    With CodeContextObject
        .ModifiedDate = Date
        .ModifiedTime = Time()
    End With


LastModified_Exit:
    Exit Function

LastModified_Err:
    MsgBox Error$
    Resume LastModified_Exit

End Function
End Sub

And I get a "Compile Error: Expected End Sub"
 
B

bigalpha

Registered User.
Local time
Today, 14:10
Joined
Jun 22, 2012
Messages
415
So I figured it out. I wasn't calling both modules; I was calling one module and trying to use the code for the second. *DUH*

So I got it all patched together and working! How do I reference the name of a form using SQL?
This is the current code, and I see that 'frm.recordsource' stores the record source of whatever form the data was changed in. How do I just reference the name of the form, too?
Code: 
strSQL = "INSERT INTO " _
           & "Audit (EditDate, User, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & cDQ & Environ("username") & cDQ & ", " _
           & cDQ & recordid.Value & cDQ & ", " _
           & cDQ & frm.RecordSource & cDQ & ", " _
           & cDQ & .Name & cDQ & ", " _
           & cDQ & varBefore & cDQ & ", " _
           & cDQ & varAfter & cDQ & ")"
 
Cronk

Cronk

Registered User.
Local time
Tomorrow, 07:10
Joined
Jul 4, 2013
Messages
2,774
If the code is in the before update event or any other module in the form, use
Me.Name

Otherwise
Forms!YourFormName.Name
 
B

bigalpha

Registered User.
Local time
Today, 14:10
Joined
Jun 22, 2012
Messages
415
I got "Invalid Use of Me Keyword" error, so I tried form.name and got a "Object required - 424" error.

This is what I used:
Code: 
        strSQL = "INSERT INTO " _
           & "Audit (EditDate, User, RecordID, SourceTable, " _
           & " SourceField, SourceForm, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & cDQ & Environ("username") & cDQ & ", " _
           & cDQ & recordid.Value & cDQ & ", " _
           & cDQ & frm.RecordSource & cDQ & ", " _
           & cDQ & .Name & cDQ & ", " _
           & cDQ & Me.Name & cDQ & ", " _
           & cDQ & varBefore & cDQ & ", " _
           & cDQ & varAfter & cDQ & ")"
 
Cronk

Cronk

Registered User.
Local time
Tomorrow, 07:10
Joined
Jul 4, 2013
Messages
2,774
You have made a reference to the form ie frm, so presumably you should use frm.name

ie instead of
& cDQ & Me.Name & cDQ & ", " _

you would use
& cDQ & frm.Name & cDQ & ", " _
 
B

bigalpha

Registered User.
Local time
Today, 14:10
Joined
Jun 22, 2012
Messages
415
Cronk said:
You have made a reference to the form ie frm, so presumably you should use frm.name

ie instead of
& cDQ & Me.Name & cDQ & ", " _

you would use
& cDQ & frm.Name & cDQ & ", " _
Click to expand...

Crap, I thought I had done that. I think I did 'form.name'. Thanks for your help!
 
You must log in or register to reply here.

Similar threads

K
Error 13 | Audit Log
Replies
4
Views
673
The_Doc_Man
The_Doc_Man
Kayleigh
  • Solved
Solved Audit trail error
2
Replies
25
Views
2,870
gcarpenter
gcarpenter
B
Solved AuditChanges not capturing ID for NEW additions
Replies
7
Views
613
Pat Hartman
P
F
Runtime Error 3001 HELP I Am loosing my mind
Replies
14
Views
710
ebs17
E
C
Next record code moves to the next record in two sets of records in two textboxes when it should be only be moving down on one
Replies
3
Views
311
ChazRab
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom