Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2006

    Pull Parameter Value from Form

    I want to use a query for a subform that I'm making. The main form has a part number, and the subform will be a calculation of the item costs requried to make that particular part.

    My idea is to use a parameter query taht will look up item costs based on the part number. Is there a way to run the query from the form, for the subform, and have the parameter automatically be equal to the part ID that is currently called up on the main form?

    Please note that when the main form is intially called up, the subform that I'm trying to run the query is on a tabbed page that will not be queried until after the user clicks on the tab. This way the query will always have a value to use as it's parameter, if I can figure out how to make it look there.

    Thanks in advance, and feel free to ask any questions if I havent been specific enough.

  2. #2
    Join Date
    Dec 2006

    Recent Progress

    I've made some progress with this situation, but I still have two unsolved issues. I have a subform on my main form that is based off a query. This query pulls various quantity and pricing information from a number of different tables.

    My first question is this: How do I set criteria in the query based upon whatever record the form is currently on?

    My second question is this: How do I check the value of a list box and pull pricing information on the related table based on which value is set in the list box?

    Here's how I have things set up at the moment (I apologize, this is a rather lengthy post, but if I can get this one figured out it will come in extremely valuable in many, many other situations elsewhere).

    There are three tables I am using:
    tblParts (PartID as an autonumber Key), which has a child:
    tblPartItems (PartItemID as an autonumber Key) which has a child:
    tblSMatConfig (SMatConfigID as an autnumber Key)

    tblParts addresses the current part assembly as a whole.

    tblPartItems contains information on individual component pieces and the quatities of them that are required to make a single parent part.

    tblSMatConfig contains information on various different ways that the Part Items can be obtained, and the cost of the Part Item.

    Any given Part can have any number of Part Items, but each Part Item will only have one Standard Material Configuration being used, though it may have many to choose from.

    My query is set up to look up all of the Part Items for a given Part, get the number of Part Items required for each Part ([tblPartItems]![QtyPerParent]), and then get the cost of the PartItem per the currently selected SMatConfigID ([tblSMatConfig]![SMatConfigID]). Lastly I have a calculated (CostPer) field that multiplies the QtyPerParent by the StdPrice ([tblSMatConfig]![StdPrice]).

    In my frmParts I have a subform that is based off of this query (qryPartItemsByPart). In the footer of this subform is a Sum of the CostPerAssy field in the query.

    This works exactly as I would like it to, which two exceptions.
    1) When I open my form, I get a diaglog asking for the PartID. When I type in the PartID for the part that I want, it will then show me all of the records that I require. I would like the query to pull that value from the current PartID on the form that has been opened (or navigated).
    2) I currently only have 1 Material Configuration, and my quesry is setup to find that price based on the PartItemID, rather than on the chosen configuration per my list box in my tblPartItems. With only one configuration, this works out very well, but if I have more than one, the query will tally the totals for each configuration, which will not do at all. I need to make the query pull only the price from the selected configuration.

    (I swear, I'm almost done....)

    Here is my SQL layout:

    SELECT tblParts.PartID, tblPartItems.PartID, tblPartItems.Description, tblPartItems.QtyPerParent, tblSMatConfig.SMatDesc, tblSMatConfig.PartItemID, tblSMatConfig.StdPrice, tblPartItems!QtyPerParent*tblSMatConfig!StdPrice AS CostPer
    FROM tblParts INNER JOIN (tblPartItems INNER JOIN tblSMatConfig ON tblPartItems.PartItemID = tblSMatConfig.PartItemID) ON tblParts.PartID = tblPartItems.PartID
    WHERE (((tblParts.PartID)=[Me]![PartID]));

    There's my case. Again, I apologize for the length of this post, as I'm sure many people will read halfway through and curse me before moving on the the next post without finishing it, but I offer many, many MANY thanks to the one person that will (hopefully) read through it and provide me with an answer. As I read many posts where I can't understand what people are trying to accomplish based on an ill-defined scenario, I am only trying to be thorough to avoid confusion.

    Thanks again,

    For the record, I am totally self taught in access, and have only been using it for a few months. My guess is that this is an easy fix, its just a matter of me not knowing all of my options yet. Preferably, I would like to be able to do this using VBA (I tend to think that using VB for many calculations rather than cluttering my GUI is a more efficient route, and easier for me to follow). Though my VBA abilities are slightly less than my Access abilities, I feel that I have enough of a basic understanding to follow an explanation were it given through code.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Enter Parameter Value
    By plesser in forum Access
    Replies: 1
    Last Post: 11-08-2008, 10:27 AM
  2. Replies: 1
    Last Post: 02-14-2007, 07:27 PM
  3. Date Parameter
    By shakira in forum Queries
    Replies: 2
    Last Post: 03-09-2006, 12:55 PM
  4. Replies: 4
    Last Post: 01-05-2006, 02:36 AM
  5. Add combo box to parameter query
    By louisa14 in forum Queries
    Replies: 1
    Last Post: 12-10-2005, 08:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums