Help With Action Queries in VBA

[3239]

Please help!
I am getting the following error: "To few parameters. Expected 1" when I try to run an action query in vba.

Here is my code.

Dim QryPO As String

'Insert record into POarchive table before deleting
    
    QryPO = "INSERT INTO POArchive" & _
    "( POID, [Date], Buyer, DeliveryFOB, Terms, TimeForDelivery, Remarks," & _
    "[Attn:], RoomNumber )" & _
    "SELECT PurchaseOrder.POID, PurchaseOrder.Date, PurchaseOrder.Buyer," & _
    "PurchaseOrder.DeliveryFOB, PurchaseOrder.Terms, PurchaseOrder.TimeForDelivery," & _
    "PurchaseOrder.Remarks, PurchaseOrder.[Attn:], " & _
    "PurchaseOrder.RoomNumber FROM PurchaseOrder WHERE (((PurchaseOrder.POID)=[Forms]![POSearch]![POID]));"

CurrentDb.Execute QryPO, dbFailOnError

Thanks

 

[dhookom]

I think there should be a space prior to the SELECT and then change to get the form reference value. Assuming POID is numeric, try:

Dim QryPO As String

'Insert record into POarchive table before deleting
    
    QryPO = "INSERT INTO POArchive" & _
    "( POID, [Date], Buyer, DeliveryFOB, Terms,  " & _
    "TimeForDelivery, Remarks, [Attn:], RoomNumber ) " & _
    "SELECT POID, [Date], Buyer," & _
    "DeliveryFOB, Terms, TimeForDelivery, " & _
    "Remarks, [Attn:], " & _
    "RoomNumber " & _
    "FROM PurchaseOrder " & _
    "WHERE POID=" & [Forms]![POSearch]![POID]

CurrentDb.Execute QryPO, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[3239]

That worked!

Thanks Duane

 

[kjv1611]

How well did it work?

Don't forget the little link that says:

Thank dhookom
for this valuable post!

Then others will know what worked here, when they run into the same or similar problem.

--

"If to err is human, then I must be some kind of human!" -Me