Detail Format code takes forever...

[RipperT]

I have the following code in the format section of a report that sets a
label's caption based on whether or not a record shows up in a query
(upon which the report is NOT based). It takes forever. Is there a
faster way to do this? (Access 2000)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If DCount("InmateId", "qryMPRI_current", "InmateId = '" &
Me.InmateId & "'") <> 0 Then
Me.lblMPRI.Caption = "MPRI"
Else
Me.lblMPRI.Caption = ""
End If
End Sub

Many thanx,

Ripper

 

[Gary Walter]

I have the following code in the format section of a report that sets a
label's caption based on whether or not a record shows up in a query
(upon which the report is NOT based). It takes forever. Is there a
faster way to do this? (Access 2000)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If DCount("InmateId", "qryMPRI_current", "InmateId = '" &
Me.InmateId & "'") <> 0 Then
Me.lblMPRI.Caption = "MPRI"
Else
Me.lblMPRI.Caption = ""
End If
End Sub

I imagine it would be possible to run
distinct query of qryMPRI_current

qryDistinctCurrentInmates

SELECT
DISTINCT
"MPRI" As MPRICaption,
InmateID
FROM
qryMPRI_current;

Then left join "report query" to above query
to use for report....

SELECT
R.*,
D.MPRICaption
FROM
qryReport As R
LEFT JOIN
qryDistinctCurrentInmates As D
ON
R.InmateID = D.InmateID

 

[Allen Browne]

Almost anything is faster than code in the report events in A2000 and later.

First thing is to set the property only if necessary, since setting a
property is slower than reading it.

A better solution would be to use a text box, with control source of:
=IIf(DCount(...) = 0, "", "MPRI")

To find out if there is a record or not:
=IsNull(DLookup(...))
might be quicker than DCount().

There's an ELookup() here:
http://allenbrowne.com/ser-42.html
that is almost twice as fast as DLookup().

If that's still too slow, it may be possible to use a subquery in the
report's query. Sometimes that's a good idea; other times it is slower
(depending whether Access runs it for every record before aggregation), or
triggers a "multi-level group by" error message (depending on the report's
Sorting And Grouping.) If you want to investiage it, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[RipperT]

Thanx, Allen.

Almost anything is faster than code in the report events in A2000 and
later.

First thing is to set the property only if necessary, since setting a
property is slower than reading it.

A better solution would be to use a text box, with control source of:
=IIf(DCount(...) = 0, "", "MPRI")

To find out if there is a record or not:
=IsNull(DLookup(...))
might be quicker than DCount().

There's an ELookup() here:
http://allenbrowne.com/ser-42.html
that is almost twice as fast as DLookup().

If that's still too slow, it may be possible to use a subquery in the
report's query. Sometimes that's a good idea; other times it is slower
(depending whether Access runs it for every record before aggregation), or
triggers a "multi-level group by" error message (depending on the report's
Sorting And Grouping.) If you want to investiage it, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066