Rownumbers

[voiD]

Hello,

I've faced a - for me - tricky problem. I have a query, containing about 8 tables and this is the datasource of a form. I have to show the rownumber of the query on the form - I mean if the query has 10 rows, I have to number the displayed rows from 1 to 10.
Does anyone have idea if how could I do it without storing the rownumbers directly in the db?? Is it possible anyway?
Many thanks in advance for any tip or idea!

Regards,
VoiD

 

[Zaeed]

well you can determine the number of rows the query has using this

    Dim q As QueryDef
    Set q = CurrentDb.QueryDefs("qry_Query")
    X = MsgBox(q.Fields.count)

That simply gives you the number of rows. Unless they have a specific order, you could simply stick a number next to each of them with the max being the total count

 

[voiD]

Thanks for the tip, but I am afraid I don't understand it. How can give me the number of fields of a query the number of its rows? The problem must be with me, but I really don't understand it. Please explain it!
Many thanks!

VoiD

 

[Zaeed]

whoops my bad...

this is what you want..

    Dim q As QueryDef
    Set q = CurrentDb.QueryDefs("qry_Query")
    X = MsgBox(q.OpenRecordset.RecordCount)

I need more sleep

 

[voiD]

Hello,

Thanks again for the tip, but unfortunately this is not what I am searching, becasue RecordCount property gives only one number - the number of records in the query. Let me show via an example what I want.
I have a query, returns with let's say 10 records:
Record 1
Record 2
...
Record 10

I would like to display the position of each records like this:
1. Record 1
2. Record 2
...
10. Record 10

In nutshell that is what I want. I know the possible source of the rownumbers (AbsolutePosition property), but I don't know how to display the position numbers.
Many thanks in advance!

VoiD

 

[ajetrumpet]

I would like to display the position of each records like this:
1. Record 1
2. Record 2
...
10. Record 10

In nutshell that is what I want. I know the possible source of the rownumbers (AbsolutePosition property), but I don't know how to display the position numbers.

Void,

The only thing I can think of that you can do here, is this (use the On_Current event of the Form):

Dim rs as recordset
  set rs = rs.recordsetclone

with rs

  .movefirst
    .findFirst("yourfield =" & forms!YourFormName!FieldContainingTheUniqueIdentifier)
      me.NewControl = "Record " & .absoluteposition + 1

end with

"NewControl" is the control name that is displaying the Record Number

...And this will only work if you have a field on the form that holds "unique" identifying values for each record that it displays. If you don't, you may want to try using something like Docmd.findRecord