Recordset object closing unexpectedly after operation

[nilanjangm]

I have this following code snippet in Access 2007.

strTempQuery = "qry_Resource_ID_By_Name"
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs(strTempQuery)
qdf.Parameters(0) = lstResourceProposed.Column(0, cnt)
qdf.Parameters(0) = lstResourceProposed.Column(1, cnt)
Set rst = qdf.OpenRecordset()
If Not rst.BOF And Not rst.EOF And rst.Fields.Count = 1 Then
       iProposedResourceID = rst.Fields("Resource ID")
End If
 

Here, "qry_Resource_ID_By_Name" is defined as

SELECT Resource_ID AS [Resource ID]
FROM tbl_Resource_Master
WHERE Resource_Last_Name+Resource_First_Name=[?];
 

The problem is that

If Not rst.BOF And Not rst.EOF And rst.Fields.Count = 1 Then

is failing as

rst.BOF

and

rst.EOF

are ivaluated as "True". However,

rst.Fields(0).Name

shows "Resource ID".

I am clueless. Any help please?

[patjones]

Hi -

The fact that rst.Fields(0).Name shows the proper name that you assigned in the SQL just means that the recordset was in fact created; it doesn't say anything about whether the recordset actually has anything in it.

The first thing I would try is very simple, and that is to use rst.MoveFirst after opening the recordset. Sometimes this step is needed in order to actually populate it before use.

The second thing I would look at is how the query is set up. It may not be generating any records. I notice, in particular, that

qdf.Parameters(0) = lstResourceProposed.Column(0, cnt)
qdf.Parameters(0) = lstResourceProposed.Column(1, cnt)

is making an assignment to the same thing twice. Secondly, when I write parameterized SQL, I like to name the parameters explicitly, because using "?" notation doesn't always work out the way you want it to. In your case, you could do something like:

PARAMETERS [prmSomeValue] datatype;
SELECT Resource_ID AS [Resource ID]
FROM tbl_Resource_Master
WHERE Resource_Last_Name+Resource_First_Name=[prmSomeValue];

Then you would assign the parameter a value as such:

qdf.Parameters![prmSomeValue] = SomeOtherValue

where you set datatype in the PARAMETERS list according to whatever kind of data it is, and "SomeOtherValue" is just whatever you are trying to pass in to the query. You can do this for multiple parameters, as long as they are all listed and properly typed in the PARAMETERS clause of the SQL string.

Let me know how it works out.

Pat

[nilanjangm]

Well, it's solved. And it was really stupid to do a programming mistake like this. :)

Pat, the correct code was

qdf.Parameters(0) = lstResourceProposed.Column(0, cnt) & lstResourceProposed.Column(1, cnt)

And this was the reason that no value was being fetched from the table, therefore, the recordset was understandably empty.

Pat, thank you for pointing out, and I will take the suggestion of naming the parameter as this will of course be a good practice as well.

[patjones]

You're welcome! Any time.