Multiple text criterias in dlookup (and performance issue)

  • Thread starter Thread starter vavroom
  • Start date Start date
V

vavroom

I'm having a problem with a DlookUp and I can't seem to find a
solution to it. I've searched the board here, and the "web", but
can't seem to get it going. I do hope someone can help.

I am selecting a person's name in a list box. From that name, I want
the appropriate alias to be displayed in a textbox on the same form.
Seems straightforward enough, but it's not quite working.

The data from the list box comes from tblPeople. The data for the
alias comes from a table named [Global Address List]. I cannot modify
the second table. I figured the best way to match was to retrieve the
Last name and First name from tblPeople, and do a dlookup with a
multiple text criteria to provide the relevant Alias. In other words:

lstNameSelect has three columns: fldPeopleID, fldFName and fldLName,
with the first column being bound.

I have one textbox with a dlookup to retrieve the Last Name based on
fldPeopleID, and another to retrieve the First Name. (note, I realise I
*could* build it with the multiple dlookups nested one into another,
but I'm not comfortable with doing this at this point, I don't know
nearly enough. First, get how I want to do it going, then see to
improve!)

Then, I have a textbox with another dlookup that looks like this:
=DLookup("Alias", "[Global Address List]", "[Last]='" &
Forms!frmInfo![txtLName] & "'" And "[First]='" &
Forms!frmInfo![txtFName] & "'")

This invariably returns the first record of the table instead of the
"right" record. What am I doing wrong?

As an aside, I'm working with an address book that has over 5,000
records, and it seems that looking up the info is slower than molasses
in winter. With the info I've provided, can anyone suggest ways to
improve speed?

Thanks in advance for any information and ideas.
 
Try taking out the " on each side of the AND statement. I don't think you
need them, there, and you are effectively ending your statement before
including the First Name criteria. Like this:

=DLookup("Alias", "[Global Address List]", "[Last]='" &
Forms!frmInfo![txtLName] & "' And [First]='" &
Forms!frmInfo![txtFName] & "'")


As for db speed, take a look at your field indexes.

Sharkbyte
 
Thanks for the prompt answer Sharkbyte.

Alas, your suggestion re skipping the " in the dlookup didn't work :(

As for field indexes, no index setup in the [Global Address List], but
that's a linked table I have no way to modify, using the MS Exchange
address book for the organisation :( So it may just be that I have no
way to improve, if I can't get it indexed, eh?
 
How about building a string to capture the first and last name?

strSQL =

you can then put a message box to see what your returning.

I would suggest opening the table via a recordset. The DLookup is
known to be slow.
 
Just a thought. I have sometimes found it clearer to simply build a
query that contains all of the necessary criteria, and then do the
dlookup using the query name as the domain.

Ron
 
Warrior, I would really not know how to do what you suggest, it might
work, but since I don't know how to go about it, I won't bang my head
against the wall ;)

Ron, using your idea modified some, I'm trying something, which is not
working, but I think it *should*. What do you think of this?

I created a query that always return just one record, with one field,
based on the first name and last name of the person selected in my
list. That query works fine (and is notably faster than the dlookup),
if I run the query alone.

But if I try to call that query in a text box's controlsource, it's not
happy and gives me #name?

Hmmmmm
 
I do not believe that scenario will work. I attempted it here and got
the same results. If I do a dlookup on it (which are not the fastest
things in the world) I can get it to work.

If the form is bound to that query, then the txt box to that field it
works.

I do not believe I have ever gotten the first type of arrangement to
work.

I have also gone so far as to put a hidden subform on the form and used
the results from that to load the information. The same is true for a
bound hidden form and then having the source be that field on the
hidden form/subform.

And also for a linked form that had only a few records that would be
selected I have also modified the linked query to simply have that
additional query as part of the tables but not linked to anything else.
Then it is available for each and every record. Messy and would make a
real sharp Access designer cry, but for the infrequent use it gets the
job done.

Just Ideas

Ron
 
Back
Top