Is using DLookup or opening a recordset faster?

[Seth Schrock]

I have a hyper link. In its OnClick event, I want to find the record in the form that I just clicked on and find out if a certain field is populated. If it is, then I will open form 1. If not, then I will open form 2. I see two ways to do this.
1. Open a recordset, use the .FindFirst method to go to the required record and then test for the !FieldName value and compare it to ""

2. Use DLookup to get the value out of the field for the record I clicked and compare it to ""

Which way uses less system resources when there could be thousands of records?

[Stewart Ross]

Hi Seth. In the circumstances you mention I'd use DLookup. The domain aggregate functions such as DLookup, DSum etc do in one simple call what otherwise takes a fair bit of programming to achieve.

For performance reasons use of domain aggregate functions inside loops would not be advised, but in the circumstances you describe you would not find use of resources an issue with either approach. DLookup is therefore a much simpler approach than using, say RecordsetClone, Findfirst and so on (or simply looping through the recordset concerned).

-Stewart

[TheSmileyCoder]

If the recordset is the same as the one you are using in your ACTIVE form, then I would use the recordsetclone. I had a bit of trouble figuring that out from your question. If its not a recordset you allready have open then I would use Dlookup.

However as Stewart says, the resource use of a single Dlookup is not that grand, especially if it is used on a indexed field.

[Seth Schrock]

The recordset wouldn't be open already as I'm needing to determine which form to open. So I'll use the DLookup.

[NeoPa]

Seth:
I want to find the record in the form that I just clicked on

I'm confused as to how this doesn't mean you already have the record available. If you do, then it makes more sense to me to reference the field via the current record on the form. IE. Not .RecordsetClone, nor even .Recordset, but Me.Fields.{Fiel dName}.

Of course, if it isn't available there then DLookup() on a single occasion, especially with operator interaction, is generally not noticeable, and preferable to opening a new recordset yourself as that would be done with many lines of code, each of which needing to be interpreted before use. Not a big deal by any means, but DLookup() wouldn't require even that much extra.

[Seth Schrock]

I have a form that lists the "Active" records in my database. It provides just a few fields to make the record more identifiable. I have the PK field included and hyperlinked so that when I click on it, will open a form that contains most of the fields. I now have a second form based on the same table that represents a different stage of processing of the records. The field that I'm testing for in the DLookup() is the field that determines which form to open. So when I click on the hyperlink, it needs to first determine which form to open and then open the form to the record I just clicked on.

Does that make more sense?

[NeoPa]

It makes sense in a way Seth, but unless the first form is designed on a query that omits the relevant field from that same table, that field will also be available on the first form, even if it isn't displayed anywhere in a control.

PS. I do recommend you use the correct terms for your posts, as controls are not fields. All fields are available, that exist in the RecordSource. Only the controls which are defined show the related field values on your form.

[Seth Schrock]

At the moment, the deciding field is omitted from the query, but that doesn't mean that it couldn't be. Hmmmm..... Definitely an idea to think about.

Correct once again on the control/field usage. And I even knew that one and still used it incorrectly. I will definitely try to be more careful in the future. Thanks NeoPa.

[NeoPa]

Seth:
At the moment, the deciding field is omitted from the query, but that doesn't mean that it couldn't be.

It is quite common to have a control defined on a form that is either not visible, or in a section that is not visible, specifically for handling such hidden values. Fields are accessible, but hidden controls make it a fair bit easier. I set a particular colour background for any section I use that way, which means I can easily recognise what it's there for.

[Seth Schrock]

Well, that is what I ending up doing and it works great. Thanks for the idea NeoPa.

[NeoPa]

I'm glad that was helpful Seth :-)

It wasn't exactly the answer to the question asked, but in your circumstances probably the best fit. Stewart's answer is still most relevant to the question.