Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-17-2019, 12:06 AM   #1
Cark
Newly Registered User
 
Join Date: Dec 2016
Posts: 62
Thanks: 45
Thanked 1 Time in 1 Post
Cark is on a distinguished road
Option Button Values causing issues with Audit Trail code

I have managed to get the code (I am using the "An Alternative Audit Trail Routine for Recording Additions, Edits and Deletes" section) supplied from https://www.fontstuff.com/access/acctut21.htm working to create an audit trail entered into tblAuditTrail, however my form includes a couple of Option Buttons that are causing me an error.

The error is "You entered an expression that has no value."

I take this is because the code looks at the .Value portion and as the Option Button is part of an Option Group, the Option Button does not have a Value, but does have a .OptionValue. Is my thinking correct?

My Option Group only has 2 Option Buttons, so I guess this might open up the methods in which we can address it by amending the code? Although I will be looking to increase this to 3 or 4 as my database develops.

Thanks.

The Code:

Code:
Sub AuditChanges(IDField As String, UserAction As String)
    On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    Select Case UserAction
        Case "EDIT"
            For Each ctl In Screen.ActiveForm.Controls
                If ctl.Tag = "Audit" Then
                    If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                        With rst
                            .AddNew
                            ![DateTime] = datTimeCheck
                            ![UserName] = strUserID
                            ![FormName] = Screen.ActiveForm.Name
                            ![Action] = UserAction
                            ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                            ![FieldName] = ctl.ControlSource
                            ![OldValue] = ctl.OldValue
                            ![NewValue] = ctl.Value
                            .Update
                        End With
                    End If
                End If
            Next ctl
        Case Else
            With rst
                .AddNew
                ![DateTime] = datTimeCheck
                ![UserName] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![Action] = UserAction
                ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
                .Update
            End With
    End Select
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

Cark is offline   Reply With Quote
Old 05-17-2019, 03:23 AM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,653
Thanks: 0
Thanked 405 Times in 402 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Option Button Values causing issues with Audit Trail code

Which is the OptionGroup control? Which line throws the error?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 05-17-2019, 03:31 AM   #3
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,945
Thanks: 452
Thanked 881 Times in 837 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Option Button Values causing issues with Audit Trail code

Just an observation, I don't think that audit code you have there will work on subforms , might be worth checking out if you intend to add subforms to your database.

__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 05-17-2019 at 06:12 PM.
Uncle Gizmo is offline   Reply With Quote
Old 05-17-2019, 04:12 AM   #4
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,945
Thanks: 452
Thanked 881 Times in 837 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Option Button Values causing issues with Audit Trail code

Quote:
Originally Posted by Cark View Post
The error is "You entered an expression that has no value."

I take this is because the code looks at the .Value portion and as the Option Button is part of an Option Group, the Option Button does not have a Value, but does have a .OptionValue. Is my thinking correct?
Yes you're on the right track, the solution is to remove "Audit" from the Tag of your "Option Buttons" and only have "Audit" as the Tag on the "Option Group". You don't need the "Audit" Tag for each Option Button, just the "Option Group" Frame itself.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 05-17-2019 at 04:18 AM. Reason: Added [B]"Audit"[/B]
Uncle Gizmo is offline   Reply With Quote
Old 05-17-2019, 04:23 AM   #5
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,945
Thanks: 452
Thanked 881 Times in 837 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Option Button Values causing issues with Audit Trail code

Also your option group frame must be linked to a field in a table. Otherwise it won't show any changes because in effect, no change takes place!
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 05-17-2019, 04:36 AM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,812
Thanks: 70
Thanked 1,953 Times in 1,901 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Option Button Values causing issues with Audit Trail code

Cark,

You may want to review this thread regarding some issues with the Audit Trail code from Martin Green (fontstuff).

Good luck with your project.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 05-22-2019, 11:31 PM   #7
Cark
Newly Registered User
 
Join Date: Dec 2016
Posts: 62
Thanks: 45
Thanked 1 Time in 1 Post
Cark is on a distinguished road
Re: Option Button Values causing issues with Audit Trail code

Thanks all. Yes the next thing I need to tweak is the Sub-Forms aspect of it.

Thanks for the reference link and hopefully I should be able to tweak it without any further issues.

I had completely forgotten that Option Groups are treated (believe it or not) as a Group...


Cark is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Audit Trail Code Tor_Fey Modules & VBA 17 10-03-2018 09:07 AM
[SOLVED] Audit Trail Option in Access Khalil Islamzada General 18 06-15-2015 11:34 AM
Trigger code for Audit Trail ozinm SQL Server 1 01-03-2008 08:30 AM
Audit Trail Code? hi there Modules & VBA 2 08-04-2003 02:22 PM




All times are GMT -8. The time now is 05:01 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World