[ReReReSolved] make query to have a field that shows as rows number.

hi,
i got a query like this

Code:

---

apple    2.50    3.40
banana    2.40    2.90
papaya    1.40    2.30
mangoes  3.40    4.40

---

what i hope is, the query would also show the number of the records.
like below

Code:

---

1 apple    2.50    3.40
2 banana    2.40    2.90
3 papaya    1.40    2.30
4 mangoes  3.40    4.40

---

so, anyone of u have any idea.
thanks in advance

Use an autonumber (access) or auto increment number for the primary key and put that into the query - it won't always be sequential, but at least it would have a unique reference to the record.

You do have a Primary Key already, correct?
Does the record number really matter?

em, because i want the report to show the numbering records that it print out.
like for the food sample above. so, if there any way to make a dummy field in our query to show something like autonumber according to the shown records?

hi, based on a solution given by rob in
http://www.vbforums.com/showthread.php?t=339978

instead of creating a dummy field in query, we could just create a textbox and set the value when the report runs. so, now my report have a numbering column.

thanks rob :)

VB Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Static idCounter
    
    idCounter = idCounter + 1
    txtId = idCounter
End Sub

while, my code got some problem

let say, when we preview the report, we would see it is nice numbering
from
1
2
3
4
5
6

but if we print the report, we would get
7
8
9
10
11
12

and i wonder whats wrong...?
is it something to do with the Detail_Print? and wat is Detail_Retreat ????


need urgent help.... sorry if it sounds annoy... but i really need to put this project finish to start another.

ok, settled ;)

VB Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    counterReport = counterReport + 1
    txtId = counterReport
End Sub
 
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    counterReport = 0
End Sub

the counterReport is a variable in module

no, i was wrong, we can't rely on the Detail_Format :(
i just test it :p

btw, we could trust Detail_Print

VB Code:

first page
==========
Detail_Format : 1    10
Detail_Format : 2    19
Detail_Format : 3    4
Detail_Format : 4    12
Detail_Format : 5    20
Detail_Format : 6    5
Detail_Format : 7    1
Detail_Format : 8    6
Detail_Format : 9    6
Detail_Format : 10    9
Detail_Format : 11    18
Detail_Format : 12    8
Detail_Format : 13    13
Detail_Format : 14    7
Detail_Format : 15    15
Detail_Format : 16    11
Detail_Format : 17    16
Detail_Format : 18    16
Detail_Format : 19    10
Detail_Format : 20    19
Detail_Format : 21    4
Detail_Format : 22    12
Detail_Format : 23    20
Detail_Format : 24    5
Detail_Format : 25    1
Detail_Format : 26    6
 
second page
===========
Detail_Format : 27    6
Detail_Format : 28    9
Detail_Format : 29    18
Detail_Format : 30    8
Detail_Format : 31    13
Detail_Format : 32    7
Detail_Format : 33    15
Detail_Format : 34    11
Detail_Format : 35    16
 
third page
==========
Detail_Format : 36    16

while, when running on Detail_Print

VB Code:

first page
==========
Detail_Print : 1    10
Detail_Print : 2    19
Detail_Print : 3    4
Detail_Print : 4    12
Detail_Print : 5    20
Detail_Print : 6    5
Detail_Print : 7    1
 
second page
===========
Detail_Print : 8    6
Detail_Print : 9    9
Detail_Print : 10    18
Detail_Print : 11    8
Detail_Print : 12    13
Detail_Print : 13    7
Detail_Print : 14    15
Detail_Print : 15    11
 
third page
==========
Detail_Print : 16    16

i attached with the database which i use to learn about this

hi,
the funny thing is, i suspect the access got a bug here.
coz, when we print, we get different number !!

anybody plez download the access file and try to print the report out.
:( it is really funny, the printer show something different from print preview

ok, i finally got it solved

the solution is to place a textbox (txtCount) in our report
under the control source, key in "=1"

and under the textbox which we wanna display our number, set the textbox control source to "=txtCount" and set the Running Sum to "Over Group"

i attached with the solved ms access database.