MS Access VBA - Using SELECT INTO to create a new table and add a record

Question

I'm trying to take a record from another table [Process Order] and put it into a new table with the date on it (NewTableName), but I keep getting 'Too few parameters. Expected 1'. I got it to work and pass one field over based on the [Process No] = txtProc or something earlier but can't get it to pass all the fields I need.

 Dim strSQL As String
 Dim NewTableName As String

 NewTableName = "[Process Order-Oven " & Format(Date, "DD-MM-YYYY") & "]"

 strSQL = "SELECT [Run No], [Product ID], [Process No], [Product Description], " & _
          "[Good Product Produced] INTO " & NewTableName & _
          " FROM 'Process Order' WHERE [Process No] = txtProc.Value"

 CurrentDb.Execute strSQL, dbFailOnError

Solution 1

[You need to concatenate the txt value. I hope it is a Number. If not enclose it between single quotes.

 Dim strSQL As String
 Dim NewTableName As String

 NewTableName = "[Process Order-Oven " & Format(Date, "DD-MM-YYYY") & "]"

 strSQL = "SELECT [Run No], [Product ID], [Process No], [Product Description], " & _
          "[Good Product Produced] INTO " & NewTableName & _
          " FROM [Process Order] WHERE [Process No] = " & txtProc

 CurrentDb.Execute strSQL, dbFailOnError

Solution 2

I removed the txtProc.Value from the string so that the value would be supplied - I assume it is a text field, so it is enclosed with quotes. This should solve the "too few parameters" error. I also changed the 'Process Order' to use bracket delimiters.

strSQL = "SELECT [Run No], [Product ID], [Process No], [Product Description], " & _
         "[Good Product Produced] INTO " & NewTableName & _
         " FROM [Process Order] WHERE [Process No] = '" & txtProc.Value & "'"

Comments

[ron tornambe]

I should add that using a parameter rather than enclosing the txtProc.Value within the string can prevent injection attacks.