Take note of my edits in last post. Just edited again as a matter of fact.
Take note of my edits in last post. Just edited again as a matter of fact.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
One more issue: in the same case your 3rd field (INSERT part) seems to be a date field
(TransactionID, TransTypeID, TransDate but your 3rd Value field is
VALUES (2,3,T495867,
EDIT - I keep stumbling on stuff, this time a comment from the email:
You think syntax is some sort of spell check I take it. It is not. It means the structure of a statement, expression, whatever - is wrong. The problem could be as simple as a missing single quote.That says syntex error and there are no spelling errors. I have checked 25x or more!
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Hi all,
Problem Solved.... I feel so stupid right now! The issue was a coma after the last field on the insert statement.
Took out those comas and all went smooth.
Thank you again for the help, sorry to put all though this ordeal!
Dave
PMFJI,
Dave,
I am not a fan of your naming convention , so I renamed fields and changed the code. I added the tables and set relationships where I could
Also, I do not understand some of your logic.
Here is what I've done:
* All of the Primary key fields I added the suffix of "_PK" and the foreign key fields (that I could determine were FK fields) I added the suffix of "_FK".
* I added "Me." to the references to the controls in the VALUES clause in the code. (helps me know what are variables and what are references to controls on the form)
* Added the Debug statements to the code.
* I modified the SQL statements to the way I write SQL... (again, this is easier for me)
There appears to be a missing table. I one SQL INSERT statement, you are inserting into table "tblAccountLedger" and in a different SQL INSERT statement it is table "AccountLedgerTbl" ????
In the code, WHY are you referencing the two controls in the upper right of the form (in RED)???
In Case 2,4 , the order of the VALUES controls is out of order. And there are too many values.
Code:(TransactionID_FK, TransTypeID_FK, TransDate, AccountID, Description, Method, Debit) VALUES ( 8, 4, 'T568654', #10/26/2021#, 2, 'eeeee', 2 , 200)
And the modified code....
Code:Private Sub cmdCommit_Click() Dim db As DAO.Database Dim strSql As String Set db = CurrentDb Select Case Me.cboTransType Case 1 If Me.DepositRemburseExp = False Then strSql = "INSERT INTO tblAccountLedger (TransactionID_FK, TransTypeID_FK, TransCode, TransDate, AccountID, Description, Method, Credit)" strSql = strSql & " VALUES (" & Me.TransactionID & ", " & Me.cboTransType & ", '" & Me.TransCode & "', #" & Me.TransDate & "#, " strSql = strSql & Me.cboToAccount & ", '" & Me.Description & "', " & Me.cboTransMethod & ", " & Me.TransAmount & ")" Debug.Print strSql db.Execute strSql, dbFailOnError Else strSql = "INSERT INTO AccountLedgerTbl (TransactionID_FK, TransTypeID_FK, TransCode, TransDate, AccountID, Description, Method, Credit)" strSql = strSql & " VALUES (" & Me.TransactionID & ", " & Me.cboTransType & ", " & Me.TransCode & ", #" & Me.TransDate & "#, " strSql = strSql & Me.cboToAccount & ", '" & Me.Description & "', " & Me.cboTransMethod & ", " & Me.TransAmount & ")" Debug.Print strSql db.Execute strSql, dbFailOnError End If Case 2, 4 strSql = "INSERT INTO AccountLedgerTbl (TransactionID_FK, TransTypeID_FK, TransDate, AccountID, Description, Method, Debit)" strSql = strSql & " VALUES (" & Me.TransactionID & ", " & Me.cboTransType & ", '" & Me.TransCode & "', #" & Me.TransDate & "#, " strSql = strSql & Me.cboFromAccount & ", '" & Me.Description & "', " & Me.cboTransMethod & ", " & Me.TransAmount & ")" Debug.Print strSql db.Execute strSql, dbFailOnError Case 3 If Me.IsRemburseExp = False Then strSql = "INSERT INTO tblAccountLedger (TransactionID_FK, TransTypeID_FK, TransDate, AccountID, Description, Method, Debit)" strSql = strSql & " VALUES (" & Me.TransactionID & ", " & Me.cboTransType & ", '" & Me.TransCode & "', #" & Me.TransDate & "#, " strSql = strSql & Me.cboFromAccount & ", '" & Me.Description & "', " & Me.cboTransMethod & ", " & Me.TransAmount & ")" Debug.Print strSql db.Execute strSql, dbFailOnError Else strSql = "INSERT INTO AccountLedgerTbl (TransactionID_FK, TransTypeID_FK, TransDate, AccountID, Description, Method, Debit)" strSql = strSql & " VALUES (" & Me.TransactionID & ", " & Me.cboTransType & ", '" & Me.TransCode & "', #" & Me.TransDate & "#, " strSql = strSql & Me.cboFromAccount & ", '" & Me.Description & "', " & Me.cboTransMethod & ", " & Me.TransAmount & ")" Debug.Print strSql db.Execute strSql, dbFailOnError End If Case Else MsgBox "Unknown error" '<<< I added the msgbox because there was nothing in the CASE ELSE. End Select Set db = Nothing End Sub
Thank you all for the help. The issue was a coma after the last field in the insert into line. I feel so stupid right now.
Again, thanks for all the assistance and have maked this sovled!\\
Dave
you do great work,
I have the two controls in RED as they will eventually be not visible, just starting this db....
Love what you did
Thanks
Dave
Here is an accumulation of all the above advice. You need to verify that the user has supplied all the values necessary in the form for the updates to run successfully before executing the updates.
Code:Private Sub cmdCommit_Click() Dim strSql As String Dim db As DAO.Database Set db = CurrentDb Select Case Me.cboTransType Case 1 'deposit If Me.DepositRemburseExp = False Then strSql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, Method, Credit) " & _ "VALUES (" & TransactionID & ", " & cboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & cboToAccount & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")" Debug.Print "case 1 " & strSql db.Execute strSql, dbFailOnError Else strSql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, Method, Credit) " & _ "VALUES (" & TransactionID & ", " & cboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & cboToAccount & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")" Debug.Print "case 2 " & strSql db.Execute strSql, dbFailOnError strSql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, Method, Credit) " & _ "VALUES (" & TransactionID & ", " & cboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & cboExpense & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")" Debug.Print "case 2 " & strSql db.Execute strSql, dbFailOnError End If Case 2, 4 '2 payment, 4 transfer strSql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, Method, Debit) " & _ "VALUES (" & TransactionID & ", " & cboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & cboFromAccount & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")" Debug.Print "case 2,4 " & strSql db.Execute strSql, dbFailOnError strSql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, Method, Credit) " & _ "VALUES (" & TransactionID & ", " & cboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & cboToAccount & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")" Debug.Print "case 2,4 " & strSql db.Execute strSql, dbFailOnError Case 3 'purchase If Me.IsRemburseExp = False Then strSql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, Method, Debit) " & _ "VALUES (" & TransactionID & ", " & cboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & cboFromAccount & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")" Debug.Print "case 3 reimb no " & strSql db.Execute strSql, dbFailOnError Else strSql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, Method, Debit) " & _ "VALUES (" & TransactionID & ", " & cboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & cboFromAccount & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")" Debug.Print "case 2 reimb yes " & strSql db.Execute strSql, dbFailOnError strSql = "INSERT INTO tblAccountLedger (TransactionID, TransTypeID, TransCode, TransDate, AccountID, Description, Method, Credit) " & _ "VALUES (" & TransactionID & ", " & cboTransType & ", '" & TransCode & "', #" & TransDate & "#, " & cboExpense & ", '" & Description & "', " & cboTransMethod & ", " & TransAmount & ")" Debug.Print "case 3 reimb yes " & strSql db.Execute strSql, dbFailOnError End If Case Else End Select ' db.Execute strSql, dbFailOnError Set db = Nothing End Sub
Coma is when you don't wake up.The issue was a coma after the last field on the insert statement.
You mean like this: Debit,) ?
I think you'll find that isn't the issue for all cases even though I mentioned that fact. The following has no comma after the last field on the insert and it still doesn't run. I don't think you're paying enough attention to what you're being told. I provided sql without that comma and told you it raises an error:
This one has an extra comma in the Values list - or there is a missing field. Then both ssanfu and I have said that for one or more cases, values or fields are in the wrong order. I have to believe you got one case working and didn't check any of the others.Code:INSERT INTO AccountLedgerTbl (TransactionID, TransTypeID, TransDate, AccountID, Description, Method, Debit) VALUES (2,3,T495867, #10/26/21#,, 'test', 1,500)
Maybe it's just me, but you're not providing a whole lot of incentive to help out when you gloss over information like this.
I also find that the code in post 22 raises an error for case 3 else - same as before. Maybe that's on me too.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
OK, look at my link for debugging in my signature. That is for Youtube videos. If you still do not understand how to do simple debugging, then coding is not for you.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
FWIW you are NOT showing us the immediate window, you are showing us the code window.
However without putting in the Debug.Print and the rest I suggested, there is no point going to the immediate window.? :-(
Another benefit of the debug.print, is that you can copy the output and paste into a new query and the query designer will highlight the error usually.
I saw the error straightaway, but if I had told you what it was or just given you the corrected code, you would be back here again, just as you are now after your previous Insert problems.
This appears to be confirmed by that is what happened in your previous Insert problem post, which I also suggested a debug.print.
You need to learn basic debugging techniques, and quickly if you want to get ahead.
FWIW Ctrl + G gets you to the Immediate Window. The clue will be it will say Immediate in the window title.
And if you do not understand something, ask for clarification. Just not saying anything just leads me at least, to think you are just ignoring the advice, in which case you just go on my ignore list and then that is one less person to possible help you. My advice may not be as expert as others here, but I have been through what you are doing and I learnt from my mistakes.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba