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

[asmdev]

hi,
i got a query like this

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

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

[Ecniv]

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?

[asmdev]

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?

[asmdev]

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

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

[asmdev]

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.

[asmdev]

ok, settled

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

[asmdev]

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

btw, we could trust Detail_Print

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

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

[asmdev]

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

[asmdev]

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.