Forms & DLookUp - performance?

[millwheal]

I have inherited a database which uses a practice that I'm not sure about.

Normally, when I create a Form, I set up a Table or Query as the source, and ensure that all the fields on the Form are contained in the source object.

The inherited database has several Forms where a dozen or more fields have a 'DLookUp' statement as their source. Some of the 'DLookUp' statements refer to the same Table, but there are still as many as eight or more Tables accessed in this way.

The database is a single file, used by up to a dozen users over a local network, using Access 2003.

Without getting into the 'split database' argument, does anyone have any comments about the efficiency [and hence response times] of having such Forms?

millwheal

 

[boblarson]

The database is a single file, used by up to a dozen users over a local network, using Access 2003.

Without getting into the 'split database' argument, does anyone have any comments about the efficiency [and hence response times] of having such Forms?

DLookups (and actually aggregate functions of any kind) are slow, very slow and so it is good to not use many of them, if at all possible. It is faster to even open a recordset object and retrieve the information that way. Allen Browne on his site has a replacement for DLookup called ELookup which uses a recordset to get the information.

Now, sorry but I must -

ANY database that is multiuser NEEDS, not should, but NEEDS to be split and a copy of the frontend on each user's machine. That is a sheer necessity and if you don't have it that way then you are playing Russian Roulette with your database. It isn't a matter of IF you will get a corrupt database file, but only of when.

 

[millwheal]

Bob,

Many thanks for your prompt reply; I'll certainly look at ELookUp.

As to the 'split database' argument, I got within a whisker of getting agreement to split the file. However, as the most senior [and most knowledgeable] user is about to retire, my users decided they would rather not change "at the moment" ... which probably means never.

Since I am retired myself and now only work for them on an ad hoc basis [no Access expertise in the internal IT Department], I can't really insist. I will pass on your words of wisdom.

millwheal

 

[spikepl]

Be careful changing things - Memo-fields in some queries only deliver the first 256 characters, which could be the reason for having a seemingly redundant separate Dlookup on a form.

 

[millwheal]

I have now located and copied 'ELookup', but am having problems with it.

I used it as a straightforward substitute for 'DLookUp', but get an error message [the same one] on every field where I have used it.

The statement which causes the error is:

Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)

The error is "3061 Too few parameters. Expected 1."

I copied the Function from: http://allenbrowne.com/ser-42.html and do have a DAO Library reference included, as directed.

All the fields are straighforward, being simple 'lookups' from a table, with specific selection criteria and all work with 'DLookUp'.

Any suggestions as to the cause would be welcome.

millwheal

 

[DCrake]

That error usually indicates a mispelt fieldname in your syntax.

 

[boblarson]

1. How are you calling the function? What is the actual function call you are using look like?

2. Do you have spaces or special characters in the names of your field that you are passing to the function? If so, you need to pass the field name with square brackets.

 

[millwheal]

David,

Thanks for the input, but the syntax is OK; I've simply put ELookUp in place of DLookUp statements in the fields on the form.

Bob,

In answer to your questions, first see above.

There are 18 fields on the form which contain an [now] ELookUp statement; all of them give the 3061 error.

A typical statement is:

=ELookUp("Province","T_Provinces","ProvID=ProvNo")

I'm aware of the potential problems with spaces in object names. There are none in any of the object names in the 18 statements.

None of the fields gave a problem as DLookUp statements; it is only since changing to ELookUp that the problems occur.

millwheal

 

[boblarson]

This

=ELookUp("Province","T_Provinces","ProvID=ProvNo")

Should be like this:


=ELookUp("Province","T_Provinces","ProvID=" & [ProvNo])

You need to concatenate in the field instead of putting it inside of the quotes so that the VALUE will be passed as the function will not be able to evaluate what ProvNo is.

 

[Galaxiom]

Separate DLookup (or ELookup) control sources are opening individual recordsets for each control. This approach will always be slower than using a single query to get values for multiple controls. Changing to individual ELookups is just tinkering with a fundamentally flawed strategy.

Multiple queries may be required. This can be handled quite efficiently by grouping the related controls into subforms with a suitable recordsource query for each and using bound controls. The controls can be made to look as though they are on the main form by removing the border from the subformcontrol and all the selector and navigation features from the subform object itself.

Another way is to open the minimum possible number recordsets and use code to load the values into the controls.

 

[millwheal]

Dear GalaxiomAtHome,

I am well aware that the forms are unlikely to be efficient; as I said in my original post, it is not the way I would do things, but I have inherited the situation. There are several forms with up to 18 'DLookUp' fields and I was hoping for something more efficient as a quick fix. The source query already has around 10 tables feeding it and the relationships between the form and the other [DLookUp] tables are not straightforward.

Dear Bob Larson,

Thank you for the syntactical suggestion. Your syntax only works where the field content is numerical [although not defined as a number], but following your suggestion I changed the syntax for non-numeric fields to something like:

=ELookUp("Province","T_Provinces","ProvID= '" & [ProvNo] & "'")

... which worked.

By now, bearing in mind I had quite a number of forms to 'fix', I was getting a long way from a 'quick fix'. When I then discovered that 'ELookUp' didn't handle null fields well, I did find a way of overcoming that and did complete a trial with one form.

However, with so many forms and so many 'DLookUp' fields, each one requiring an examination of the field [numeric or non-numeric] and a less-than-straightforward syntax change for each one to account for null fields, I've decided to abandon the search for a quick fix and instead examine a better long-term solution ... when I have the time.

Thanks for your help with this.

 

[gemma-the-husky]

Multiple dlookups indicate maybe a different "type" of form to a normal form.

First, a dlookup is by definition non-updateable - so a form with many dlookups is not a normal form.

eg, I use a KPI form, which draws together data from many aspects of the system, and presents it as a series of text boxes, populated by dlookups.

 

[chege]

I have DLOOKUP Issues

I don't know if this is the right place to post my question but i have problem with MS Access 2003 DLOOKUP problem.

I have got a table with the following fields:

ptLName = text
ptFName = text
ptMName= text
ptOPNo = text

I have a combo box on a form called cboSelPts. Every time the combo box is updated i would like the DLOOKUP to populate a text box (txtDetails) with lastname (ptLName, ptFName, ptMName) of the underlying table.

The code looks like the following. When i change the combo box with ptOPNo nothing happens to the txtDetails text box. Any idea where i am going wrong?

Thank you.

Private Sub cboSelPts_AfterUpdate()
'populate the text box
Me.txtDetails = DLookup("ptLName", "ptFName","ptMName","tblResultstemp", "[ptOPNo]= " & "'cboSelPts &'")
End Sub

Chege