Invalid Use of "." (Dot) or "!" Operator

[techchallenged]

Help!!

I'm trying to create a log of updates, but although my code (below) works, it produces the following error:

ERROR # 2447:
There is an invalid use of the .(dot) or ! operator or
invalid parenthesis.

******CODE:**********************************************
'If control was previously Null, record _
"previous value was blank."

Dim MyForm As Form, C As Control, xName As String

If IsNull(C.OldValue) Or C.OldValue = "" Then
MyForm!Updates = MyForm!Updates & Chr(13) & _
Chr(10) & C.Name & "--previous value was blank"
**********************************************************
Does anyone know why this could be happening?

 

[ghubbell]

I think so... What's C ? ... Set C = Something! Gord
ghubbell@total.net

 

[techchallenged]

I declared "C" as a control (on my form). What I'm attempting to do is print the previous value (or old value) of any control on my form where the data was updated. If the control (or "C&quot was previously null, then my code will print "previous value was blank".

Thanks!

 

[RickSpr]

Wrap your If statement in a For loop:
For Each C In Forms!Updates.Controls
If TypeOf C Is TextBox _
Or TypeOf C Is ComboBox _
Or TypeOf C Is ListBox _
(etc.) Then
If IsNull(C) Or C.OldValue = "" Then
MyForm!Updates = MyForm!Updates & Chr(13) & _
Chr(10) & C.Name & "--previous value was blank"
End If
End If
Next C

As Gord said, you have to Set C to some control before you can test its properties. The For loop sets it to each control on the form in turn. However, some controls (such as Labels) aren't ones you want to test, so the first If statement eliminates those.
Rick Sprague

 

[Quehay]

techchallenged

Obviously you're doing a "What's changed and show me the before/after values" kind of thing. If you go through and mark the controls that you want to monitor with a
ctrl.tag = TrackChanges flag then you can run through the controls collection, checking for this flag (which hopefully is only set on controls with a text property!!)
and compare the .oldvalue and .text properties. What follows is a routine I'm using to do just that and insert the results in a "What's changed" table. There is a form routine and a call to a module routine (I've since tested and the first Nz function with the tag isn't necessary--no error appears when testing the controls whose tag isn't "TrackChanges):

[tt]
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorTrap

Dim ctrl As Control

'Check to see if any important info has changed
'Routine in basModule

'Refers to boolean result of function
If TrackChanges(Me) = True Then
Me![LastUpdt] = Date
Me.txtUpdt.Requery
End If


ExitErrorTrap:
Exit Sub

ErrorTrap:
MsgBox Err.Number & ": " & Err.Description
Resume ExitErrorTrap
End Sub

Public Function TrackChanges(ByVal frm As Form) As Boolean
On Error GoTo Error_TrackChanges

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim ctrl As Control

Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_TrackChanges", , dbAppendOnly)

For Each ctrl In frm.Controls

If Nz(ctrl.Tag, "&quot = "TrackChanges" Then
If CStr(Nz(ctrl.OldValue, &quot;&quot) <> CStr(Nz(ctrl.Value, &quot;&quot) Then

TrackChanges = True

With rs
.AddNew
!SiteKey = frm![SiteKey]
!FieldName = ctrl.ControlSource
!Changedate = Date
!OldValue = CStr(Nz((ctrl.OldValue), &quot;Empty&quot)
!NewValue = CStr(Nz((ctrl.Value), &quot;Empty&quot)
.Update
End With
End If
End If

Next

rs.Close


Exit_Error_TrackChanges:
Set rs = Nothing
Set db = Nothing
Exit Function

Error_TrackChanges:
MsgBox Err.Number & &quot;: &quot; & Err.Description
Resume Exit_Error_TrackChanges
End Function
[/tt]