Append Query in VBA 2

[woohahee]

Ok this ones got me stumped.

I have a list of all Suppliers needing to be set up.
Next to each supplier is a command button which is meant to take the specific record and append it to a new table.

The VBA code for the command button is as follows:

Private Sub SelectSupplier_Click()

Dim strSQL As String
Dim UseIDCode As String

UseIDCode = Form_frmSetUpSupplierFromPO.txtPOIDCode.Value

strSQL = "INSERT INTO [tblSupplierData] ([Supplier], [Address1]) SELECT [tblPurchaseOrderRequistion].[SupplierName], [tblPurchaseOrderRequistion].[SupplierAddress1]FROM [tblPurchaseOrderRequistion]WHERE (([tblPurchaseOrderRequistion].[IDCode]) = UseIDCode);"

CurrentDb.Execute strSQL

End Sub

And the error I get is "Run-time error '3061' - Too few parameters. Expected 1.

This has been puzzling me for a while - I know the problem is in my SQL statement but I can't see it!

Many thanks,

Rich

 

[CPUburn]

hi,

easiest and fastest way to solve this little problem:

1. place a break on the line currentdb.execute
2. in the immediate window get your sql string's value:
?strSQL

3. copy that string into the sql pane of a new query, it will then ask you the "parameter"

4. correct it in the string

grtz

cpuburn

 

[jebry]

Hi Rich!

Change the following line:

(([tblPurchaseOrderRequistion].[IDCode]) = UseIDCode);" to

(([tblPurchaseOrderRequistion].[IDCode]) = " & UseIDCode & ""

That assumes that UseIDCode is numeric.

hth
Jeff Bridgham

 

[JohnLowell]

The UseIdCode is a string variable?
If so, you need to wrap the variable with qoutes.
Try this code:
Private Sub SelectSupplier_Click()

Dim strSQL As String
Dim UseIDCode As String

UseIDCode = Form_frmSetUpSupplierFromPO.txtPOIDCode.Value

strSQL = "INSERT INTO [tblSupplierData] ([Supplier], [Address1]) SELECT [tblPurchaseOrderRequistion].[SupplierName], [tblPurchaseOrderRequistion].[SupplierAddress1] FROM [tblPurchaseOrderRequistion] WHERE [tblPurchaseOrderRequistion].[IDCode] = '" & UseIDCode & "';"

CurrentDb.Execute strSQL

End Sub

Let me know how this works,

John

 

[woohahee]

Thank you,

That just about solves everything.

Rich

 

[CPUburn]

hi,

give a poor man some fish and he's not hungry any more for now,
learn him how to fish and he'll allways know what to eat

;-0