Recordset run time problems

[TonyB]

Hi,
I'm trying out using recordsets for the first time, and I'm having a couple
of problems with the syntax in runtime.
The code is
Dim db As Database
Dim rs As Recordset
Dim sqlstr As String
Set db = CurrentDb
sqlstr = "SELECT * FROM tblQuoteDetails WHERE [quoteID] = tbquoteID"
Set rs = db.OpenRecordset(sqlstr, dbOpenDynaset)

tbQuote is the value of a control on the form calling this vba sub
At this point I get a runtime error 'Too Few parameters, expected 1'

By debugging and assigning tbQuoteID to a test integer variable, I know
tbQuoteID = 5 which is a valid value for the table/field
If I change the sqlstr line to replace tbQuoteID by 5 it runs OK.

Any ideas ?

The other problem I have is that if I use 5 in the openrecordset, and run
further down the sub, I get to a line
rs!Price = DLookup([Price UK], "tblProductInfo", [ProductID] =
rs!ProductID)

Ths fails with a runtime error 2465, "Access can't find field '|' referred
to in your expression"

From the local window, rs!ProductID has the expected value (1 in this case),
rs!Price is in the recordset as can be seen in the locals window.
[ProductID] is a field in "tblProductInfo" as is [Price UK]

What am I doing wrong here ?
TIA
Tony

 

[Naresh Nichani MVP]

Hi:

Try
sqlstr = "SELECT * FROM tblQuoteDetails WHERE [quoteID] = " & tbquoteID

The tbQuoteId should be outside the quotes

Regards,

Naresh Nichani
Microsoft Access MVP

Hi,
I'm trying out using recordsets for the first time, and I'm having a couple
of problems with the syntax in runtime.
The code is
Dim db As Database
Dim rs As Recordset
Dim sqlstr As String
Set db = CurrentDb
sqlstr = "SELECT * FROM tblQuoteDetails WHERE [quoteID] = tbquoteID"
Set rs = db.OpenRecordset(sqlstr, dbOpenDynaset)

tbQuote is the value of a control on the form calling this vba sub
At this point I get a runtime error 'Too Few parameters, expected 1'

By debugging and assigning tbQuoteID to a test integer variable, I know
tbQuoteID = 5 which is a valid value for the table/field
If I change the sqlstr line to replace tbQuoteID by 5 it runs OK.

Any ideas ?

The other problem I have is that if I use 5 in the openrecordset, and run
further down the sub, I get to a line
rs!Price = DLookup([Price UK], "tblProductInfo", [ProductID] =
rs!ProductID)

Ths fails with a runtime error 2465, "Access can't find field '|' referred
to in your expression"

From the local window, rs!ProductID has the expected value (1 in this case),
rs!Price is in the recordset as can be seen in the locals window.
[ProductID] is a field in "tblProductInfo" as is [Price UK]

What am I doing wrong here ?
TIA
Tony

 

[TonyB]

Hi Naresh,
Thanks, I just couldn't see that.
I thought that would fix the other problem as I had got the string wrong
there too. I corrected that and now I have the line
rs!Price = DLookup("[Price (US)]", "tblProductInfo", "[ProductID] = " &
rs!ProductID)

This now produces the following Run-time error '2001': You cancelled the
previous operation.

rs is the recordset. From the locals window, rs!ProductID has the expected
value (1 in this
case), rs!Price is in the recordset as can be seen in the locals window.
[ProductID] is a field in "tblProductInfo" as is [Price UK]

Is there a conflict between the dlookup function and having a recordset open
? dlookup is operating on different table to the recordset so I can't see
why this should be so !
Can anyone tell me what this means ?
Thanks
Tony

Hi:

Try
sqlstr = "SELECT * FROM tblQuoteDetails WHERE [quoteID] = " & tbquoteID

The tbQuoteId should be outside the quotes

Regards,

Naresh Nichani
Microsoft Access MVP

The other problem I have is that if I use 5 in the openrecordset, and run
further down the sub, I get to a line
rs!Price = DLookup([Price UK], "tblProductInfo", [ProductID] =
rs!ProductID)

Ths fails with a runtime error 2465, "Access can't find field '|' referred
to in your expression"

From the local window, rs!ProductID has the expected value (1 in this case),
rs!Price is in the recordset as can be seen in the locals window.
[ProductID] is a field in "tblProductInfo" as is [Price UK]

What am I doing wrong here ?
TIA
Tony