none
form parameter RRS feed

  • Question

  • Why I can't pass the value from a form field to an updater query by using the format: [Forms]![FormName]![FieldName]?
    Saturday, December 1, 2018 9:23 PM

All replies

  • Is the form open when you run the query?
    Saturday, December 1, 2018 9:26 PM
  • be sure that [Forms]![FormName]![FieldName]  is entered into the Parameter area of the Query Design View.

    to do that, when in Query Design View, with your cursor in the upper portion of the display - right click and look for 'Parameters'


    Sunday, December 2, 2018 3:08 PM
  • what is the expression for a subform field?
    Sunday, December 2, 2018 5:11 PM
  • Forms!MainformName.Subformname.form!subformControlName

    Sunday, December 2, 2018 8:45 PM
  • I follow this format:

    [Forms]![Order].[OrderSub].[Form]![ItemID]

    It's still asking me for the parameter value.

    Sunday, December 2, 2018 10:59 PM
  • Are you sure that OrderSub is the name of the subform control, i.e. the control in the parent form's Controls collection which houses the subform, rather than the name of its source form object?

    Ken Sheridan, Stafford, England

    Sunday, December 2, 2018 11:07 PM
  • is the sub form in single view or is it continuous.....??

    this can't work in continuous because there are multiple records with the same subform control name.....

    -if single view - be sure that syntax is also put in Parameters of the query design

    Sunday, December 2, 2018 11:09 PM
  • the sub form is not a single view. How should I do it.
    Monday, December 3, 2018 12:15 AM
  • Where can I find the subform control name? thanks
    Monday, December 3, 2018 12:16 AM
  • a 'control' is any object you drag onto a form: field, combobox, button, etc.  In this case it probably is referring to a field....

    in the form's property - one can select Continuous or Single view

    Monday, December 3, 2018 12:40 AM
  • I figured it out by using the following format would work: [Forms]![Order].[OrderLine].[Form]![ItemID]

    but it won't work if I move the cursor to next row of the sub form.

    Another question is if I input multiple itemid in the subform, how to pass all these value to the update query?

    Thanks!

    Monday, December 3, 2018 2:00 AM
  • You will need to execute the UPDATE query either independently for each row in the subform - usually this would be in the AfterUpdate or AfterInsert event procedure of the subform, i.e. of the source form object, or  by basing the UPDATE query on the subform's table, restricting the query to those rows which reference the primary key of the parent form's current record.

    How is the UPDATE query currently being executed, and what is its SQL statement?

    A more fundamental issue is whether the UPDATE query is necessary in the first place.  It might be introducing redundancy, e.g. by updating an inventory table, with the consequent risk of update anomalies, rather than computing the values from the base data.

    Ken Sheridan, Stafford, England




    • Edited by Ken Sheridan Monday, December 3, 2018 1:15 PM Expanded.
    Monday, December 3, 2018 1:06 PM
  • Here is my update query, it works well for one selected row but it won't work if I move the cursor to the next row in the subform.

    UPDATE Merchandise SET Merchandise.OnHand = [OnHand]-[Forms]![Order].[OrderLine].[Form]![Quantity]
    WHERE (((Merchandise.ItemID)=[Forms]![Order].[OrderLine].[Form]![ItemID]));

    My currently questions is how to get the multiple itemids from the subform and pass them to the query to do the update.

    Thanks!

    Monday, December 3, 2018 3:24 PM
  • You have not said how you are executing the UPDATE query.  As I said before, I'd expect this to be in the subform's module.  Doing so in the subform's AfterInsert event procedure, which only executes if a new row has been inserted in the subform, will adjust the current stock level accordingly, but doing so in the AfterUpdate event procedure in the case of an existing order line will double-count the quantity, resulting in an incorrect OnHand value unless adjusted by subtracting the Quantity value in the control from the previous value, if any.  For example:

    Current quantity on hand:             100
    Previous Quantity in  order line:      10
    Amended Quantity in order line:        5

    Adjusted current quantity on hand:    100 + 10 - 5 = 105

    This assumes only the quantity is changed.  If, on the other hand, the execution of the AfterUpdate event procedure results from a change of the item sold, then the adjustment would require the OnHand quantity for the previous item to be increased and the OnHand quantity for the new item to be decreased, requiring the execution of two UPDATE statements.

    To cater for this in an UPDATE query the SQL statement would normally be built and executed in the code, rather than executing a saved querydef object.

    However the need for such adjustments is avoided if the stock on hand value is not stored at a column position in a table, but computed when required by subtracting the sum of all movements  out of stock per item (sales orders and other disposals) from the sum of all movements into stock per item (purchase orders).  Additionally account needs to be taken of discrepancies between physical stock levels and computed stock levels resulting from stock takes.

    You might like to take a look at Inventory.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the basics of inventory management.  An operational inventory management database, for which commercial products are available, is much more complex, however.

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Monday, December 3, 2018 4:30 PM Typo corrected.
    Monday, December 3, 2018 4:26 PM