need help pulling data from 3 tables

Posted on 2006-05-01
Last Modified: 2008-03-06
To give you some background with my requirement,   i have an access database that works great.   We issue purchase orders and receive equipment against the Purchase Orders.   However we do not know what part numbers we will be receiving until the equipment arrives.  Sometimes its 1 line item  other times its 30 line items each with different qtys.   I need the user to be able to pull up a PO#, cross ref a part# to a description, and fill in the QTY that has been received for that particular po#.                                                      I am trying to create a form that pulls data from 3 tables, and also captures new data from the user.     The new form is called "Pre_Inventory"             I need to pull information from 3 tables.          The 1st table is "Puchases"    from this table i need the field "PO_number" .       The second table is "Part_Number_Ref"      from this table I need the fields, "Part_Number"  and    "Description"          The 3rd table is  "Pre_Inventory"  which has fields PO_Number, Part_Number, Description and Quantity.       I need the 3rd table to collect the data from the Purchases and Part_Number_Ref, and at the same time the user needs to manually enter the QTY of each line item.    The idea is that the PO_number doesn't have to be typed in for every line item, once the user types it in the 1st time, it auto-populates every next record that is used.  Also once the user cross refs the Part_Number and Desciption, I need Part_Number and Desciption and po# all to appear on one record in my form.   Please advise the best way to have a form that can accomplish this for me.              thanks for your help!
Question by:cbrichs
    LVL 3

    Accepted Solution

    I'm sure there is a solution to what you are looking for, but I'd like to clarify what you're trying to accomplish.  It seems to me that from your description, you're looking to have the Pre_Inventory table as a subform on a form bound to the Purchases Table.  So, in other words, at the top of your form, you've got all the information about one certain PO.   On that form a user will want to enter in various repeatable items for that PO, which will come from the Part_Number_Ref.   This detail information will be stored in a table called Pre_Inventory which will also include the Quantity for each part on that PO.  

    Is that about correct?  

    If so, then here are the steps.  1) Create a bound form to the Purchases table.  Have all the fields up at the top for the user to fill out.  

    In the center of the form, put a subform linked to the Pre_Inventory table.  This form will need to have a field linked to the PO Form, for this you'll use the PO Number, which should also be a field in the Pre_inventory form so you can link the two.  
    Also, this subform will be in Datasheet form, so you can enter line items.  Finally, for the field, Part_Number (which is in your Pre_Inventory Table), you'll create a combo box in the subform.  You may already know this, but don't use this field to store the actual part name, but instead use it to store the part ID.  For this combo box, you can use a query to pull the part name and description.  If you're not familiar with how to do this, let me know and I'll walk you through it, but the combo box wizard should give you a good idea.
    Finally, have a quantity field in the Pre_inventory Subform to store this information.  

    Hopefully this wasn't too confusing.  


    LVL 20

    Expert Comment

    I would create a global variable....
    Global glbPONumber as string

    Next I would create a function to retreive this value....

    Function get_glbPONumber() as string
    get_glbPONumber = glbPONumber
    End Function

    At the beginning of a transaction - before the first data entry form, clear this global variable.....

    glbPONumber = ""

    As a form appears, retreive the current value of glbPONumber (if exists).

    Me.PONumber = get_glbPONumber()

    If it doesn't exist - no problem - the function will return an empty string.  If it does exist, the current value of glbPONumber will populate the box.

    After each form close, populate the variable (glbPONumber) with the current value of the field in the form.

    glbPONumber = me.PONumber.

    How this will work is that until the user enters ANY value in the PO NUMBER text box (of any form) the box(es) will be blank.  But once the user enters ANY PO Number, the system will remember the value and populate all subsequent forms with this value - until you 'turn it off' ........   glbPONumber = ""

    Scott C.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now