count vs recordset

  • Thread starter Thread starter edward
  • Start date Start date
E

edward

Surely this is a common situation, but I haven't been able to find a
discussion of it.

I'm processing in the click event procedure of a command control. I'm
going to open another form based on user inputs from the form. But if
the user input results in no records found, I want to display a MsgBox
instead of the form.

I can do that (and have done that) by using DCount with the same
criteria that I will pass to OpenForm.

But this query can sometimes be slow -- many seconds, even a minute or
two. When I do it this way, is Access running the query twice? Or is it
smart enough to keep the RecordSet from the first query lying around
and reuse it?

Is there a better way to handle this?

My first thought was to create a RecordSet, check if it's empty, and
pass it to the form. But my searching indicates that this lies
somewhere between difficult and impossible, especially since I'm using
ADO (due to possibly needing to move the back end in the future).

Edward
 
Surely this is a common situation, but I haven't been able to find a
discussion of it.

I'm processing in the click event procedure of a command control. I'm
going to open another form based on user inputs from the form. But if
the user input results in no records found, I want to display a MsgBox
instead of the form.

I can do that (and have done that) by using DCount with the same
criteria that I will pass to OpenForm.

But this query can sometimes be slow -- many seconds, even a minute or
two. When I do it this way, is Access running the query twice? Or is it
smart enough to keep the RecordSet from the first query lying around
and reuse it?

Is there a better way to handle this?
Click to expand...


Yes, DCount will run the query an extra time, but the
tables should be cached when the form opens so that should
be somewhat faster than the DCount.

I think another approach should be substantially quicker.
Open the form and let the form cancel itself if there's no
data.

Try using this in the form's Open event:

Cancel = (Me.RecordsetClone.RecordCount = 0)

If that works, the procedure with the OpenForm method should
get a 2501 error, so you'll want to trap that and put up
your message box.
 
Thanks, Marsh. I even found additional useful info on the topic in one
of your posts from last year. ;-)

Only one niggling thing still has me bothered. My code is working, but
it seems there should be an easier way to say "I don't handle this
error, pass it to the caller". My code is

On Error Resume Next
DoCmd.OpenForm stDocName, , , stLinkCriteria
ErrNum = Err.Number
If ErrNum = 2501 Then
MsgBox "Did not find any job with this job number " & _
"(and date if entered)"
ElseIf ErrNum <> 0 Then
ErrSource = Err.Source
ErrDescription = Err.Description
On Error GoTo 0
Err.Raise ErrNum, ErrSource, ErrDescription
End If

Like I say, it works. (Tested by intentionally raising an error in the
form's Open.) But it seems I should be able to write something on the
order of

ElseIf ErrNum <> 0 then
Err.KickItUpstairs
End If

but I can't find it. Of course, I suppose I should remind myself that
although the year is 2006, the language is still Basic ... evolved to
be sure, much of it unrecognizable yet still showing ugly remnants of
the Basic that I learned in 1968 ... I think it was my fourth
programming language, back then in the Dark Ages.

Edward
 
Thanks, Marsh. I even found additional useful info on the topic in one
of your posts from last year. ;-)

Only one niggling thing still has me bothered. My code is working, but
it seems there should be an easier way to say "I don't handle this
error, pass it to the caller". My code is

On Error Resume Next
DoCmd.OpenForm stDocName, , , stLinkCriteria
ErrNum = Err.Number
If ErrNum = 2501 Then
MsgBox "Did not find any job with this job number " & _
"(and date if entered)"
ElseIf ErrNum <> 0 Then
ErrSource = Err.Source
ErrDescription = Err.Description
On Error GoTo 0
Err.Raise ErrNum, ErrSource, ErrDescription
End If

Like I say, it works. (Tested by intentionally raising an error in the
form's Open.) But it seems I should be able to write something on the
order of

ElseIf ErrNum <> 0 then
Err.KickItUpstairs
End If

but I can't find it. Of course, I suppose I should remind myself that
although the year is 2006, the language is still Basic ... evolved to
be sure, much of it unrecognizable yet still showing ugly remnants of
the Basic that I learned in 1968 ... I think it was my fourth
programming language, back then in the Dark Ages.
Click to expand...


I don't think so. Your KickItUp procedure would need
arguments for the error number, etc. so there would be
little gain as far as coding is concerned (other than
changing the name from Raise to KickItUp).

Note that you can shorten your code by skipping the
intermediate variables:

ElseIf ErrNum <> 0 Then
On Error GoTo 0
Err.Raise ErrNum, Err.Source, Err.Description
End If

Seems like that's as short as can be and don't forget that
each procedure has its own error environment so KickItUp's
Raise would just send the error back to your above code.
 
Marshall said:
I don't think so. Your KickItUp procedure would need
arguments for the error number, etc. so there would be
little gain as far as coding is concerned (other than
changing the name from Raise to KickItUp).
Click to expand...

Well, the idea was to say "pretend that I didn't have this error handler
and continue with processing at a higher level, saving all the information
already stored about the error". Now, maybe VB stores so little information
about errors that raising err with number, source, and description amounts
to the same thing. I'm used getting a lot more information about errors.
Note that you can shorten your code by skipping the
intermediate variables:

ElseIf ErrNum <> 0 Then
On Error GoTo 0
Err.Raise ErrNum, Err.Source, Err.Description
End If
Click to expand...

Doesn't work. The resulting message is blank. That's why I coded it that
way. I suspect that all the properties of Err get cleared by the "on error
go to 0", but I haven't tried to pin down the details.

Edward
 
Back
Top