dlookup form

[DD]

i have a form in which i have a field i type in an item number. if the item
number exist i have another field that displays the description of the item
number using a dlookup from another table. after i type in the item number,
nothing seems to be displayed in the dlookup description box. if i come
back to that record, the desription is populated. why isnt it displayed as
soon as i type in the item number.

a second problem that is related to this form, i would like a msgbox to be
displayed if the item number isnt found in the table. i basically said if
the description is null after i type in item number, then display the
message box, "item not found". this isnt working. i wasnt sure if the
description box would be considered null if the dlookup doesnt find
anything. Is there some other arguement i could use.

thanks

 

[Jeff Boyce]

If you are using a text box and forcing your users to type in an item
number, you (and Access) are working harder than you need to.

Consider using a combobox instead. You can select your "other table"
(actually, a query against that other table) as the row source for your
combobox. Your user can begin typing an item number (if they actually think
of things by item number) or begin typing the item's name (most folks
remember the name of things before they remember the number assigned to the
thing).

In the AfterUpdate event of the combobox, you can fill other textboxes in
the form with values that your combobox's query returns, using something
like:
Me!txtSomeOtherField = Me!cboYourItemComboBox.Column(n)
where "n" is the n-1-th column of data in your combobox's query (.Column ()
is zero-based).

You can also use the combobox's NotInList event to manage what happens if
the user enters a value not in your "other table".

Regards

Jeff Boyce
Microsoft Office/Access MVP