Link to home
Start Free TrialLog in
Avatar of thao-nhi
thao-nhi

asked on

How to display record pull from another table

I have a form that pull records from 1 table. I want to add text boxes that will show results pulled from another table/query based on ItemID in the current form.

thanks
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

you can do this using the dlookup() function, but this is inefficient.

if the two tables are related, it will be best to use a Main Form/Subform.

see how to do this here
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thao-nhi
thao-nhi

ASKER

Both examples are great but what I want is very simple: Pulling 1 or 2 fields from another table or query to show only total Qty of that  item from incoming shipment . The item details are already on the form populated from a table or query. In short, the form shows  what I now have. I need to see how many more are coming in. For this purpose, a subform or another from is more than what is needed. Is there a way to show that additional data on the same form? Thanks
If Dlook up is a more suitable solution for this purpose, please give step by step instructions. Thanks
what is the name of the table/query and names of fields you need to pull?

if ItemID is Number Data Type, use this
=dlookup("NameOfField","TablerOrQueryName","ItemID=" & Forms!NameOfForm.ItemID)

if ItemID is Text Data Type, use this
=dlookup("NameOfField","TablerOrQueryName","ItemID='" & Forms!NameOfForm.ItemID & "'")
I would use this statement =dlookup("NameOfField","TablerOrQueryName","ItemID=" & Forms!NameOfForm.ItemID) with the maco builder right?
table name = Item on Order
Fieldname1 = Qty, number data type
Fieldname2 = Status, text data type
what is the name of the table? name of the form?
table name = tbl_Item_on_Order
Form = frm_Product_Details
=dlookup("Qty","tbl_Item_on_Order","ItemID=" & Forms!frm_Product_Details.ItemID)


=dlookup("Status","tbl_Item_on_Order","ItemID=" & Forms!frm_Product_Details.ItemID)
I have a syntax error on the 1st statement when putting it in control source for the textbox
The sample database I posted shows exactly how to to use DLookup() to populate a form field.  It is labeled as the "bad" example because there are two other better, more efficient methods.