Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    lookup list and show fields (XP)

    Maybe this should be easy but I can't think of how to do this - I have two tabs on my Entry Form which are Vendors and Contracts (they have separate tables). We would like to fill in the vendor table and the contract table information ahead of time, then when the users are filling out PO information they can click on the vendor tab and look in a pulldown list to find a vendor to add, and then all the vendor information would show. Same thing with the contracts. I can't figure out how to have them choose from the list, have all the rest of the fields fill in, and then still be connected just to that PO Number. Hopefully this is understandable. Thanks for any help. I will include the database.
    Attached Files Attached Files

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

    Re: lookup list and show fields (XP)

    I don't understand the setup. You have a POPrimary field in tblVendors. This would seem to imply that each vendor is associated with a unique purchose order (but there could be several vendors associated with the same purchase order). Is that really what you want? I would expect that each vendor could be involved in several purchase orders.

  3. #3
    New Lounger
    Join Date
    Aug 2001
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup list and show fields (XP)

    You are right. Each PO could have many different vendors and contract numbers. I messed it up trying to figure it out.

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

    Re: lookup list and show fields (XP)

    You have a many-to-many relationship between POs and vendors. This is implemented using an intermediate table with two fields: the PO ID and the Vendor ID. This table contains a separate record for each PO - Vendor combination.

    See <post:=364,203>post 364,203</post:> for a sample database demonstrating how to use such an intermediate table in a main form - subform setup.

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup list and show fields (XP)

    Thank you Hans. I finally got my subforms working on the tabs I set up for Vendors and Contracts. The only info showing is the one field. So now my question is how can I show the rest of the info for each vendor? Is it even possible in this setup? On one hand I don't see how it could show all that info for each vendor when you are picking more than one vendor per PO, but then on the other hand, it seems like maybe it could be listed next to it? I tried using a list box but I couldn't get it to work, and I know the combo box will only show one field. Is there a way to connect the rest of the fields to this?

    Also, I'm having another problem on the main form. I have a table with Qty Received, and under that a table with Qty Issued. Under that, there is a table that is supposed to say the Total in Stock, which takes Qty Received - Qty Issued. It works if I fill in a Qty received, then right away fill in Qty Issued, then the calcuation works. But if I have a Qty Received, but nothing is issued, it doesn't put in the total in Qty Received as it should. Can you help?
    Attached Files Attached Files

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

    Re: lookup list and show fields (XP)

    See the attached version. I created a query based on the selected vendors and list of vendors tables, and used the query as record source for the subform. When you select a vendor from the combo box, the other data (I added three fields only) will be filled in automatically.

    I'll leave your second question for others to answer, it's outside my field.

  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup list and show fields (XP)

    Hi
    Thank you for all your help on this. I don't see the attachment though.

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

    Re: lookup list and show fields (XP)

    Oops, sorry about that. Here it is.
    Attached Files Attached Files

  9. #9
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup list and show fields (XP)

    Hans, thank you so much for all the help. I have this is good shape now - I never would have gotten this far without you.

  10. #10
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup list and show fields (XP)

    The only thing that isn't working now is my calculation, if anyone could help. I mentioned it earlier and here's what it is:

    I have a table with Qty Received (based on it's own query), and under that a table with Qty Issued (based on another query). Under that, there is a table that is supposed to say the Total in Stock (third query), which takes Qty Received - Qty Issued. It works if I fill in a Qty received, then right away fill in Qty Issued, then the calcuation works. But if I have a Qty Received, but nothing is issued, it doesn't put in the total in Qty Received as it should. I put an Iif statement in the "Total in Stock" query and it seems to work, but when I enter into the forms it doesn't update correctly.

    Thanks for any help.

  11. #11
    Star Lounger
    Join Date
    Feb 2003
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup list and show fields (XP)

    I forgot to attach the database with the calculations. Here it is.
    Attached Files Attached Files

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

    Re: lookup list and show fields (XP)

    Reposted in <post:=648,887>post 648,887</post:>. To avoid duplication, this thread is locked. Please post all replies in the other thread.

Posting Permissions

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