VLOOKUP in Access

[SanjaySingh]

I want to put a Vlookup formulae in Access Form. So when i feed a Customer Code in One Field it automatically feed other fields like customer name etc..

[ArviLaanemets]

Look for DLookup in help!

[Micron]

Seems to me you'd want a form combo box that allows you to choose the customer code from its list. When that happens, you use the AfterUpdate event of the combo to requery the form, which is based on a query that uses the combo box results as criteria. Then the rest of the controls display the data based on the customer code.

There is no VLookup or HLookup in Access. DLookup can only return data from one field in one record in a table or query. It cannot populate the rest of your form, unless you want to put that function in every control, which would not be the way to go.

[June7]

Is purpose of this control to input criteria to search for CustomerCode and filter the form? This must be an UNBOUND control and as Micron points out, code would perform this action.

If purpose of this control is to enter value into a new record and therefore is bound to a field, use combobox with a RowSource that retrieves multiple fields - this is a multi-column combobox. Then textboxes can reference columns of the combobox to display info of the selected item.

[CodeLiftSleep]

Why not just write VBA to handle the "OnChanged" event to run a SQL Query like

"SELECT * FROM Customers WHERE CUSTOMERCODE  = " & textBox.Value

and then populate the other fields from the returned recordset?

Alternatively you could use the TextBox.Value to do a DLookup for the each of the other fields as well.

[pbaldy]

Another option which avoids extra trips to the table:

BaldyWeb - Autofill

[CodeLiftSleep]

Another option which avoids extra trips to the table:

BaldyWeb - Autofill

He can also download the necessary info to a variant array(s) and then search those instead of the table which would be a lot quicker as well.