Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Tables + Relationships _ Table Not Updating

Posted by: SemiAuto40 Dec 7 2017, 04:17 PM

I am having the most aggravating time calling my LogChange in its own module from another form after changes made pullhair.gif
With this code I get a runtime error 3134. I have tried with some success to open a recordset and .addnew - but even that won't work every time.
Anyone? Please?

CODE
Public Sub LogChange(TimeStamp As Date, Old_Classification_ID As String, OldSequential As String, OldRev As Integer, New_Classification_ID As String, NewSequential As String, NewRev As Integer, Activity As String)
        
  Dim strSQL as string

       strSQL = "INSERT INTO tbl_ChangeLog (TimeStamp, UserName, Old_Classification_ID, OldSequential, OldRev, New_Classification_ID, NewSequential, NewRev, Activity ) " & _
                    "VALUES (" & "#" & TimeStamp & "#," & "'" & MyUserName & "'," & "'" & Old_Classification_ID & "'," & "'" & OldSequential & "'," & OldRev & "," & _
                    "'" & New_Classification_ID & "'," & "'" & NewSequential & "'," & NewRev & "," & "'" & Activity & "'" & ");"

            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True

Posted by: SemiAuto40 Dec 7 2017, 04:27 PM

OK. Never mind. Once I added brackets to the terms in the INSERT INTO code it now works. Hopefully that was the problem! Thinking I did not need brackets if terms did not have any blank spaces.

Posted by: GroverParkGeorge Dec 7 2017, 04:29 PM

The best way I know to figure out this kind of problem is to put Debug.Print strSQL into the code and inspect the actual string created by your concatenation using the immediate window. You can copy it from there and try it in a query to see what error(s) are raised.


Posted by: GroverParkGeorge Dec 7 2017, 04:32 PM

It's likely that one or more of the field names you've picked are reserved words in Access which either should NOT be used for field names, or which must be enclosed in brackets [] in order for Access to recognize them as field names.

Check outhttp://allenbrowne.com/AppIssueBadWord.html and this https://support.office.com/en-us/article/Learn-about-Access-reserved-words-and-symbols-AE9D9ADA-3255-4B12-91A9-F855BDD9C5A2.

I'm thinking TimeStamp would be the guilty one here.

Posted by: SemiAuto40 Dec 7 2017, 04:35 PM

I use debug.print. The strSQL output all looked fine, quotes on strings, no quotes on numbers. It all just looked like it had to have worked... yet didn't. Doing runSQL must require the brackets on this operation.

Posted by: SemiAuto40 Dec 7 2017, 04:36 PM

Thank you for the list link! My 'TimeStamp' is a reserved word actually.

Thanks!

Posted by: GroverParkGeorge Dec 7 2017, 06:25 PM

The naming thing can be pretty insidious.

Congratulations on solving the mystery.

Continued success with your project.

Posted by: John Vinson Dec 8 2017, 08:36 PM

It NEVER hurts to use [square brackets] even if they're not needed. They even help readability once you get used to them - if it's in brackets you know it's the name of an Access object (field, control, form, prompt, or whatever).