Trying to Use Dlookup to Autofill a field in a Form

[sandydaly]

Hello Everyone! I'm fairly new to the Access world and I am looking for some help!



I have a form called "Inventory Data Entry Form". On the form I have a an a field "Item number" and a field "Item Description".
I built a table called "Item-Master" that contains all the item numbers and Item descriptions. The Item description Field is named Description in the Item_Master Table.

I need the Item description to autofill or auto populate from my Item_Master table when the user enters the item number in the form.

I have tried without success to use the Dlookup function. But I'm not sure that I am using it properly or in the right place on the properties box. I have also attempted to write a VBA code which did not work either.

Here is the basic Idea of what I need


=DLookup("[Item Description]", "Item_Master", "Item Number"= "Item Number"=[Invenotry Data Entry Form])

So I want to look up the item description in the Item_Master Table with the criteria of the Item number on the inventory data entry form = the Item Number in the Item_Master table and return that value in the Item Description field on the Inventory Data Entry form.

Any help is GREATLY appreciated.

Thanks,
Sandy

[ranman256]

you dont really need DLOOKUP, if you use a combo box to pick ITEM#,
fill in the description, using what gets picked.
The combo box would have 2 columns, 1 for the item# , col2 for descrip.
just grab it off the combo. NOTE: in VB, columns begin with zero. so:

sub cboItem_afterupdate()
  txtDescription = cboItem.column(1)   'col 2 numerically.
end sub

This method prevents another read to the database, since you already have the answer in the combo.

But, the where clause must be outside the quotes and you must put brackets around multi word fields. (dont use spaces in fieldnames):
=DLookup("[Item Description]", "Item_Master", "[Item Number]"= "[Item Number]=" txtbox)

[sandydaly]

Great! Thank you! I will try the Dlookup expression you gave me.

I would have used a combo box but the item number list contains 1,200 item numbers. Too many for a combo box.

Where would I put the Expression? In the Data properties under the control source of the Item number field or the Item description field?

[aytee111]

That number of records can easily be handled by a combobox. However, if you don't want to use one then here is a better option than Dlookup: http://allenbrowne.com/ser-32.html