Sql Error

[aceband]

Hi,

I'm trying to run a query and get the AutoIncrement ID Returned to me but i get this error "Too few parameters. Expected 3." On this line:

db.Execute ("INSERT INTO Loan([Copy ID], [Employee ID], [Loan Date], [Due Date]) VALUES (List2.Value, Combo4.Value, Date(), Calendar7.Value)")

And this is my whole thing:

Private Sub AddLoan_Click()

 If List2.ListIndex = -1 Then
    MsgBox ("Please select a copy to loan out.")
 ElseIf Combo4.ListIndex = -1 Then
    MsgBox ("Please select the employee.")
 Else
    'Add the loan entry
    
    'Get Insert ID
    
            Dim db As DAO.Database
            Dim rs As DAO.Recordset
            
            Set db = DBEngine(0)(0)
            db.Execute ("INSERT INTO Loan([Copy ID], [Employee ID], [Loan Date], [Due Date]) VALUES (List2.Value, Combo4.Value, Date(), Calendar7.Value)")
            
            Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
            ShowIdentity = rs!LastID
            
        MsgBox (ShowIdentity)
        
            rs.Close
            
            
            Set rs = Nothing
            Set db = Nothing
    
    'Update the copy and set it to On Loan
    Dim updateString
    updateString = "UPDATE Copy SET [Availability]='On Loan' WHERE [Copy ID]=List2.Value"

 End If
 
End Sub

[si_the_geek]

Welcome to VBForums

The problem is that the items like List2.Value etc are items in your VB code - they do not exist inside the database tables.

Rather than put the names of the controls into the SQL, you need to put in their values, and delimit them appropriately.

For more information, see the following articles from our Database Development FAQs/Tutorials (at the top of the Database Development forum):

How can I put the value of a variable/control into my SQL statement?
How do I use values (numbers, strings, dates) in SQL statements?

[aceband]

Thank you very much!

Do i also need to change the '[TabelNAme]' bits or are they valid?

Thanks

[si_the_geek]

They are valid, because they refer to actual database items (tables and fields).