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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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
In the case of "lookup" values, the best solution is to change the RecordSource query of the form to include a join to the lookup table.  The join should be a left join to cater to the situation where there is no value entered in the foreign key.  So, for example, an Invoice would show customer information.  We don't duplicate the customer name and address on each invoice, those attributes are saved in the customer record.  To show them on the invoice form, create a query that joins the invoice table to the customer table (using a left join).

WARNING:  When you show lookup fields on a form this way, they are updateable and that is dangerous because someone might think the change only affects the record they are looking at.  Best practice would be to set the locked property to true for the lookup fields to prevent accidental updating.

The sample below shows three ways of showing data on a form (the fourth item on the menu).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thao-nhiAuthor Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

thao-nhiAuthor Commented:
If Dlook up is a more suitable solution for this purpose, please give step by step instructions. Thanks
Rey Obrero (Capricorn1)Commented:
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 & "'")
thao-nhiAuthor Commented:
I would use this statement =dlookup("NameOfField","TablerOrQueryName","ItemID=" & Forms!NameOfForm.ItemID) with the maco builder right?
thao-nhiAuthor Commented:
table name = Item on Order
Fieldname1 = Qty, number data type
Fieldname2 = Status, text data type
Rey Obrero (Capricorn1)Commented:
what is the name of the table? name of the form?
thao-nhiAuthor Commented:
table name = tbl_Item_on_Order
Form = frm_Product_Details
Rey Obrero (Capricorn1)Commented:
=dlookup("Qty","tbl_Item_on_Order","ItemID=" & Forms!frm_Product_Details.ItemID)

=dlookup("Status","tbl_Item_on_Order","ItemID=" & Forms!frm_Product_Details.ItemID)
thao-nhiAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.