Populating ADO recordset from SQL statement

[jade]

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

--jad

 

[Gerald Stanley]

The error message is likely to be about the SQL statement
and I would advise checking that out first. In particular,
check out the WHERE clause. If numPurchaseId is a variable
in your procedure, then the SQL should be
txtSelect = "SELECT tblPurchaseDetail.PurchaseID,
tblPurchaseDetail.ComponentType,tblPurchaseDetail.ComponentID,
tblPurchaseDetail.Number, tblPurchaseDetail.Cost FROM
tblPurchaseDetail WHERE PurchaseID = " & numPurchaseID & ";"

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
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;"

Set cnn = CurrentProject.Connection

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

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.