[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

need help pulling data from 3 tables

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!
1 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.  


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.


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now