SQL statement problem

[jonathanchye]

Can anyone please have a look at my SQL statement below? It looks correct to me but I get the error " Too few parameters - expected 1"...

  strSql18 = "INSERT INTO [tblSetTime] ( FKVersionID, txtMachineID,txtEB, txtBE, txtB, txtE, txtF) " & _
                    "SELECT " & lngID & " As FKVersionID,txtMachineID, txtEB, txtBE, txtB, txtE, txtF " & _
                    "FROM [tblSetTime] WHERE txtMachineID = " & LamID & ";"
                DBEngine(0)(0).Execute strSql18, dbFailOnError

 

[GinaWhipp]

The only thing I see it the brackets around tblSetTime that might be causing some confusion, try...

Watch out for word wrap...

  strSql18 = "INSERT INTO tblSetTime ( FKVersionID, txtMachineID, txtEB, txtBE, txtB, txtE, txtF) " & _
                    "SELECT " & lngID & " As FKVersionID, txtMachineID, txtEB, txtBE, txtB, txtE, txtF " & _
                    "FROM tblSetTime WHERE txtMachineID = " & LamID & ";"
                DBEngine(0)(0).Execute strSql18, dbFailOnError

 

[spikepl]

Put

debug.print strSql118

in your code before
DBEngine....

and inspect the output in the Immediate window

 

[McSwifty]

2 questions.

1.)Are lngId and LamID on the same form where this is running from?

2.)Are they text or number fields?

I would definitely go with spikepl, use the debug.print command and even put a breakpoint on the code when it runs so you can see the result.
what i do then is copy the SQL code strainght into an empty query to make sure the code is written correctly.

HTH.

 

[Galaxiom]

I have not thought about it too much but that query looks like it is working on denormalized data. You are repeating values by inserting data into the table it is being taken from.

In any case you should qualify the references to the controls with Me otherwise Access guesses what object they are coming from. I have seen it get it wrong.

The problem certainly is not the square brackets around the table name.

BTW That message appears if there are any quote marks in the concatenated values. In that case they would strings too and need quotes.