Select Case gives error on Insert Into Statement

[d9pierce1]

AcctData.zip

Hi all,
I have run into a problem I cannot seem to solve, have been all day on this. I have checked all spelling 10x, all values are correct and I have no idea why this Select Case is not working?


I have uploaded db and opens to form in question, hit Commit Button and error will arise!
Any assistance would be just wonderful....
Thanks
Dave

[Welshgasman]

Ok, you do not appear to take anything in

Put the SQl into a string variable and debug.print it.

How many times do you need to be told?

If even then you cannot see your error, you can post the output back here.

Oh and it is nothing to do with a Case statement, that at least works correctly.
I would also do the same for the other sql statements? as it looks like you have copy and pasted and amended to suit.

TIP: Make sure something is correct before copying and using it again and again.

[d9pierce1]

I am sorry but I do not understand how to put the "SQL into a String Variable and Debeg.Print It?
Please forgive my lack of understanding on this.\
Thanks

[Welshgasman]

Dim strSQL As String
StrSQL = all that SQL code you wrote for the insert
Debug.Print strSQL

Then look at the output in the immediate window.
You were given a link by pbaldy in your other Insert thread that did not work. Obviously did not bother to check it out.

Then you can also just Execute strSQL when you get it right!!!

[d9pierce1]

So, I beleive i did what you suggested? Still a bit fuzzy....
Here is the screen shot of results.
I have checked the spelling countless times against the form, against the tables and they are all correct.
I have put curser on the highlighted code and the values are absolutely correct and conform with the data types.
I have no idea what is wrong with this....?

[orange]

d9pierce1
Something like the following:
(Note the red ' before and after TransCode ---you need the ' delimiters for the TransCode which is a text datatype in tblAccountLedger)

assign the SQL to the variable strSQL
strSql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, Method, Credit,) " & _
"VALUES (" & TransactionID & ", " & cboTransType & ",'" & TransCode & "',#" & TransDate & "#, " & cboToAccount & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")"

Print the rendered SQL to the immediate window to check for any error
Debug.Print strSql

This appears in the immediate window: (NOTE: MY regional Date format is DD-MMM-YY)

INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, Method, Credit,) VALUES (2, 1, 'T495867',#26-Oct-21#, 2, 'test', 1, 500)

Also, you do not need both DoCmd.RunSQL and db.Execute strSql, dbFailOnError
I would drop the Docmd.Runsql approach. Use the db.execute strSQL, dbfailonerror

You can also remove the DoCmd.SetWarnings False and DoCmd.SetWarnings True if you use the db.execute.

[Micron]

Where's this part?

StrSQL = all that SQL code you wrote for the insert

You are asking for trouble by turning warnings off and not having any error handler that will turn them back on when the code prematurely exits because of an error. Haven't looked at your file yet. Perhaps that is next...

[Micron]

Late to the party again. This is your output if using the suggested method (I wrapped the line after Credit) to make it easier to read):
INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, Method, Credit,)
VALUES (2, 1, T495867, #10/26/21#, 2, 'test', 1, 500)

Hopefully you would have spotted the issue immediately, but it has already been pointed out for you. You also have a trailing , after Credit. I guess it doesn't matter. If you have a whole lot of similar sql, suggest you assign the repeating/common parts to variables and concatenate them. Say what follows is the only common part. Rather than typing this umpteen times

"VALUES (" & TransactionID & ", " & cboTransType & ", " & TransCode & ", #" & TransDate & "#, " & cboToAccount & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")"

do it once and concatenate

strValuePart = "VALUES (" & TransactionID & ", " & cboTransType & ", " & TransCode & ", #" & TransDate & "#, " & cboToAccount & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")"
sql = "INSERT INTO part here " & strValuePart

Can also have parts like strSelect, strCriteria, strSortOrder and so on, that you concatenate as

sql = strSelect & strCriteria & strSortOrder
db.Execute sql, dbFailOnError for example

[d9pierce1]

Thank you all for the help, i am trying!

I put the ' marks in like above and ran it again, still get the syntax error.
What i am seeing in immediate window when I hold curser over on the insert into the AccountID, Method, and Credit have no data pop up for them
Not sure if that is my problem but they are all same data type?
Here is screen shot2
Thanks

[Micron]

The highlighted one is not the one you have assigned to the variable. You may think it is the same, but obviously if the debug on the 1st assignment is correct, the one you're trying to run in the code is not the same.

[d9pierce1]

Hi Micron,
I copied and pasted that code and it has to be the same.
Did it several tiems to make sure, same results.
doesnt hightlight the first line (strSql=) just the next code?
I am really lost here and way over my head with this one.
I used a similiar code in a different app long ago and had no issues with it.
There really shouldnt be any reason why it dont work...
Thanks again

[d9pierce1]

Hi Orange,
How is the " db.execute strSQL, dbfailonerror " to replace the DoCmd.RunSql?
is it something like db.execute strSQL Inseert Into ....
I have never used that before?
Thanks

[Micron]

I copied and pasted that code and it has to be the same.

I doubt that the form or its records were exactly the same. If I click the button without altering any control values it fails even when I have corrected certain things (trailing commas in the INSERT portion), missing quotes as already mentioned. I'd say the reason for that is that you are not dealing with Null values, so when cboFromAccount (or To) is null you end up with

VALUES (2, 3, T495867, #10/26/21#, , 'test', 1, 500)

Since it's not my db, I don't know what to suggest as a solution.
Perhaps Nz if there is a valid value instead of Null.
Or have to test controls (but how many?) for Null and don't add them to the sql.
Or don't allow either From or To to be Null, which is a table field design problem. If there is a transaction, why can one of them be Null?
Or if these are the only 2 and it's OK to have Null, then test and actually put Null in the sql.

EDIT - I may have pegged the wrong form control there. It depends on which record you're on because various fields have no data in one or more records. Or there are just one too many commas in the sql, giving the impression that there's missing data. Not sure which at the moment.

EDIT 2 - 1 insert contains 7 fields while the Values portion contains 8 values. You can't do that either.

 
Case 3
...

Else
   strSql = "INSERT INTO AccountLedgerTbl (TransactionID, TransTypeID, TransDate, AccountID, Description, Method, Debit) " & _
   "VALUES (" & TransactionID & ", " & cboTransType & ", " & TransCode & ", #" & TransDate & "#, " & cboFromAccount & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")"

   Debug.Print strSql
   db.Execute strSql, dbFailOnError

[d9pierce1]

OK,
I have went over this so many times today... If I run the code from button i get the debug window. That says syntex error and there are no spelling errors. I have checked 25x or more!
With that said, the only thing i can find is in the immediate window, when you curser over the fields, AccountID, Method, and Credit dont give me a value like the others do? Is this maybe the problem?
I just dont understand why this isnt working. All the values are their, all the data types are the same per field... Man i would like to get this fixed. Any ideas at all on this?
Thanks
dave

[d9pierce1]

Hi Welshgasman
Just for the record, I did look at pbaldy link but just didnt understand it. I am now with a lot of assistance figuring out how to do this.
Just wanted you to know i dont ignore anything, i even watched a but load of videos on it too and didnt really help me.
Thanks