I have a function that creates the audit trail of any changes made on a form. It doesn't work well if there is an apostrophe. So I applied 5 find and replace rules to cover most common apostrophe occurrences. The code fails at db.Execute repsql5, dbFailOnError . I run the result of debug.print repsql5 from a query window it works flawlessly and the data is updated correctly in the table, but the vba code fails with error message. Given below is the error message as exactly displayed.
Error Number 3078 Occurred
The Microsoft Office Access database engine cannot find the input table or query ''. Make sure it exists and that its name spelled correctly.
When I read through the forums before posting many people have suggested that this might happen with linked tables. I also tried dumping the data in a local table but that didn't work.
Please help.
Error Number 3078 Occurred
The Microsoft Office Access database engine cannot find the input table or query ''. Make sure it exists and that its name spelled correctly.
When I read through the forums before posting many people have suggested that this might happen with linked tables. I also tried dumping the data in a local table but that didn't work.
Please help.
Code:
Option Compare Database
Function WriteChanges(MyForm As Form)
Dim c As Control
Dim frm As String
Dim user As String
Dim CaseNum As String
Dim Time As Date
Dim sql As String
Dim changes As String
Dim repsql As String
Dim repsql1 As String
Dim repsql2 As String
Dim repsql3 As String
Dim repsql4 As String
Dim repsql5 As String
Dim db As DAO.Database
Set db = CurrentDb
frm = Screen.ActiveForm.Name
user = fOSUserName()
Debug.Print frm
If frm = "HEARINGAUDITMAIN2" Then
CaseNum = Forms!HEARINGAUDITMAIN2!CASE_NUMBER
ElseIf frm = "HEARINGAUDITMAIN" Then
CaseNum = Forms!HEARINGAUDITMAIN!CASE_NUMBER
ElseIf frm = "HEARINGAUDITSUBFORM2" Then
CaseNum = Forms!HEARINGAUDITSUBFORM2!CASE_NUMBER
ElseIf frm = "MAIN" Then
CaseNum = Forms!MAIN![APPEALCASEID]
ElseIf frm = "MAIN2" Then
CaseNum = Forms!MAIN2![APPEALCASEID]
End If
'Debug.Print CaseNum
Time = Now()
changes = ""
sql = "INSERT INTO AuditTrail " & _
"([FormName], [DateTime], [CaseNumber],[User], [ChangesMade]) " & _
"VALUES ('" & frm & "', '" & Time & "','" & CaseNum & "','" & user & "', "
'For Each c In f.Controls
For Each c In MyForm.Controls
Select Case c.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionGroup
If IsNull(c.OldValue) And Not IsNull(c.Value) Then
changes = changes & _
c.Name & "--" & "BLANK" & "--" & c.Value & _
vbCrLf
ElseIf IsNull(c.Value) And Not IsNull(c.OldValue) Then
changes = changes & _
c.Name & "--" & c.OldValue & "--" & "BLANK" & _
vbCrLf
ElseIf c.Value <> c.OldValue Then
changes = changes & _
c.Name & "--" & c.OldValue & "--" & c.Value & _
vbCrLf
End If
End Select
Next c
sql = sql & "'" & changes & "');"
repsql = Replace(sql, "n't", "n''t")
repsql1 = Replace(repsql, "'s", "''s")
repsql2 = Replace(repsql1, "'ll", "''ll")
repsql3 = Replace(repsql2, "'ve", "''ve")
repsql4 = Replace(repsql3, "'m", "''m")
repsql5 = Replace(repsql5, "o'", "o''")
Debug.Print repsql
Debug.Print repsql1
Debug.Print repsql2
Debug.Print repsql3
Debug.Print repsql4
Debug.Print repsql5
'db.Execute repsql5
db.Execute repsql5, dbFailOnError
Set f = Nothing
Set db = Nothing
End Function
Last edited: