Populating ADODB Recordset from query

[Guest]

I have created an Access Database to track an assembly process and the costs of the components

I have a purchases form which uses a datasheet subform tied to the tblPurchaseDetails, to record items purchased. When I save the Purchase entry, I also need to modify another table that tracks current inventory and the latest Cost for the components

I am trying to open a recordset using an SQL statement on tblPurchaseDetails , so that I can limit the number of rows in the recordset to those of the current purchase

Here is the code I have been using

Dim numPurchaseID As Lon
numPurchaseID = Me.PurchaseID.Valu

Dim cnn As Connectio

Dim rstThisPurchase As New ADODB.Recordse

Dim txtSelect As Strin

txtSelect = "SELECT tblPurchaseDetail.PurchaseID, tblPurchaseDetail.ComponentType,tblPurchaseDetail.ComponentID, tblPurchaseDetail.Number, tblPurchaseDetail.Cost FROM tblPurchaseDetail WHERE PurchaseID = numPurchaseID;

Set cnn = CurrentProject.Connectio

rstThisPurchase.Open [txtSelect], cnn, , adLockReadOnly, adCmdTex

When I run the subroutine, I get a "Runtime Error -214721904 No value given for one or more required parameters.

I have tried several other values for the adCursor and adLock parameters, but with no success

What am I missing, or am I trying to do something that is not supported?

Thanks in advance

--jade

 

[Treebeard]

I have created an Access Database to track an assembly process and the costs of the components.

I have a purchases form which uses a datasheet subform tied to the

tblPurchaseDetails, to record items purchased. When I save the Purchase
entry, I also need to modify another table that tracks current inventory and
the latest Cost for the components.

I am trying to open a recordset using an SQL statement on

tblPurchaseDetails , so that I can limit the number of rows in the recordset
to those of the current purchase.

Here is the code I have been using:

Dim numPurchaseID As Long
numPurchaseID = Me.PurchaseID.Value

Dim cnn As Connection

Dim rstThisPurchase As New ADODB.Recordset

Dim txtSelect As String

txtSelect = "SELECT tblPurchaseDetail.PurchaseID,
tblPurchaseDetail.ComponentType,tblPurchaseDetail.ComponentID,
tblPurchaseDetail.Number, tblPurchaseDetail.Cost FROM tblPurchaseDetail
WHERE PurchaseID = numPurchaseID;"


Try:

txtSelect = "SELECT tblPurchaseDetail.PurchaseID,
tblPurchaseDetail.ComponentType,tblPurchaseDetail.ComponentID,
tblPurchaseDetail.Number, tblPurchaseDetail.Cost FROM tblPurchaseDetail
WHERE PurchaseID = " & numPurchaseID