Search code examples
ms-accessdlookup

populating field data by Dlookup in access

I'm trying to populate data from a query (SUMA PALETS) that counts the records from a field (its related table is called "LOTES PRODUCTOS") to a field in a subform (PEDIDOS PRODUCTOS). Both, query (and its related table) and subform (and its related table) have a common field with the same value. This field is called "PRODUCTOPEDIDO" in SUMA PALETS query and in LOTES PRODUCTOS table and it is called "IdPEDIDOSPRODUCTOS" in the subform.

I'm using the next formula in the control source of the subform field (it's called "QUEDAN"):

=DLookUp("[CountofIdPEDIDOSPRODUCTOS]";"[SUMA PALETS]";"[PRODUCTOPEDIDO] = " & Forms![PEDIDOS PRODUCTOS]![IdPEDIDOSPRODUCTOS])

Where:

  • SUMA PALETS: It's the query name
  • PRODUCTOPEDIDO: table field I want to count
  • CountofidPEDIDOSPRODUCTOS: it's the query field that counts PRODUCTOPEDIDO field in the query
  • PEDIDOS PRODUCTOS: subform name
  • IDPEDIDOSPRODUCTOS: subform field name with the same value as - PRODUCTOPEDIDO in query.

But I get #Name? error in the subform field. I don't know where is the problem. Could It be because the field's name is the same in another tables?

I would like that QUEDAN field was updated instantly when I add records in LOTES PRODUCTOS field (by introducing data in a subform). I don't know If It`s the right approach or could It be better option If I write a query to get count values directly in QUEDAN field (I don't know much about queries in SQL).

Thanks in advance

Solution

  • Should be:

    =DLookUp("[CountofIdPEDIDOSPRODUCTOS]";"[SUMA PALETS]";"[PRODUCTOPEDIDO] = " & Forms![ParentFormName]![PEDIDOS PRODUCTOS]![IdPEDIDOSPRODUCTOS])

    You need to refer to the parent form before the subform.