Select Case gives error on Insert Into Statement

[Micron]

Take note of my edits in last post. Just edited again as a matter of fact.

[Micron]

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:

That says syntex error and there are no spelling errors. I have checked 25x or more!

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.

[d9pierce1]

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

[ssanfu]

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.

       (TransactionID_FK, TransTypeID_FK,  TransDate,    AccountID, Description,  Method, Debit)
VALUES (               8,              4,  'T568654', #10/26/2021#,           2, 'eeeee',     2  , 200)

And the modified 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

[d9pierce1]

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

[d9pierce1]

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

[davegri]

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.

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

[Micron]

The issue was a coma after the last field on the insert statement.

Coma is when you don't wake up.

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:

INSERT INTO AccountLedgerTbl (TransactionID, TransTypeID, TransDate, AccountID, Description, Method, Debit) VALUES (2,3,T495867, #10/26/21#,, 'test', 1,500)

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.

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.

[Welshgasman]

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

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.

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.