3

I have a MS Access database that records financial transactions. It has a form named frmDeposits that is based on table tblDep. One of the buttons on that form runs a procedure that inserts data from the current record into another table, tblAccount, using DoCmd.RunSql. The code looks like this:

DoCmd.RunSQL "INSERT INTO tblAccount ([Date], CheckNo, Amount, Vendor, Cleared, Deposit, Printed, Misc) " _
        & "SELECT [Date], CheckNo, Amount, Vendor, Cleared, " & ident & " AS Deposit, Printed, Misc " & _
        & "FROM tblDep WHERE Form=" & formnum & ";"

The formnum variable is set elsewhere, and identifies the current record in the form. The ident variable is also defined elsewhere.

Somehow, in the process of debugging other parts of my code, the last line of this SQL statement was erased, leaving:

DoCmd.RunSQL "INSERT INTO tblAccount ([Date], CheckNo, Amount, Vendor, Cleared, Deposit, Printed, Misc) " _
        & "SELECT [Date], CheckNo, Amount, Vendor, Cleared, " & ident & " AS Deposit, Printed, Misc"

Surprisingly, the form continued to operate correctly! This RunSQL statement was still picking up the current record from frmDeposits and inserting it into the tblAccount table.

My question is, why does this work? I thought that SQL statements with a SELECT had to have a FROM clause. I know you can use INSERT INTO ... VALUES with no FROM to insert a single record, but even then you have to supply actual values and not field names.

One other thing I noticed: This only seems to work on code in the form's module. If I paste the SQL string into a query design and run it, or run similar code outside the form's scope, I get the 'Enter Parameter Value' popup box for each field. Is VBA pre-parsing the SQL string, and grabbing the form fields or text boxes for the parameters? Or is it defaulting the FROM to the form's RecordSource? I don't have text boxes on the form for all the fields referenced in the SQL string, although they all exist in the form's underlying table.

Btw, this is running in Access 2000. Yes, I know it's an outdated version, but that's what I have to work with.

**Edit - One added note, this behavior only shows up when using DoCmd.RunSQL. If I put the same SQL string into a db.Execute, it returns an 'Wrong number of parameters' error.

user2205564
  • 31
  • 2

1 Answers1

2

VBA code exists in two forms in an Access application: plain text source; and compiled "p-code". Perhaps what has happened is the stored p-code didn't get changed when that part of the source code text went missing. IOW, it still includes symbols for the missing FROM clause.

In your situation, I would suspect corruption and perform a decompile to clean up the compiled code. First make a backup of the database for safe keeping. You can find detailed instructions for decompile in the 2 answers to this question: HOW TO decompile and recompile.

Frankly corruption is sort of a wild guess. However, since part of your source code mysteriously disappeared and Access operates as if it's still there, corruption seems like a stronger possibility.

Another possibility is you have DoCmd.SetWarnings = False. Turning SetWarnings off silently discards the error message about the failed INSERT attempt. So you don't see an error message but nothing actually gets inserted, either.

Community
  • 1
  • 1
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Interesting thought. Unfortunately, I tried decompiling & recompiling, and the behavior doesn't change. I don't actually think the 3rd line disappeared on its own, I think I accidentally deleted it while working with the surrounding code. – user2205564 Mar 24 '13 at 23:53
  • That was actually one of the first things I tried, but even with SetWarnings on, the only message is the standard 'This query is about to insert 1 record'. And with the warnings either way, the record still gets inserted fine. – user2205564 Mar 25 '13 at 02:57