Append query in VBA Code

[Khalil Handal]

Hi to all


The following query adds records to the selected table and runs pefectly as an access query:

INSERT INTO tblStorage ( BookTitle, DateRegistered, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages, YearPublished, BoxNumber, BookNumbering )
SELECT tblBooks.BookTitle, tblBooks.DateRegistered, tblBooks.VolumeNumber, tblBooks.CallNumberNum, tblBooks.CallNumberText, tblBooks.NumberOfPages, tblBooks.YearPublished, [Forms].[frmBooks].[txtBoxNumber] AS BoxNumber, [Forms].[frmBooks].[txtBookNumbering] AS BookNumbering
FROM tblBooks;

The Form frmBooks is open and I added a command button to execute the above query. The code is:

Dim strSQL As String
strSQL = "INSERT INTO tblStorage ( BookTitle, DateRegistered, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages, YearPublished, BoxNumber, BookNumbering )" _
            & " SELECT tblBooks.BookTitle, tblBooks.DateRegistered, tblBooks.VolumeNumber, tblBooks.CallNumberNum, tblBooks.CallNumberText, " _
            & "tblBooks.NumberOfPages, tblBooks.YearPublished, [Forms].[frmBooks].[txtBoxNumber] AS BoxNumber, [Forms].[frmBooks].[txtBookNumbering] AS BookNumbering " _
            & "FROM tblBooks"
    
    Debug.Print strSQL
db.Execute strSQL

I am receiving the following error:
Error 3061: Too few parameters. Expected 2

Any ideas?

Sincerely
Khalil

[Welshgasman]

Concatenate the form control values into your string.
Debug.Print any sql string before you try and use it.

[jojowhite]

if both BoxNumber and BookNumbering are numeric:

strSQL = "INSERT INTO tblStorage ( BookTitle, DateRegistered, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages, YearPublished, BoxNumber, BookNumbering )" _
            & " SELECT tblBooks.BookTitle, tblBooks.DateRegistered, tblBooks.VolumeNumber, tblBooks.CallNumberNum, tblBooks.CallNumberText, " _
            & "tblBooks.NumberOfPages, tblBooks.YearPublished, " & [Forms].[frmBooks].[txtBoxNumber] & ", " & [Forms].[frmBooks].[txtBookNumbering] & " " _
            & "FROM tblBooks"

[Khalil Handal]

Hi
Code in post #3 gave the error message that :

Object doesn't support this property or method

I used the following code and it works:

Dim strBoxNumber As String
   Dim strBookNumbering As String
   
   strBoxNumber = Forms.frmBooks!txtBoxNumber
   strBookNumbering = Forms.frmBooks!txtBookNumbering

strSQL = "INSERT INTO tblStorage ( BookTitle, DateRegistered, VolumeNumber, CallNumberNum, CallNumberText, NumberOfPages, YearPublished, BoxNumber, BookNumbering )" _
            & " SELECT BookTitle, DateRegistered, VolumeNumber, CallNumberNum, CallNumberText," _
            & " NumberOfPages, YearPublished, '" & strBoxNumber & " ' AS BoxNumber, ' " & strBookNumbering & " ' AS BookNumbering" _
            & " FROM tblBooks "

Thank you all

Khalil

[Welshgasman]

Another way to try is surround the control with Eval()

[jojowhite]

it errors, because your Fields are String, and the SQL I wrote is for Numeric, get it?