none
input on grouped level RRS feed

  • Question

  • i have a query with totals. now i want a form to input a number related to the grouped level of the query

    what is the way to do this ?

    Tuesday, July 17, 2018 11:23 AM

All replies

  • i have a query with totals. now i want a form to input a number related to the grouped level of the query

    what is the way to do this ?

    Hi tekoko,

    A form with an unbound control for the input?

    Imb.

    Tuesday, July 17, 2018 12:34 PM
  • bound or unbound , which one to choose ?
    Tuesday, July 17, 2018 12:39 PM
  • bound or unbound , which one to choose ?

    Hi tekoko,

    Bound controls are "bound" to a field in a recordset. An input control that is "related to the grouped level of the query" would thus be unbound.

    The difference between bound and unbound controls is that bound controls are automatically filled from the fields, and the fields are automatically updated from the controls (if the control is editable). With unbound controls you can use code to get the effects you want.

    Imb.

    Tuesday, July 17, 2018 1:29 PM
  • Queries that contain totals cannot be edited and therefore you cannot enter data into them directly, if that is what you were attempting to do. You will need a seperate query to input data and then use the query with totals as a Record Source for a report or form.
    Tuesday, July 17, 2018 7:09 PM
  • A value which relates to a grouped level is an attribute of the entity type on which the group is based, so should be a column in the table which models that entity type.  For example with a query like this:

    SELECT Orders.OrderID, OrderDate,
    SUM(Quantity * UnitPrice) AS OrderTotal
    FROM ORDERS INNER JOIN OrderDetails
    ON Orders.OrderID = OrderDetails.OrderID
    GROUP BY Orders.OrderID, OrderDate;

    Any value, OrderNumber for instance, which is an attribute of the Orders entity type would be a column in the Orders table.  While such a column could be shown in a control in a form bound to the OrderNumber column added to the above query, no value could be entered into the control, because the query does not return an updatable recordset.  It would be necessary to enter the value into an unbound control, and execute an UPDATE statement in the control's AfterUpdate event procedure, e.g.

    Dim strSQL As String

    strSQL = "UPDATE Orders" & _
        " SET OrderNumber = " & Me.txtOrderNumber & _
        " WHERE OrderID = " & Me.OrderID

    CurrentDb.Execute strSQL, dbFailOnError

    Ken Sheridan, Stafford, England

    Tuesday, July 17, 2018 10:43 PM