Using Dlookup in a form

[M0E-lnx]

Hello forum... New user here

I have a form which I want to link to 2 tables

Sort of like an order management database, but this is for purchases, not sales, so the top part of the form will contain information that comes from a table called "Vendors".
The bottom part of the form contains information that will come from another table called "Parts"

The top is fine.. just fill it in. The bottom is what I need help with

Table "Parts" is populated with a long list of data that is sorted out like this
[ID] | [PartNumber] | [Description] | [Price] | [U/M]

I want to be able to select or type the part number, and I want it to find that part number in the table called "Parts" and fill in the Description and possibly the Price and UM fields automagically.
Been trying dlookup for this, but I can't get it to work right


Can anyone lend me a hand here?
is Dlookup what I need, or is there another way to do this?

Thanks in advance

 

[boblarson]

Why not just use a combo box to select it. You can set the rowsource to include all of the fields and then in the combo's AfterUpdate event you can update the other controls by something like:

Me.YourDescriptionTextBoxNameHere = Me.YourComboName.Column(x)

where x is the number of the column (zero-based) that the field you want is in.

 

[M0E-lnx]

I do have comboboxes there... but instead of running through all the fields, I want to be able to type in or select a part number, and then have it fill in the rest of the information.
Not sure if I'm explaining it right....

 

[boblarson]

I don't think you understood me. You can include more fields in your combo box (but not display them) and then use those to fill the text boxes, instead of using extra computing resources to use DLookups. See this sample to see what I mean:
http://downloads.btabdevelopment.com/Samples/combos/Sample-FillInFields.zip

 

[M0E-lnx]

Ok... I may have definitely explained this the wrong way.

I see what you did in your sample. Makes sense. But in my situation, my form has a subform embedded into it.
It really just looks like a table. So I guess if there is a way to make the same functionality you just did work on a table, I could use that.

I have attached my current db maybe it will help you understand what I need it to do
Open the form called FrmPoRecords

 

[boblarson]

Okay, I'll be working through this as there are several issues. The first is that you are using lookup fields at table level, which is a big NOOOOOOOO, DON'T DO IT!!!!!! (see here for why: http://www.mvps.org/access/lookupfields.htm )

There is no reason why you can't do what I was explaining, and I'll try to modify your stuff to show you how and why.

 

[M0E-lnx]

Thank you... I'm reading the link you pointed me to..