dlookup alternative?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

r - 05 Aug 2005 00:58 GMT

I have a form with a subform, and need to pull in calculations from
unrelated tables and queries. In one case I use dlookup to pull in the sum
of all checks paid against a job. In another I use it to pull in the
descriptions for job codes. There are a couple other uses. However, it
takes a really long time for these fields to display because there are so
many records to sift and filter through.

I tried making a query and using the ID on the open form to filter out what
I need. I can see the data in the query, but when I pull it into an unbound
control, it just displays #Name?

Any more efficient alternatives out there that I can learn about?

Reply to this Message

MacDermott - 05 Aug 2005 02:13 GMT

How about a subform based on the query?
Leave out the criteria for the ID, but use the ID field as the
LinkMasterField and LinkChildField.

> I have a form with a subform, and need to pull in calculations from
> unrelated tables and queries. In one case I use dlookup to pull in the sum
[quoted text clipped - 8 lines]
>
> Any more efficient alternatives out there that I can learn about?

Reply to this Message

Allen Browne - 05 Aug 2005 02:51 GMT

Reply embedded.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a form with a subform, and need to pull in calculations from
> unrelated tables and queries. In one case I use dlookup to pull in the
> sum
> of all checks paid against a job.

A DSum() probably is the best solution here. A subquery or grouping would
give a read-only result, which is probably not what you want.

> In another I use it to pull in the
> descriptions for job codes. There are a couple other uses.

The description for the job code probably is related to the code in the
form. If so, you may be able to create a query using the main table and the
Job Code table, and use that as the source for your form. This lets you get
the Descrip field from the JobCode table directly into the form.

There are a couple of traps when doing that. Firstly, if the job code is not
a required field, you need to use an outer join in the query or you'll miss
the records where job code is null. If that's new, see:
http://allenbrowne.com/casu-02.html

Secondly, make sure that none of the fields in the JobCode table have a
Default Value set. If they do, when you try to enter a new record Access
will (wrongly) try to assign the default value to the lookup table, and the
new entry will fail.

> However, it
> takes a really long time for these fields to display because there are so
> many records to sift and filter through.

The DSum() will still be slow, but the outer join query should make a world
of difference.

> I tried making a query and using the ID on the open form to filter out
> what
[quoted text clipped - 3 lines]
>
> Any more efficient alternatives out there that I can learn about?

This subform uses the LinkMasterFields/LinkChildFields to show just the
records in the main form? If so, you don't need to also refer to the main
form in the Criteria of the query.

The other standard solutions include:
- Make sure you have relationships defined with Referential integrity
enforced.

- Make sure all relations are on fields of the same data type and size.

- Make sure you have indexes on the fields used for criteria or sorting.

- Make sure the SubdatasheetName property is set to No in all tables
(Properties box in table design view.)

- Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General.

- Don't use the Like operator if = will do, and never use the Like operator
with number or date fields (i.e. text fields only.)

Reply to this Message