Qualifying question in global module

[gmazza via AccessMonster.com]

Hey there,
I have an Audit Trail module that gets called on the Before Update Event in
my Form's code.
I want this module to be used for all forms, so I need the qualifiers correct,
and not use the exact names of the forms. Can anyone help as I keep getting
errors on qualified names. Here is my module:
Dim db As DAO.Database
Dim C As control, xName As String, sSql As String
Dim strComments As String
Dim DatabaseName As String
Dim FormName As String


DoCmd.SetWarnings False
FormName = Screen.ActiveForm.Name
DatabaseName = CurrentProject.Name

For Each C In FormName.Controls ----------------------------------
Error here
strComments = "ElectronicAuthApproved"
If TypeOf C Is TextBox Then
If C.Value <> C.OldValue Or IsNull(C.OldValue) Then
If Not IsNull(C.Value) Then
If FormName.NewRecord = True Then ----------------------
Here it will error next surely
sSql = "INSERT INTO tblaudit ( audType, audDate, audUser,
audForm, audDatabase, FieldName, NewValue, Comments ) " & _
" SELECT 'Insert' AS Expr1 , " & _
"'" & Now & "', " & _
"'" & ap_GetUserName & "', " & _
"'" & "FormName" & "', " & _
"'" & DatabaseName & "', " & _
"'" & C.Name & "', " & _
"'" & C.Value & "', " & _
"'" & strComments & "'"
End If
DoCmd.RunSQL sSql
End If
End If
End If
Next C

DoCmd.SetWarnings True

 

[Potone]

    Dim C As control, xName As String, sSql As String
    Dim strComments As String
    Dim DatabaseName As String
    Dim FormName As String

   DoCmd.SetWarnings False
   FormName = Screen.ActiveForm.Name
   DatabaseName = CurrentProject.Name

    For Each C In FormName.Controls      ----------------------------------

correct syntax is:
For Each C in Forms(FormName).Controls

bye
Mat.

[cut]

 

[gmazza via AccessMonster.com]

Works Perfect, thanks Mat!!

[cut]

    Dim C As control, xName As String, sSql As String
    Dim strComments As String

[quoted text clipped - 6 lines]

    For Each C In FormName.Controls      ----------------------------------

correct syntax is:
For Each C in Forms(FormName).Controls

bye
Mat.

[cut]

 

[Dirk Goldgar]

Hey there,
I have an Audit Trail module that gets called on the Before Update Event
in
my Form's code.
I want this module to be used for all forms, so I need the qualifiers
correct,
and not use the exact names of the forms. Can anyone help as I keep
getting
errors on qualified names. Here is my module:
Dim db As DAO.Database
Dim C As control, xName As String, sSql As String
Dim strComments As String
Dim DatabaseName As String
Dim FormName As String


DoCmd.SetWarnings False
FormName = Screen.ActiveForm.Name
DatabaseName = CurrentProject.Name

For Each C In FormName.Controls ----------------------------------
Error here
strComments = "ElectronicAuthApproved"
If TypeOf C Is TextBox Then
If C.Value <> C.OldValue Or IsNull(C.OldValue) Then
If Not IsNull(C.Value) Then
If FormName.NewRecord = True
Then ----------------------
Here it will error next surely
sSql = "INSERT INTO tblaudit ( audType, audDate,
audUser,
audForm, audDatabase, FieldName, NewValue, Comments ) " & _
" SELECT 'Insert' AS Expr1 , " & _
"'" & Now & "', " & _
"'" & ap_GetUserName & "', " & _
"'" & "FormName" & "', " & _
"'" & DatabaseName & "', " & _
"'" & C.Name & "', " & _
"'" & C.Value & "', " & _
"'" & strComments & "'"
End If
DoCmd.RunSQL sSql
End If
End If
End If
Next C

DoCmd.SetWarnings True

Screen.ActiveForm gives you a reference to the form, so you don't need to be
concerned with the name of the form. Do it like this:

'----- start of revised code -----
Dim C As Access.Control,
Dim strSql As String
Dim strComments As String
Dim strDatabaseName As String
Dim strAudType As String
Const Q As String = """"
Const QQ As String = Q & Q

strDatabaseName = CurrentProject.Name

With Screen.ActiveForm

For Each C In .Controls

strComments = "ElectronicAuthApproved"

If TypeOf C Is TextBox Then

If C.Value <> C.OldValue Or IsNull(C.OldValue) Then

If Not IsNull(C.Value) Then

If .NewRecord = True Then
strAudType = "Insert"
Else

strAudType = "Update"
End If

strSql = _
"INSERT INTO tblaudit (" & _
"audType, audDate, audUser, audForm,
audDatabase, " & _
"FieldName, NewValue, Comments) " & _
"VALUES(" & _
"'" & strAudType & "'" & _
", " & Format(Now, "\#mm\/dd\/yyyy\#") & _
", '" & ap_GetUserName & "'" & _
", '" & .Name & "'" & _
", '" & strDatabaseName & "'" & _
", '" & C.Name & "'" & _
", " & Q & Replace(C.Value, Q, QQ) & Q & _
", " & Q & Replace(strComments, Q, QQ) & Q &
")"

CurrentDb.Execute strSql, dbFailOnError

End If

End If

End If

Next C

End With
'----- start of revised code -----

However, I'm not at all sure of your logic. You may well want to review
what happens if a control has been changed from a non-null value to Null.
And will there be no controls other than text boxes that you want to audit?
Do you want to audit unbound controls?

 

[gmazza via AccessMonster.com]

I tried what you suggested and keep receiving syntax errors on the Insert. I
don't see a closing braquet on the VALUES but I just can't seem to get around
the error.
I hear you about other controls, I'm just trying it on text boxes only for
now, and nothing unbound.
I'm just doing it piece by piece so here is the code and as I say, it errors
on the INSERT, syntax error.

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 & "', " & _
"'" & Now & "', " & _
"'" & ap_GetUserName & "', " & _
"'" & FormName & "', " & _
"'" & DatabaseName & "', " & _
"'" & C.Name & "', " & _
"'" & C.Value & "', " & _
"'" & strComments & "'"

'CurrentDb.Execute sSql, dbFailOnError
DoCmd.RunSQL sSql
End If
End If
End If
Next C

Hey there,
I have an Audit Trail module that gets called on the Before Update Event

[quoted text clipped - 43 lines]

DoCmd.SetWarnings True

Screen.ActiveForm gives you a reference to the form, so you don't need to be
concerned with the name of the form. Do it like this:

'----- start of revised code -----
Dim C As Access.Control,
Dim strSql As String
Dim strComments As String
Dim strDatabaseName As String
Dim strAudType As String
Const Q As String = """"
Const QQ As String = Q & Q

strDatabaseName = CurrentProject.Name

With Screen.ActiveForm

For Each C In .Controls

strComments = "ElectronicAuthApproved"

If TypeOf C Is TextBox Then

If C.Value <> C.OldValue Or IsNull(C.OldValue) Then

If Not IsNull(C.Value) Then

If .NewRecord = True Then
strAudType = "Insert"
Else

strAudType = "Update"
End If

strSql = _
"INSERT INTO tblaudit (" & _
"audType, audDate, audUser, audForm,
audDatabase, " & _
"FieldName, NewValue, Comments) " & _
"VALUES(" & _
"'" & strAudType & "'" & _
", " & Format(Now, "\#mm\/dd\/yyyy\#") & _
", '" & ap_GetUserName & "'" & _
", '" & .Name & "'" & _
", '" & strDatabaseName & "'" & _
", '" & C.Name & "'" & _
", " & Q & Replace(C.Value, Q, QQ) & Q & _
", " & Q & Replace(strComments, Q, QQ) & Q &
")"

CurrentDb.Execute strSql, dbFailOnError

End If

End If

End If

Next C

End With
'----- start of revised code -----

However, I'm not at all sure of your logic. You may well want to review
what happens if a control has been changed from a non-null value to Null.
And will there be no controls other than text boxes that you want to audit?
Do you want to audit unbound controls?

 

[Dirk Goldgar]

I tried what you suggested and keep receiving syntax errors on the Insert.
I
don't see a closing braquet on the VALUES but I just can't seem to get
around
the error.

It looks like you didn't fix one of the line wraps caused by the newsreader.
There was a closing parenthesis there, but it got wrapped to a new line, as
did another of the original code lines. I tried to format the code so as to
eliminate such problems before I posted, but I missed a couple. It also
looks like I left an unwanted comma on the declaration of C. Try this
amended version of the code:

'----- start of code -----
Dim C As Access.Control
Dim strSql As String
Dim strComments As String
Dim strDatabaseName As String
Dim strAudType As String
Const Q As String = """"
Const QQ As String = Q & Q

strDatabaseName = CurrentProject.Name

With Screen.ActiveForm

For Each C In .Controls

strComments = "ElectronicAuthApproved"

If TypeOf C Is TextBox Then

If C.Value <> C.OldValue Or IsNull(C.OldValue) Then

If Not IsNull(C.Value) Then

If .NewRecord = True Then
strAudType = "Insert"
Else

strAudType = "Update"
End If

strSql = _
"INSERT INTO tblaudit (" & _
"audType, audDate, audUser, audForm, " & _
"audDatabase, FieldName, NewValue, " & _
"Comments) " & _
"VALUES(" & _
"'" & strAudType & "'" & _
", " & Format(Now, "\#mm\/dd\/yyyy\#") & _
", '" & ap_GetUserName & "'" & _
", '" & .Name & "'" & _
", '" & strDatabaseName & "'" & _
", '" & C.Name & "'" & _
", " & Q & Replace(C.Value, Q, QQ) & Q & _
", " & Q & Replace(strComments, Q, QQ) & _
Q & ")"

CurrentDb.Execute strSql, dbFailOnError

End If

End If

End If

Next C

End With
'----- end of code -----

I strongly suggest that you not use DoCmd.RunSQL (and DoCmd.SetWarning
False/True), but instead use CurrentDb.Execute, as I've shown above. Also,
is your audDate field really a text field? If so, why? I'm assuming it's a
date/time field, in which case there is no reason to format Now() into a
text value to insert into it.

 

[gmazza via AccessMonster.com]

Ok Thanks Dirk, the errors are gone.
Now I am trying to wrap my head around the whole Date thing.
I have a Function, in a different module, which gets the current GMT date:
Private Function GetCurrentGMTDate() As String

Dim tzi As TIME_ZONE_INFORMATION
Dim GMT As Date
Dim dwBias As Long
Dim tmp As String

Select Case GetTimeZoneInformation(tzi)
Case TIME_ZONE_ID_DAYLIGHT
dwBias = tzi.Bias + tzi.DaylightBias
Case Else
dwBias = tzi.Bias + tzi.StandardBias
End Select

GMT = DateAdd("n", dwBias, Now)
tmp = Format$(GMT, "dddd mmm dd, yyyy hh:mm:ss am/pm")

GetCurrentGMTDate = tmp
End Function


Thing is, how can I call that in my Insert that we were just working on,
rather than using Now, I tried to Format it but I get a missing operator in
my expression error:

Format(GetCurrentGMTDate, "\#mm\/dd\/yyyy\#") & _

 

[Dirk Goldgar]

Ok Thanks Dirk, the errors are gone.
Now I am trying to wrap my head around the whole Date thing.
I have a Function, in a different module, which gets the current GMT date:
Private Function GetCurrentGMTDate() As String

Dim tzi As TIME_ZONE_INFORMATION
Dim GMT As Date
Dim dwBias As Long
Dim tmp As String

Select Case GetTimeZoneInformation(tzi)
Case TIME_ZONE_ID_DAYLIGHT
dwBias = tzi.Bias + tzi.DaylightBias
Case Else
dwBias = tzi.Bias + tzi.StandardBias
End Select

GMT = DateAdd("n", dwBias, Now)
tmp = Format$(GMT, "dddd mmm dd, yyyy hh:mm:ss am/pm")

GetCurrentGMTDate = tmp
End Function


Thing is, how can I call that in my Insert that we were just working on,
rather than using Now, I tried to Format it but I get a missing operator
in
my expression error:

Format(GetCurrentGMTDate, "\#mm\/dd\/yyyy\#") & _

I'm not sure why you're getting the specific error you mention, but now
you've changed the nature of the field. Your GetCurrentGMTDate() function
returns a string, so you're going to want to use a text field in your audit
table to hold it, and you need to change the SQL statement back to using
quotes around the value:


", '" & GetCurrentGMTDate() & "'" & _

 

[gmazza via AccessMonster.com]

Thanks for that. Not sure why we want any date field a text field, thats the
reason though it was failing when I originally used the Format, it was
returning a string, but the datatype was a Date/Time.
Take care!

Ok Thanks Dirk, the errors are gone.
Now I am trying to wrap my head around the whole Date thing.

[quoted text clipped - 25 lines]

Format(GetCurrentGMTDate, "\#mm\/dd\/yyyy\#") & _

I'm not sure why you're getting the specific error you mention, but now
you've changed the nature of the field. Your GetCurrentGMTDate() function
returns a string, so you're going to want to use a text field in your audit
table to hold it, and you need to change the SQL statement back to using
quotes around the value:

", '" & GetCurrentGMTDate() & "'" & _