Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-13-2015, 11:20 AM   #1
Hrithika
Newly Registered User
 
Join Date: Aug 2011
Posts: 53
Thanks: 14
Thanked 0 Times in 0 Posts
Hrithika is on a distinguished road
Error Number 3078 Occured

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 by Hrithika; 05-13-2015 at 11:32 AM.
Hrithika is offline   Reply With Quote
Old 05-13-2015, 11:40 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 9,348
Thanks: 10
Thanked 2,270 Times in 2,222 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Error Number 3078 Occured

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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
Hrithika (05-14-2015)
Old 05-13-2015, 12:13 PM   #3
Hrithika
Newly Registered User
 
Join Date: Aug 2011
Posts: 53
Thanks: 14
Thanked 0 Times in 0 Posts
Hrithika is on a distinguished road
Re: Error Number 3078 Occured

Quote:
Originally Posted by plog View Post
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.

Hrithika is offline   Reply With Quote
Old 05-13-2015, 10:32 PM   #4
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,081 Times in 2,036 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Error Number 3078 Occured

Is [DateTime] in table AuditTrail a text field type, then you're treated is as text?
Code:
         "VALUES ('" & frm & "', '" & Time & "','" & 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:
       repsql5 = Replace(repsql5, "o'", "o''")
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
Hrithika (05-14-2015)
Old 05-14-2015, 07:40 AM   #5
Hrithika
Newly Registered User
 
Join Date: Aug 2011
Posts: 53
Thanks: 14
Thanked 0 Times in 0 Posts
Hrithika is on a distinguished road
Re: Error Number 3078 Occured

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?
Hrithika is offline   Reply With Quote
Old 05-14-2015, 09:40 AM   #6
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,346
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Error Number 3078 Occured

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 05-14-2015, 11:24 AM   #7
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,137
Thanks: 3
Thanked 466 Times in 459 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Error Number 3078 Occured

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)


Cronk is offline   Reply With Quote
Reply

Tags
function , modules & vba , vba access 2007

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
run time error 3078 hfs Modules & VBA 11 11-12-2013 09:51 AM
An error occured while loading form... Bilbo_Baggins_Esq Modules & VBA 6 05-24-2013 10:05 AM
Run Time Error 3078 jimjaix General 5 02-02-2010 02:59 PM
Runtime error occured when accessing a control in a subreport ZeroCoolJr Forms 0 06-17-2009 01:49 AM
enter parameter value" popuopwindow error occured sridhar1980 Forms 1 09-19-2005 03:10 AM




All times are GMT -8. The time now is 07:03 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World