Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Create a text box with control source

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

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

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

    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.
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thank you so much!!!

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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 ...?

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    Thanks,

    Christa

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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>post 401925</post#> for instructions.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  10. #10
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

    <code>On_Hand: Nz([SumOfQuantity_Received],0)-Nz([SumOfQuantity_Taken],0)</code>

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

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

  12. #12
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Vancouver, Canada
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    You are a lifesaver!!! Thanks!

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •