Here is the problem I am having (and I am under the gun!!):
I have made a database for data entry. I added an audit trail. Now I am receiving an error message.
Error Msg Text: "Object doesn't support this property or method"
Error trigger: The error appears 1) AFTER I have updated (either an edit or new) data on the main form of "Companies" and WHEN I then click the tab to enter the subform of "Occupations"
Note: audittrail is recording the changes made in the main form.
The Audit trail SQL:
Code:
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 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()
'Populates username from lookup
Dim rst2 As ADODB.Recordset
Set rst2 = New ADODB.Recordset
Dim strSQL As String
strSQL = "Select UsernameDesc from Lookup_Username WHERE Username = '" & Environ("ComputerName") & "'"
If Not IsNull(DLookup("Name", "MSysObjects", "Name='Lookup_Username' And Type In (1,4,6)")) Then
rst2.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
If rst2.EOF Then
strUserID = Environ("ComputerName")
Else
If IsNull(rst2![UsernameDesc]) Then
strUserID = Environ("ComputerName")
Else
strUserID = rst2![UsernameDesc]
End If
End If
Else
strUserID = Environ("ComputerName")
End If
'strUserID = Environ("ComputerName")
Select Case UserAction
Case "EDIT"
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![Username] = strUserID
![FormName] = 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
For the subform:
Code:
Sub AuditChangesSub(IDField As String, UserAction As String)
On Error GoTo AuditChangesSub_Err
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ctl As Control
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()
'Populates username from lookup
Dim rst2 As ADODB.Recordset
Set rst2 = New ADODB.Recordset
Dim strSQL As String
strSQL = "Select UsernameDesc from Lookup_Username WHERE Username = '" & Environ("ComputerName") & "'"
If Not IsNull(DLookup("Name", "MSysObjects", "Name='Lookup_Username' And Type In (1,4,6)")) Then
rst2.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
If rst2.EOF Then
strUserID = Environ("ComputerName")
Else
If IsNull(rst2![UsernameDesc]) Then
strUserID = Environ("ComputerName")
Else
strUserID = rst2![UsernameDesc]
End If
End If
Else
strUserID = Environ("ComputerName")
End If
'strUserID = Environ("ComputerName")
Select Case UserAction
Case "EDIT"
For Each ctl In Screen.ActiveControl.Parent.Controls
If ctl.Tag = "Audit" Then
If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
With rst
.AddNew
![DateTime] = datTimeCheck
![Username] = strUserID
![FormName] = Screen.ActiveControl.Parent.Form.Name
![Action] = UserAction
![RecordID] = Screen.ActiveControl.Parent.Form(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.ActiveControl.Parent.Form.Name
![Action] = UserAction
![RecordID] = Screen.ActiveControl.Parent.Form(IDField).Value
.Update
End With
End Select
AuditChangesSub_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
AuditChangesSub_Err:
MsgBox Err.Description, vbCritical, "ERROR!"
Resume AuditChangesSub_Exit
End Sub
I really dont know where the problem is with this! I've used this audit trail SQL before with no issues. MSDN says:
"The "object doesn't support this property or method" error occurs when you try to use a method or property that the specified object does not support."
It then suggests:
"
- Use the Object Browser to determine what members (properties and methods) are available for the selected class (object).
- Use the IntelliSense feature in the Visual Basic Editor. When you type a period (.) after a property or method in the Visual Basic Editor, a list of available properties and methods is displayed.
- Use Word Visual Basic for Applications Help to determine which properties and methods can be used with an object. Each object topic in Help includes a page that lists the properties and methods for the object. Press F1 while in the Object Browser or while in a module to display the appropriate Help topic.
- Use the TypeName function to determine the type of object returned by an expression. The following example displays "Range" because the Content property returns a Range object."
I am not sure what they mean by "properties and methods" other than the literal meanings. So this Object Browser method is no help.
IntelliSense is useless as the VBA has been used many times without issues.
Word Visual Basic for Applications Help also no help as it goes back to "properties and methods" "can be used with an object" - again I understand the literal meanings but I do not know what THE ERROR is refferring to when it comes up, it doesn't open the code editor to show me WHAT is wrong.
The TypeName feature, same problem. I can't test something I can't find.
Any help would be great, the database is a large part of my first project after a major promotion so I really don't want to screw this up!
Thank you guys, you are awesome!!