Loop TypeOf Is Textbox, but can I exclude some text boxes?

[gmazza via AccessMonster.com]

Hey there,
I have a loop that goes through each textbox on the form and inserts a record
for each one that changed into an Audit table. There are a couple text boxes
I don't want inserted, I don't care that they changed, is there anyway I can
exclude them?
Here is a snipet of my code:

For Each C In Forms(FormName).Controls
If TypeOf C Is TextBox Then
If C.Value <> C.OldValue Or IsNull(C.OldValue) Then
If Not IsNull(C.Value) Then
If Forms(FormName).NewRecord = True Then
strAudType = "Insert"
Else
strAudType = "Update"
End If
sSql = "INSERT INTO Audit ( audType, audDate, audUser,
audForm, audDatabase, FieldName, NewValue, Comments ) " & _
"VALUES(" & _
"'" & strAudType & "', " & _
"'" & TimeAndDate() & "', " & _
"'" & ap_GetUserName & "', " & _
"'" & FormName & "', " & _
"'" & DatabaseName & "', " & _
"'" & C.Name & "', " & _
"'" & C.Value & "', " & _
"'" & strComments & "' " & _
")"

CurrentDb.Execute sSql, dbFailOnError
End If
End If
End If
Next C

 

[Jeff Boyce]

If C.Name = "YourExcludedTextBoxName" Then
Else
....

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[RoyVidar]

Hey there,
I have a loop that goes through each textbox on the form and inserts a
record for each one that changed into an Audit table. There are a couple
text boxes I don't want inserted, I don't care that they changed, is
there anyway I can exclude them?
Here is a snipet of my code:

For Each C In Forms(FormName).Controls
If TypeOf C Is TextBox Then
If C.Value <> C.OldValue Or IsNull(C.OldValue) Then
If Not IsNull(C.Value) Then
If Forms(FormName).NewRecord = True Then
strAudType = "Insert"
Else
strAudType = "Update"
End If
sSql = "INSERT INTO Audit ( audType, audDate,
audUser,
audForm, audDatabase, FieldName, NewValue, Comments ) " & _
"VALUES(" & _
"'" & strAudType & "', " & _
"'" & TimeAndDate() & "', " & _
"'" & ap_GetUserName & "', " & _
"'" & FormName & "', " & _
"'" & DatabaseName & "', " & _
"'" & C.Name & "', " & _
"'" & C.Value & "', " & _
"'" & strComments & "' " & _
")"

CurrentDb.Execute sSql, dbFailOnError
End If
End If
End If
Next C

If there are lot of controls, you could stuff the relevant controls into
a collection, and use/loop that collection in stead of looping all the
form controls each time. For lot of controls, this can make a difference.

Simpler, perhaps, is to use the .Tag property of controls, either just
put some value into it, or use some specific value, which can be tested,
say

If c.Tag = "Audit" Then
' insert to audittable
End IF