DLookup vs SQL query as the best and fastest performance

[Bresart]

Hi, i have a report that must hide a Header section depending on the value of a field that isn't in the recordsource table of the report.

It must be done several times, therefore the way of doing it must be the one with the lowest resources consumption.

Which one of the two options:

1.- varX = DLookup("[NombreCompañía]", "CompañíasEnvíos", "[IdCompañíaEnvíos] = 1")

2.- strQuery = "SELECT tblName.fieldName....WHERE..."
Set rrdset = New ADODB.recordSet
rrdset.Open strQuery, CurrentProject.Connection, _
adOpenStatic, , adCmdUnknown
varX = rrdset!fieldName


would be the best in that sense? Any other way?

Thanks in advance.

 

[pbaldy]

Generally recordsets are more efficient, though there are numerous replacement Lookup functions that can be used, such as:

http://allenbrowne.com/ser-42.html

http://www.mdbmakers.com/forums/showthread.php?t=4589

However, if the criteria is hardcoded as in your sample, it would be more efficient to set a report level variable when the report opens, then use that variable. That way you only perform the lookup once.

 

[Bresart]

Thanks pbaldy.

"That way you only perform the lookup once." <--- The lookup must be done for each record in the report, because the Where condition in the code gives as the result of the select query the current record. In that sense, the lookup can't be performed once but several times.

So i will try with recordsets.

 

[dhookom]

If the same record and field are consistent for the entire report then I would add the field/table(s) to the report's record source and add criteria to filter it down to a single record. This will have no effect on the records in the report other than adding the same value/field to every record.

I would think this would be more efficient than a domain aggregate function or code.

Duane MS Access MVP

 

[Bresart]

Thanks dhookom. I have tried reading the data (it's in a subreport field):

If IsNull(Me.SecControl.Report.FieldName.Value) = True then

Else

End if



and when this field is empty it gives the error You have introduced an expresion with no value.

 

[dhookom]

Is the field empty or does the subreport not contain any records? There is a huge difference between no records and a null field.

Duane MS Access MVP

 

[Bresart]

Yes, specifically there's no records in the subform.

 

[dhookom]

You need to find out if the subreport has data like:

If Me.SecControl.Report.HasData Then 
 If IsNull(Me.SecControl.Report.FieldName.Value) = True then

    Else

 End if 
End If

Duane MS Access MVP

 

[Bresart]

Thanks. It works.

The code in the OnPrint event of Group2Header is:

If Me.SecControl.Report.HasData Then
If IsNull(Me.SecControl.Report.RefGrCul.Value) = True Then
Me.Group1Header.visible = False
Else
Me.Group1Header.visible = True
End If
Else
Me.Group1Header.visible = False
End If


If a record without data in the Group1Header is followed by another record (only one, which is the last record) with data, this second record neither is shown. But the previous records to that one, which have data, are shown.

And, if after the record without data there's more than one record with data, all them are shown.

Thanks for any help given.

 

[Bresart]

With the recordset:

strQuery = "SELECT tblName.fieldName....WHERE..."
Set rrdset = New ADODB.recordSet
rrdset.Open strQuery, CurrentProject.Connection, _
adOpenStatic, , adCmdUnknown
varX = rrdset!fieldName


it works when the headers are the main report. But the fail happens when the report with the headers is a subreport, which also have a thrid child report in which data is looked for.


With


If Me.SecControl.Report.HasData Then
If IsNull(Me.SecControl.Report.FieldName.Value) = True then

Else

End if
End If


it fails in both cases, main and child report.

The fail i refer to is the one mentioned in the previous post.

 

[dhookom]

I am totally lost. I don't know if you are even asking a question in your last two posts.

If you want something to happen in your main report based on something in your subreport, you might be able to put the "something in your subreport" into the record source of your main report. I would need to have an understanding of your report and subreport record source as well as the link master/child properties and what determines stuff.

Duane MS Access MVP

 

[Bresart]

I have solved it by moving the code from the OnPrint event to the On Format event.

Thanks.