[AccessD] Rownumbers

Alternatively Marcel you can do it without any code at all.
Within your Sorting and Grouping set a Group Header at your item level. Move all of your printed fields from the Detail section into this new Group Header section, and reduce the Detail section's height to get rid of it. Now add an unbound control whose source is =Count(OR_NUMMER) and set its Running Sum property to Over Group. That should do it.

Andy Lacey
http://www.minstersystems.co.uk


-- Original Message --
From: Gustav Brock <gustav at cactus.dk>
To: info <accessd at databaseadvisors.com>
Send: 2003-02-20
Subject: Re: [AccessD] Rownumbers

Hi Marcel

You can add a subquery to return the rownumber (RowNo):



SELECT
  OR_OD_NUMMER,
  OR_NUMMER,
  (SELECT
    COUNT(*)
  FROM
    tblOrderDetail AS tblA
  WHERE
  tblA.OR_OD_NUMMER = tblOrderDetail.OR_OD_NUMMER
  AND
  tblA.OR_NUMMER 
FROM
  tblOrderDetail
ORDER BY
  OR_OD_NUMMER,
  OR_NUMMER;



/gustav

> I will try in my best englisch to make this problem clear to you. Hope it
> works:-) I have a table in my database with an autonumber field and a
field
> wich contains ordernumers. It looks like this

> OR_NUMMER    OR_OD_NUMMER
> 1000                    10
> 1001                    10
> 1002                    10
> 1003                    11
> 1004                    11
> 1005                    10
> 1007                    12
> 1008                    10

> Now we want to print on a report the actual orderposition of a current
> record. For example ordernumber 10 contains rownumbers 1000,
> 1001,1002,1005,1008. We would like to print 1, 2, 3, 4, 5 according to the
> actuel rownumbers. I have tried to make this happen to take parts of the
> actual autonumber but that is not sufficient enough.

> What I am trying is to make a function whicht take the current recordset
and
> put in a field with a number a calculatie in the function.

> FncRownumber(ByRef rs as dao.recordset)
>     Dim Rownumber as long

>     rs.movefirst
>     Do while not rs.eof
>         Rownumber = Rownumber + 1
>         rs.movenext
>     loop
> End function

> It works but I cannot get this in the report because then I lose the
> recordset.

> Are there other ways to make this happen exept putting an extra field in
the
> table which contains this recordnumber?


> Thanks, Marcel Vreuls