By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Recordset object closing unexpectedly after operation

P: 11
I have this following code snippet in Access 2007.

Expand|Select|Wrap|Line Numbers
  1. strTempQuery = "qry_Resource_ID_By_Name"
  2. Set dbs = CurrentDb()
  3. Set qdf = dbs.QueryDefs(strTempQuery)
  4. qdf.Parameters(0) = lstResourceProposed.Column(0, cnt)
  5. qdf.Parameters(0) = lstResourceProposed.Column(1, cnt)
  6. Set rst = qdf.OpenRecordset()
  7. If Not rst.BOF And Not rst.EOF And rst.Fields.Count = 1 Then
  8.        iProposedResourceID = rst.Fields("Resource ID")
  9. End If
  10.  
Here, "qry_Resource_ID_By_Name" is defined as
Expand|Select|Wrap|Line Numbers
  1. SELECT Resource_ID AS [Resource ID]
  2. FROM tbl_Resource_Master
  3. WHERE Resource_Last_Name+Resource_First_Name=[?];
  4.  
The problem is that
Expand|Select|Wrap|Line Numbers
  1. If Not rst.BOF And Not rst.EOF And rst.Fields.Count = 1 Then
is failing as
Expand|Select|Wrap|Line Numbers
  1. rst.BOF
and
Expand|Select|Wrap|Line Numbers
  1. rst.EOF
are ivaluated as "True". However,
Expand|Select|Wrap|Line Numbers
  1. rst.Fields(0).Name
shows "Resource ID".

I am clueless. Any help please?
Mar 20 '10 #1
Share this Question
Share on Google+
3 Replies


patjones
Expert 100+
P: 931
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

Expand|Select|Wrap|Line Numbers
  1. qdf.Parameters(0) = lstResourceProposed.Column(0, cnt)
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [prmSomeValue] datatype;
  2. SELECT Resource_ID AS [Resource ID]
  3. FROM tbl_Resource_Master
  4. WHERE Resource_Last_Name+Resource_First_Name=[prmSomeValue];

Then you would assign the parameter a value as such:

Expand|Select|Wrap|Line Numbers
  1. 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
Mar 20 '10 #2

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

Pat, the correct code was
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 20 '10 #3

patjones
Expert 100+
P: 931
You're welcome! Any time.
Mar 20 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.