Hi Ian,

I don't see why you need recordset operations for this: you can just use
calculated fields in a query to return the "X" and "Y" for each record.
Here's an example that works in the Northwind sample database (ItemSeq
is X, ItemCount is Y):

SELECT
  (SELECT COUNT(C.OrderID)
     FROM [Order Details] AS C
     WHERE C.OrderID = A.OrderID
       AND C.ProductID <= A.ProductID) AS ItemSeq,
  (SELECT COUNT(D.OrderID)
     FROM [Order Details] AS D
     WHERE D.OrderID = A.OrderID) AS ItemCount,
   A.OrderID,
   A.ProductID,
   A.UnitPrice,
   A.Quantity
 FROM [Order Details] AS A
 ORDER BY A.OrderID, A.ProductID
;

If you don't want to include the two subqueries in the query to which
the form is bound (maybe because you need the query to be updatable),
you can use the same technique in a parameter query that calculates and
concatenates "X" and "Y" for the current record only. Then use the query
in a DLookup() expression in the textbox's ControlSource property, or in
the form's Current event procedure.

   

   

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.