Hi guys,
need your knowledge again, if possible.
Based on http://www.fontstuff.com/access/acctut21.htm I have been trying to get changes not only in the main form (as in code) but to get changes from the subforms as well...
1. Original code to retrieve changes only on the mainform
Code:
For Each ctl In Screen.ActiveForm.Controls
.
.
.
![FormName] = Screen.ActiveForm.Name
2. But if I change the code a "little" I can now record changes in the subforms but not from the mainform... plus I get an error message
"Object doesn't support this property or method" when I change a field in the mainform
Code:
strCtl = Screen.ActiveForm.ActiveControl.Name
strParent = Screen.ActiveForm.ActiveControl.Parent.Name
For Each ctl In Forms(strParent)(strCtl).Form.Controls
.
.
.
![FormName] = Forms(strParent)(strCtl).Form.ActiveControl.Name
I can I tell access to use either code depending on the location of the field (form or subform)
Playing code... Database is found here, in case you are interested as well
Code:
Option Compare DatabaseOption Explicit
Sub AuditChanges(IDField As String, UserAction As String)
On Error GoTo AuditChanges_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
'#
Dim strCtl
Dim strParent
Dim strTemp
Dim strTempControl As Control
strCtl = Screen.ActiveForm.ActiveControl.Name
strParent = Screen.ActiveForm.ActiveControl.Parent.Name
' If Forms(strParent)(strCtl).ControlType = acSubform Then
' strTemp = Forms(strParent)(strCtl).Form.ActiveControl.Name
' 'strTempControl = Screen.ActiveForm.Controls
' 'strActCtl2 = Forms(strParent)(strCtl).Form.ActiveControl
' Else
' strTemp = strParent
' 'strTempControl = Screen.ActiveForm.Controls
' End If
'#
Dim datTimeCheck As Date
Dim strUserID As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()
strUserID = Environ("USERNAME")
Select Case UserAction
Case "EDIT"
' If IsNull(Forms(strParent)(strCtl).Form.Controls) Then
' strTempControl = Screen.ActiveForm.Controls
' Else
' strTempControl = Forms(strParent)(strCtl).Form.Controls
' End If
For Each ctl In Forms(strParent)(strCtl).Form.Controls 'Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Forms(strParent)(strCtl).Form.ActiveControl.Name 'Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
End With
End If
End If
Next ctl
Case Else
With rst
.AddNew
![DateTime] = datTimeCheck
![UserName] = strUserID
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![RecordID] = Screen.ActiveForm.Controls(IDField).Value
.Update
End With
End Select
AuditChanges_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChanges_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChanges_Exit
End Sub