Using more than one table/query in a form (Access XP)

Topic

New Reply

In the attached database, I would like to have the quantity on hand (On_Hand field in the On Hand query) appear on the ‘Used’ form. The quantity on hand should correspond to the description the user chooses from the Description drop down list. I can’t seem to make this work. Can anyone help me?
Thanks in advance,
Christa

Reply | Quote

Replies

Create a text box with control source

=DLookUp("On_Hand","On Hand","Item=" & Chr(34) & [Description] & Chr(34))

Reply | Quote

Thank you so much!!!

Reply | Quote

This morning I copied the formula you send into the Control source of a new text box and the quantity on hand came up…it worked perfectly!…however since then I’ve added some macros to open forms and a switchboard…and now it no longer works. I haven’t changed any of the fields that the On Hand query refers to…any ideas?

Thanks,

Christa

Reply | Quote

Whether the expression works shouldn’t have anything to do with macros and a switchboard form. Do you get an error message, or do you see #Name, or #Value, or …?

Reply | Quote

I don’t get any error messages…the field just remains blank.

Thanks,

Christa

Reply | Quote

I don’t think we can solve this without seeing the database. Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

A stripped down version of the database is attached. Thanks for all your help.

Reply | Quote

I’ve figured it out…the formula works as long as there is already a record for the particular item in the Used table. If there is no record in the Used table for the item, the quantity on hand doesn’t show eventhough there is a quantity on hand in the Receipts table. I would like it to just pull the total number from the On Hand query no matter what records are in either table (Receipts or Used). Can this be done?

Thanks,

Christa

Reply | Quote

Change the definition of the On Hand field in the On Hand query to

On_Hand: Nz([SumOfQuantity_Received],0)-Nz([SumOfQuantity_Taken],0)

Change the control source of the On Hand text box on the Used form to

=Nz(DLookUp("On_Hand","On Hand","Item=" & Chr(34) & [Description] & Chr(34)),0)

Reply | Quote

You are a lifesaver!!! Thanks!

Reply | Quote

The NZ function is very useful in some situations. You can use it to replace a Null (blank, missing) result with a non-Null value. In this example, we use 0, but it could be anything you like.

Reply | Quote

I think you need to refine your table structure to make this work.

Your inventory table does not have a primary key defined – There is no unique identifier of an item such as an item code or autonumber.

You should have such a field, then anywhere else in the db that you refer to an item, you refer to it by this field. So the Used table is presumably talking about inventory items used, but you have no way of knowing what was used because it uses just the Category and Description which do not clearly identify exactly which item was used. So the used table needs to include the ItemID field that you need to add to the inventory table.

You inventoryt able includes a quantity field. Does this represent quantity on hand? If not what is it? If you really need to keep using the Items used query then this needs to also include the ItemID field to identify the item.

Reply | Quote