Query the MS Access table for the id entered in textbox

Question

I have two tables Order and Customer.

In the order form, if I enter the value for the CustId field, I need the value for CustDesc to come automatically with reference to 'Customer' table.

The remaining fields in the 'Orders' form, I have mapped to the fields in the order form directly. I am stuck only in mapping to the 'Customer.CustDesc' to Orders.CustDesc. Because I dont know how to query like below

select custdesc from Customer where [custId= the value of CustId in Orderform]

I am new to MS Access form. pls help.

Answer 1

You can use the lookup function:

= DLookup("[custdesc],"[Customer]", "[custId] =[Forms]![Orderform]![CustId]")

• In VBA on the AfterUpdate event:

(in orderform) CustId_AfterUpdate

[Forms]![Orderform]![CustDesc] = Nz(DLookup("[custdesc],"[Customer]", "[custId] =[Forms]![Orderform]![CustId]"))

- By setting the control source property of the desired field to the same statement:

= DLookup("[custdesc],"[Customer]", "[custId] =[Forms]![Orderform]![CustId]")

Answer 2

It might be easier to use a query that joins both tables as your recordsource for the form instead of your orders table. That way you don't need to come up with this kind of construct to find one other value specifically.

Query:

SELECT O.*, C.custdec as CustomerDesc
FROM Order O left join Customer C
ON O.custId = C.custId

If you also need to be able to edit/add data to those tables from this form, you'll need to set the RecordsetType property to 'dynaset'.

Another option would be the use of subforms (customer data in the main form, data from different orders in the subform), but that depends on what the form's intended purpose is.