Tracking changes/edits made by users on records.

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • NeoPa
    Recognized Expert Moderator MVP
    • Oct 2006
    • 32634

    #16
    Hi Shem.

    Can you show us the value of sSQL as the code stops on that fourth line please. That should give us a clue or two.

    Basically, we need (you) to check that the reference to the table is valid in two ways :
    1. The table as included in the SQL exists.
    2. The name of the table is valid in itself and doesn't need to be surrounded by '[' & ']' characters.

    You'd be looking for spaces in the name or such like.

    Comment

    • Shem K
      New Member
      • Apr 2015
      • 40

      #17
      Hi NeoPa.
      Please see from line 8.
      I included the previous code that relates to it:

      Code:
          'Remove any cancelled update still in the tmp table.
          Set db = DBEngine(0)(0)
          sSQL = "DELETE FROM " & sAudTmpTable & ";"
          db.Execute sSQL
      
          'If this was not a new record, save the old values.
          If Not bWasNewRecord Then
              sSQL = "INSERT INTO " & sAudTmpTable & " ( Audit_Type, Audit_Date, Audit_User ) " & _
                  "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
                  "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
              db.Execute sSQL, dbFailOnError
          End If
          AuditEditBegin = True

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32634

        #18
        I'm thoroughly confused as to why you'd want me to look at that code. It doesn't seem to follow on from what I suggested you do in any discernable way.

        I did look at the code just in case it could help. It answered no questions and gave no useful information that I was able to see.

        Maybe we should try again on this one. Please read my earlier post again and :
        A) Show the value requested.
        B) Confirm that the table does exist and is spelled exactly as you have it in the value which you will (hopefully) have shown by then.

        NB. It's the SQL code we're looking for here. Not any VBA code. VBA code that creates a SQL string is not a SQL string value. I hope what I'm saying is clearer this time.
        Last edited by NeoPa; Jun 11 '15, 01:11 AM.

        Comment

        • Shem K
          New Member
          • Apr 2015
          • 40

          #19
          Sorry. I thought it was the one in the VBA.
          It's clearer now. Navigating to the sSQL code is all new to me. I'll kindly need your help on doing it please, especially in unraveling that statement
          Code:
          db.Execute sSQL
          , as, in light of what you have just pointed out, I don't know which SQL it's referring to.

          Again, I apologise for my newbieness :(

          Comment

          • zmbd
            Recognized Expert Moderator Expert
            • Mar 2012
            • 5501

            #20
            insert right after the sSQL variable the following:
            Code:
            Debug.Print sSQL
            Press <Ctrl><G>
            Run your code.
            In the window that appears in the VBAISE when you pressed <Ctrl><G> the resolved string value for sSQL at that point will be printed. That is the string we need to review.

            Comment

            • Shem K
              New Member
              • Apr 2015
              • 40

              #21
              Hi guys. I trust you had a great weekend.
              Hi Z. The string value on the Immediate window (Ctrl+G) is:
              Code:
              DELETE FROM List_of_Matters_Audit_temp;

              Comment

              • zmbd
                Recognized Expert Moderator Expert
                • Mar 2012
                • 5501

                #22
                Line 3
                Code:
                  sSQL = "DELETE [iCODE]*[/iCODE] FROM " & sAudTmpTable & ";"
                :)

                Comment

                • NeoPa
                  Recognized Expert Moderator MVP
                  • Oct 2006
                  • 32634

                  #23
                  Originally posted by Shem
                  Shem:
                  The string value on the Immediate window (Ctrl+G) is:
                  Code:
                  DELETE FROM List_of_Matters_Audit_temp;
                  Perfect. That's exactly what we were after. Thanks for stepping in Z.

                  Originally posted by ZMBD
                  ZMBD:
                  Code:
                  sSQL = "DELETE [highlight]*[/highlight] FROM " & sAudTmpTable & ";"
                  In fact, neither the asterix (*) nor the semi-colon (;) is necessary for this SQL to work correctly.

                  Originally posted by NeoPa
                  NeoPa:
                  B) Confirm that the table does exist and is spelled exactly as you have it in the value which you will (hopefully) have shown by then.
                  @Shem.
                  Your SQL looks fine. What you still need to tell us from my post #16 is whether that table name is valid in your database at the time it's run. The error message in post #15 indicates that it is not. Please check the spelling very carefully as it's not a syntax problem.

                  Comment

                  • Shem K
                    New Member
                    • Apr 2015
                    • 40

                    #24
                    Hi NeoPa. The table does exist in my database. I did counter-checked the spelling severally, and the name is intact.

                    I thought it was probably the underscore present in my call events that made the code not recognise the table. I removed the underscores (to read "List of Matters Audit temp" in place of "List_of_Matter s_Audit_temp"), but this brings a syntax error.
                    Run-time error '3131':
                    Syntax error in FROM clause.
                    The error is on line 3.

                    I'd say I'm stuck at this point.

                    Comment

                    • zmbd
                      Recognized Expert Moderator Expert
                      • Mar 2012
                      • 5501

                      #25
                      bad habit that with asterix and semi-colon :)

                      Comment

                      • zmbd
                        Recognized Expert Moderator Expert
                        • Mar 2012
                        • 5501

                        #26
                        if the table name has spaces then you have to enclose it in the brackets nor will the underscore substitute for the space so the table must be comme ça:
                        [List of Matters Audit temp]

                        Code:
                        1.sSQL = _
                           "DELETE * FROM [iCODE][[/iCODE]" & _
                               sAudTmpTable & "[iCODE]][/iCODE];"

                        :) yes the "*" and ";" ... old dog - new tricks :)
                        Last edited by zmbd; Jun 16 '15, 03:14 PM.

                        Comment

                        • jforbes
                          Recognized Expert Top Contributor
                          • Aug 2014
                          • 1107

                          #27
                          When getting stuck like this and you have the SQL Command in the Immediate Windows, you can open a new Query, switch to SQL, paste the SQL from the Immediate Window into the Query and attempt to run it. You should get the same error with the possibility of Access highlighting what it thinks is wrong.
                          Last edited by zmbd; Jun 16 '15, 06:21 PM. Reason: [z{good point!!}]

                          Comment

                          • NeoPa
                            Recognized Expert Moderator MVP
                            • Oct 2006
                            • 32634

                            #28
                            The asterix and the semi-colon won't hurt at all. On the other hand their absense wouldn't explain the problem being experienced.

                            JForbes' idea of creating a QueryDef object based on the SQL you're trying to execute is a good one. One can often get better and more informnative error messages from a QueryDef that can help point you in the right direction to look for the problem.

                            That may be your best approach as I see nothing wrong with what you already have. Typically that error message that you got earlier is a strong indication that it doesn't recognise the table specified in the SQL. Whether that's due to spelling or incorrect specificatioon (A table name with spaces in which isn't surrounded by [] characters for instance.) is not clear.

                            If the trick JForbes suggests doesn't help you then you are left trying to determine why Access doesn't like tha table name supplied, because it seems clear it doesn't.

                            Comment

                            Working...