I have an Audit function that is not returning control names

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

caandalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
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
caandalAuthor Commented:
I would not be able to run it it is not because the button that calls the procedure is on the form
0
caandalAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

caandalAuthor Commented:
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
peter57rCommented:
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
caandalAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
<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
caandalAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
OK

Great keep us posted.

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

I am sure Pete can get you going...

;-)

Jeff
0
caandalAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
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
caandalAuthor Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.