Value of Existing Control

[CementCarver]

How does not obtain the value of an existing control? I've set my control be: dim ctl as control. But when I try to debug.print it's value with:

debug.print ctl.value..... after you keyin the period, the pulldown object properties does not contain the word "Value"

I'm using Access 2010.

CementCarver

[Dal Jeanis]

"Value" doesn't appear to be a property of a "generic" control object.

http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

Which is a little weird, because there is an OldValue property.

[CementCarver]

Yes, I thought it was alittle weird too. Since I'm using Martin Greene's audit trail code, he has that same object code line in his sample. But I'm having alot of problem trying to figure out why this is causing so much heartache.

So the line:

If Nz(ctl.value) <> Nz(ctl.oldvalue) then

is always causing an error: Object not supported. But yet another user attempted the same code and he got it to work. So I'm really confused.

CementCarver

[Dal Jeanis]

I wonder if you have a particular instance of ctl that does not have a Value?

[CementCarver]

Dal Jeanis, I'm still trying to figure this out. Received some code from another user, who supplied with a msgbox concatenation of several control items and I'm trying to figure out whether which control is causing me all this heart ache.

Thanks for your input......CementCarver

[Missinglinq]

...I wonder if you have a particular instance of ctl that does not have a Value...

You haven't given the exact code you're using, but I suspect that Dal Jeanis' statement is the answer to your problem. I assume that you're looping through the Controls on a Form, and unless you're limiting the Print statement to those types of Controls that have a Value, i.e. Textboxes, Comboboxes, Checkboxes, etc., you'll get this error. The usual culprit for this kind of thing is a Label, which has no Value.

Linq ;0)>

[CementCarver]

Missinglinq,

Below is the contents of my complete audit trail module. I too was suspecting a label to cause this one line to fail, but I have not tag any label with the tag Audit, so it should just pass over this. But I'm going to go back and check my work.

Option Compare Database
Option Explicit ' <<- this helps to find undeclared variables.
Sub AuditChanges(MEMBER_ID As String, UserAction As String)
'On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rstAudit As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
Dim strUserID As String
Dim MaxMember_id As Integer

Set cnn = CurrentProject.Connection
Set rstAudit = New ADODB.Recordset
rstAudit.Open "Select * from Audit_Trail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = CurrentUser()

MaxMember_id = DMax("[MEMBER_ID]", "BUSINESS")

Select Case UserAction
'existing records
Case "Edit"
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
Debug.Print "Control Name = " & ctl.Name & vbNewLine & "Control Type = " & ctl.ControlType
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then..........This is the line that's failing.....
With rstAudit
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![Record_ID] = MaxMember_id
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
'Debug.Print "Current values " & UserAction
End With
End If
End If
Next ctl
Case Else
' New or deleted records
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
' If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rstAudit
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![Record_ID] = MaxMember_id
![FieldName] = ctl.ControlSource
' ![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
' End If
End If
Next ctl
End Select
AuditChanges_exit:
On Error Resume Next
rstAudit.Close
cnn.Close
Set rstAudit = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "Error!"
Resume AuditChanges_exit
End Sub