Problems with opening a recordset

[LittleSmudge]

Running Access 2000 and using ADO

I have a stored query that looks at an open form to provide data for the WHERE clause like this:-

"SELECT CorrId FROM tblCorr WHERE LSERef = [Forms]![frmCorrFind]![LSERef]"

frmCorrFind is an Unbound form.

With the form open and valid data in the LSERef control the query can be opened from the Database window and it displays the correct record.
This query is saved as qryCorrIdFromLSERef


In the frmCorrFind!LSERef.AfterUpdate Event I want to be able to count the number of records that match so I open a recordset :-

rst defined using ADO

rst.Open "SELECT * FROM qryCorrIdFromLSERef"

etc..


The code fails on this rst.Open line with
"No value given for one or more required parameters"


I've looked at this for far too long and I cann't see the wood for the trees anymore.
I know it's simple - but can anyone show me where I'm going wrong ?



It is NOT a miss spelling of qryCorrIdFromLSERef - becuase that gives a completely different error.

If I remove the WHERE clause from the query it returns all of the records and opens fine.

So why can't I open the recordset on a query that contains a WHERE clause ?


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[nfell]

Hi,

I think you can solve the problem by changin your code to -

rst.Open "SELECT CorrId FROM tblCorr WHERE LSERef = " & [Forms]![frmCorrFind]![LSERef]

With this method you dont need the query.

Hope it helps

 

[LittleSmudge]

Yes nfell I COULD

Only the query is going to be converted to be a PassThrough query on a MySQL database & as tblCorr is very large I'm looking to take advantage of the CLient Server structure.
By putting the WHERE in the PassThrough query the work gets done in the MySQL db and only the selected records are transmitted down the wire.

If I use
rst.Open "SELECT CorrId FROM tblCorr WHERE LSERef = " & [Forms]![frmCorrFind]![LSERef]
then the whole table has to be sent down the wire so that JET can conduct the WHERE filtering.


Hence my problem.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.