?
Solved

I have an Audit function that is not returning control names

Posted on 2011-10-03
13
Medium Priority
?
227 Views
Last Modified: 2012-05-12
Hi Gents
I found an Audit Trail code snippet in an old post on Experts-Exchange.  I have put the function call on a save button in the code.  The code is falling over and I am not sure why. When stepping through the code I notice that the control names  are not being returned.  Can someone please help me. (GetAnalyst is a function that returns the username- this works as I use it elsewhere)
Alan  
'In the on click command on the button
   Dim x As Integer
   If Not IsNull(Me.ReportID) Then
    x = WriteAudit(Me, Me.ReportID)
    End If

'In the module which I have left as Module1
Public Function WriteAudit(frm As Form, lngID As Long) As Boolean
On Error GoTo err_WriteAudit
 
    Dim ctlC As Control
    Dim strSQL As String
    Dim bOK As Boolean
    
    bOK = False
    
    DoCmd.SetWarnings False
    
    ' For each control.
    For Each ctlC In frm.Controls
        If TypeOf ctlC Is TextBox Or TypeOf ctlC Is ComboBox Then
            If ctlC.Value <> ctlC.OldValue Or IsNull(ctlC.OldValue) Then
                If Not IsNull(ctlC.Value) Then
                    strSQL = "INSERT INTO tAudit ( ReportID, FieldName, AudB4, AudAft, User, [Date]) " & _
                           " SELECT " & lngID & " , " & _
                           "'" & ctlC.Name & "', " & _
                           "'" & ctlC.OldValue & "', " & _
                           "'" & ctlC.Value & "', " & _
                           "'" & GetAnalyst & "', " & _
                           "'" & Now & "'"
  
                           
                           
                    'Debug.Print strSQL
                    DoCmd.RunSQL strSQL
                End If
            End If
        End If
    Next ctlC
    
    WriteAudit = bOK
    
exit_WriteAudit:
    DoCmd.SetWarnings True
    Exit Function
    
err_WriteAudit:
    MsgBox Err.Description
    Resume exit_WriteAudit
    
End Function

Open in new window

0
Comment
Question by:caandal
  • 7
  • 4
  • 2
13 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36908232
Is the form open when you run the code? (it needs to be open)

Otherwise we need to know which line causes the error.
0
 

Author Comment

by:caandal
ID: 36908249
I would not be able to run it it is not because the button that calls the procedure is on the form
0
 

Author Comment

by:caandal
ID: 36908258
Peter

the other thing is that when I have put watches on the code the control names are not being returned which means Cctl.Value in fact can't be created so the procedure would fall over anyway  
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:caandal
ID: 36908290
Peter

Here is the message that i am getting but we are not even getting to the step in the code which generates this
     Error Message
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36908374
When you are explicitly referring to a control and definitely NOT referring to the Field in the control you need to be sure that the control has a different name from the field.

Make sure this is so in your case.
0
 

Author Comment

by:caandal
ID: 36909717
my controls generally have the same name as the field names - I have not had an issue with this in the past.  If I use the immediate window to check the value and use the controlname.value it returns the value in the control/field.  I do not think that is the problem but I will try renaming some of the fields and run it anyway.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36910294
<No Points wanted, just some comments>

<my controls generally have the same name as the field names - I have not had an issue with this in the past.>
You mean until now....?
;-)

If you use:
me.FirstName.BackColor on a "TextBox Control" it works fine.
If you try the same thing on a field, it does not work, because a "Field" does not have a backcolor property, but a "Control" does.

Leaving the default name is like giving all your children the same name
It will work, but it causes too much confusion....



;-)


Jeff

Especially with issue like this where the code may presume  that the names are different.

Changing the controls names is fairly standard in MS Access development:
http://www.xoc.net/standards/rvbanc.asp
http://en.wikipedia.org/wiki/Leszynski_naming_convention

0
 

Author Comment

by:caandal
ID: 36913419
Hi Jeff

Thanks for that.  Interesting.  I can see why this may be the casue of the problem.  Going to try it out and see what happens tx Guys - I will be back....
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36914709
OK

Great keep us posted.

As I stated, I don't want any points...

I am sure Pete can get you going...

;-)

Jeff
0
 

Accepted Solution

by:
caandal earned 0 total points
ID: 36915009
Hi Guys

The problem has nothing to do with naming conventions.  I had a Control that was hidden behind a Logo that was put there for testing purposes way back when.  The code was falling over on that field.I deleted it and Voila.  The control values were returned irresepective of whether the control  had the same name as the filed or not.

However I do see the logic in the naming conventions as laid out by Leszynski.  I use the same sort of logic for tables queries stored procs etc.  I have not used it at control level but I do not think that it is a bad idea to do so.

Cheers
Alan
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36917031
OK Alan.

The other factor here is troubleshooting.
 If you have a control named "PictureFile", it wont be clear if this the actual image displayed in an image control ("imgPictureFile"), or if it contains the image path in a textbox ("txtPictureFile").

It gets more confusing with Boolean fields.

In this case you can actually accept your own post as the solution.
;-)

Jeff
0
 

Author Closing Comment

by:caandal
ID: 36941162
Thanks for your input guys - sorry about the points.  The nice thing about the jobs that we do is that there is always room for new ideas and ways of doing things

Kind regards
Alan
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36922185
No need to be sorry about the points.

You solved the issue yourself, and you posted the solution, ...so you deserve the points...
;-)

Jeff
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question