Error Number 3078 Occured (1 Viewer)

Hrithika

Registered User.
Local time
Today, 03:45
Joined
Aug 5, 2011
Messages
53
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.

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:

plog

Banishment Pending
Local time
Today, 02:45
Joined
May 11, 2011
Messages
11,613
You might have broken a record for most reserved words (https://support.microsoft.com/en-us/kb/286335) improperly used.

Reserved words are ones you shouldn't use as names (field, table, query, variable, etc.) because they can cause conflicts when writing code. At a glance I see 3 of them in your function (sql, Time and [DateTime]).

I don't know if they are causing the specific issue you are having, but they sure aren't helping. I'd try and rename the 2 variables (sql and Time) and see if that helps.
 

Hrithika

Registered User.
Local time
Today, 03:45
Joined
Aug 5, 2011
Messages
53
You might have broken a record for most reserved words (https://support.microsoft.com/en-us/kb/286335) improperly used.

Reserved words are ones you shouldn't use as names (field, table, query, variable, etc.) because they can cause conflicts when writing code. At a glance I see 3 of them in your function (sql, Time and [DateTime]).

I don't know if they are causing the specific issue you are having, but they sure aren't helping. I'd try and rename the 2 variables (sql and Time) and see if that helps.

Changed those two still the same error message.
 

JHB

Have been here a while
Local time
Today, 08:45
Joined
Jun 17, 2012
Messages
7,732
Is [DateTime] in table AuditTrail a text field type, then you're treated is as text?
Code:
         "VALUES ('" & frm & "', [B][COLOR=Red]'[/COLOR][/B]" & Time & "[B][COLOR=Red]'[/COLOR][/B],'" & CaseNum & "','" & user & "', "
' is only used for text field type, for date/time field type # is used and for number field type nothing is used.
I think also you have a problem here then you are setting repsql5 to an empty string, (and afterwards you're trying to run a query on an empty string):
Code:
       [B][COLOR=Red]repsql5[/COLOR][/B] = Replace([B][COLOR=Red]repsql5[/COLOR][/B], "o'", "o''")
 

Hrithika

Registered User.
Local time
Today, 03:45
Joined
Aug 5, 2011
Messages
53
Code:
repsql5 = Replace(repsql5, "o'", "o''")
was the problem. Now the data is updating. However, Now I have a little different problem. The changesmade field in the table is memo field but only getting updated with 255 character.

Is there a way I can capture the 'changes' in its entirety?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:45
Joined
Feb 28, 2001
Messages
27,001
You mentioned in passing that your database worked with linked tables. Linked to what?

If the answer is Excel (as a back-end table holder) then the problem is with Excel. A cell normally cannot exceed 255 characters.

If the answer is Access (using a second Access file to hold back-end tables) then the question is what you are using as an intermediate to capture or otherwise manipulate the fields you are editing. Know that old saying about "the weakest link in the chain"? It also applies to the SMALLEST link in the chain.

Also, the function/subroutine call might make a difference (though this one, I'm not betting a lot on...). When you pass strings into the function and pass answers out, are you passing by value or by reference? I think if you have MEMO fields, you need to use BYREF for passing things in. I am not at all sure for passing things out, but BYREF, if you set it up correctly, wouldn't hurt matters either.
 

Cronk

Registered User.
Local time
Today, 18:45
Joined
Jul 4, 2013
Messages
2,770
To avoid SQL problems when using variables containing text with apostrophes, don't use single quote delimiters. Use chr(34) which gives a double quote. For example,
instead of
Code:
txtSQL = ......WHERE Surname =' & me.txtSurname & "'"
use
Code:
txtSQL = ......WHERE Surname = & chr(34) &  me.txtSurname & chr(34)
 

Users who are viewing this thread

Top Bottom