dynaset recordset

[placek]

Hello

I have a problem and keep banging my head against the wall
trying to figure out the solution, but with no success.
Basically, i have 4 tables:

* tblBorrowerRelation has fields lngBorrowerNumberCnt(PK)
and strBorrowerName;
* tblLoanRelation has fields lngBorrowerNumberCnt (FK),
lngAcquisitionNumberCnt (FK), dtmDateBorrowed and
dtmDateReserved;
* tblAcquisitionRelation has fields
lngAcquisitionNumberCnt (PK), strISBN (FK) and
dtmDateAcquired, and
* tblBookRelation has fields strISBN (PK), strTitle and
strAuthor.

I want to enter a Borrower Number into a input box and
populate a recordset accordingly. The code i have come up
with is below.

Dim i As Integer
Dim d As Database
Dim r As Recordset

i = InputBox("Enter the Borrower Number")
Set d = DBEngine.Workspaces(0).Databases(0)
Set r = d.OpenRecordset("SELECT
tblLoanRelation.lngBorrowerNumberCnt,
tblLoanRelation.lngAcquisitionNumberCnt,
tblBookRelation.strISBN, tblBookRelation.strTitle,
tblBookRelation.strAuthor,
tblLoanRelation.dtmDateReserved,
tblLoanRelation.dtmDateBorrowed FROM
tblAcquisitionRelation, tblBookRelation, tblLoanRelation
WHERE
tblLoanRelation.lngAcquisitionNumberCnt=tblAcquisitionRelat
ion.lngAcquisitionNumberCnt AND
tblAcquisitionRelation.strISBN = tblBookRelation.strISBN
AND tblLoanRelation.lngBorrowerNumberCnt=i")


Stop

However, when i run the code i get a 3061 run-time error
that says 'Too few parameters. Expected 1.' Can anybody
please point me to the error of my ways.

Martin

 

[Marshall Barton]

Basically, i have 4 tables:

* tblBorrowerRelation has fields lngBorrowerNumberCnt(PK)
and strBorrowerName;
* tblLoanRelation has fields lngBorrowerNumberCnt (FK),
lngAcquisitionNumberCnt (FK), dtmDateBorrowed and
dtmDateReserved;
* tblAcquisitionRelation has fields
lngAcquisitionNumberCnt (PK), strISBN (FK) and
dtmDateAcquired, and
* tblBookRelation has fields strISBN (PK), strTitle and
strAuthor.

I want to enter a Borrower Number into a input box and
populate a recordset accordingly. The code i have come up
with is below.

Dim i As Integer
Dim d As Database
Dim r As Recordset

i = InputBox("Enter the Borrower Number")
Set d = DBEngine.Workspaces(0).Databases(0)
Set r = d.OpenRecordset("SELECT
tblLoanRelation.lngBorrowerNumberCnt,
tblLoanRelation.lngAcquisitionNumberCnt,
tblBookRelation.strISBN, tblBookRelation.strTitle,
tblBookRelation.strAuthor,
tblLoanRelation.dtmDateReserved,
tblLoanRelation.dtmDateBorrowed FROM
tblAcquisitionRelation, tblBookRelation, tblLoanRelation
WHERE
tblLoanRelation.lngAcquisitionNumberCnt=tblAcquisitionRelat
ion.lngAcquisitionNumberCnt AND
tblAcquisitionRelation.strISBN = tblBookRelation.strISBN
AND tblLoanRelation.lngBorrowerNumberCnt=i")


Stop

However, when i run the code i get a 3061 run-time error
that says 'Too few parameters. Expected 1.' Can anybody
please point me to the error of my ways.

You have to use the value of i, not its name in the query:

AND tblLoanRelation.lngBorrowerNumberCnt=" & i)

 

[Guest]

thanks

-----Original Message-----



You have to use the value of i, not its name in the query:

AND tblLoanRelation.lngBorrowerNumberCnt=" & i)