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
thao-nhiAsked:
Who is Participating?
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
0
PatHartmanCommented:
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).
FillFormFields130127.zip
0

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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.