Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Form datasheet unbound field

Discussion in 'Information Technology' started by Marc, Jul 28, 2006.

  1. Marc

    Marc
    Expand Collapse
    Guest

    I am struggling with a new issue that revolves around the inability to
    update a query with a calculated field. I have a datasheet form with
    fields that include [item], [rawingredient], [mixcode] and [process].
    I would like to be able to update the [process] field, but [mixcode] is
    a calculated field in the query that displays [mixcode] for an [item]
    if it is different from the [rawingredient] with an "Iif" statement.

    As has become abundantly clear on this group and in Access Help, you
    cannot update data in a query with a calculated field. So, I would
    like to include an unbound control in a subform with datasheet view
    that would provide the [mixcode] information for a given [item] and
    [rawingredient] and still allow me to change [process].

    So, the subform is bound to "tblItemProcess" with fields [item],
    [rawingredient] and [process], and query "qryItemMixCode" includes
    fields [item], [rawingredient] and [mixcode]. [mixcode] is a
    calculated field (Iif Statement) that determines if the rawingredient
    is actually part of an assembly.

    I have tried a dlookup up formula, such as
    "=dlookup("mixcode","qryitemmixcodes","[item] = """ & me![item] & """ &
    "[rawingredient] = """ & me![rawingredient] & """")" , but I get an
    error from Access that "You may have entered an operand without an
    operator."

    Any thoughts?
     
  2. Loading...


  3. Marc

    Marc
    Expand Collapse
    Guest

    Never mind. Another one I was able to (slowly) puzzle through on my
    own. The immediate problem was that I was referencing fields from the
    controlsource (query), as opposed to form fields. Fixing that made the
    unbound control work in both single and datasheet views.

    Hurrah.

    Marc wrote:
    > I am struggling with a new issue that revolves around the inability to
    > update a query with a calculated field. I have a datasheet form with
    > fields that include [item], [rawingredient], [mixcode] and [process].
    > I would like to be able to update the [process] field, but [mixcode] is
    > a calculated field in the query that displays [mixcode] for an [item]
    > if it is different from the [rawingredient] with an "Iif" statement.
    >
    > As has become abundantly clear on this group and in Access Help, you
    > cannot update data in a query with a calculated field. So, I would
    > like to include an unbound control in a subform with datasheet view
    > that would provide the [mixcode] information for a given [item] and
    > [rawingredient] and still allow me to change [process].
    >
    > So, the subform is bound to "tblItemProcess" with fields [item],
    > [rawingredient] and [process], and query "qryItemMixCode" includes
    > fields [item], [rawingredient] and [mixcode]. [mixcode] is a
    > calculated field (Iif Statement) that determines if the rawingredient
    > is actually part of an assembly.
    >
    > I have tried a dlookup up formula, such as
    > "=dlookup("mixcode","qryitemmixcodes","[item] = """ & me![item] & """ &
    > "[rawingredient] = """ & me![rawingredient] & """")" , but I get an
    > error from Access that "You may have entered an operand without an
    > operator."
    >
    > Any thoughts?
     

Share This Page