asked on 10/4/2011

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

Microsoft Development Microsoft Access Programming

Last Comment

Jeffrey Coachman

10/6/2011

peter57r

10/4/2011

Is the form open when you run the code? (it needs to be open)

Otherwise we need to know which line causes the error.

caandal

10/4/2011

ASKER

I would not be able to run it it is not because the button that calls the procedure is on the form

caandal

10/4/2011

ASKER

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

caandal

10/4/2011

ASKER

Peter

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

peter57r

10/4/2011

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.

caandal

10/4/2011

ASKER

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.

Jeffrey Coachman

10/4/2011

<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

caandal

10/4/2011

ASKER

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....

Jeffrey Coachman

10/5/2011

OK

Great keep us posted.

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

I am sure Pete can get you going...

;-)

Jeff

ASKER CERTIFIED SOLUTION

caandal

10/5/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Jeffrey Coachman

10/5/2011

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

caandal

10/5/2011

ASKER

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

Jeffrey Coachman

10/6/2011

No need to be sorry about the points.

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

Jeff