Numering Records in Two Reports

K

KT

I have two reports, the record source of these two reports are coming
from two queries that use different tables.

At the moment, I use "running sum over all" as the record number.
Therefore, the starting record number of both reports are =1.

Is it possible if I want the first record number of the second report
to follow the last number of the first report?

I have thought about if I could specify the starting number of the
second report that might work...but I couldn't go on.

Thanks for your help in advance.

Kanley
 
F

fredg

I have two reports, the record source of these two reports are coming
from two queries that use different tables.

At the moment, I use "running sum over all" as the record number.
Therefore, the starting record number of both reports are =1.

Is it possible if I want the first record number of the second report
to follow the last number of the first report?

I have thought about if I could specify the starting number of the
second report that might work...but I couldn't go on.

Thanks for your help in advance.

Kanley
Click to expand...

Air code ****
I haven't tried this, but it should work.
In the first report, let's name that =1 counter control "RecCount"

Add a new table to your database

TableName: "tblHoldRecCount"
FieldName: "LastRec" Number datatype, Integer FieldSize
Enter a Zero as the only record

Code the Report Footer Format event (of just the first report):

CurrentDb.Execute "Update tblHoldRecCount Set tblHoldRecCount.LastRec
= Me![RecCount];", dbFailOnError


In the second report, up in the Report's code Declaration's section:

Option Explicit
Dim intLastRec as Integer

(The Declaration section is at the very top of the code sheet, above
the first Sub procedure)

Code the second report's Report Header Format event:
intLastRec = DLookUp("[LastCount","tblHoldRecCount")

Add an unbound control to the Detail section.
Set it's control source to = 1.
Set it's Running sum to Over all (just like RecCount in the first
report). Name this one RecCount2. You can make this control not
visible.

Add another unbound control to the detail section.
Set it's Control Source to:
= intLastRec + [RecCount2]
(This one will show the actual number.)
..
Let me know if I've got it right...
 
K

KT

I followed your instructions and I got some errors after I edit the
code. It probably should work but i just didnt do them right...

I dont know what i have done wrong. Since I am not familiar with VB, i
just copied and pasted your code. I am using Access 97, would that be
a problem? When I pasted the first code to the repor footer, i got an
error said "Expected: End of Statment" and the semi-colon was
hightlighted. I got some syntax errors for the other code as well,
but i dont know how to fix them.

However, your design have given me some really good insights. Of cos
it's not as good as your original design, I am using the 'second part'
of your design now.

On the second report, I added an invisible unbound box to do the
running sum. Then a box will promopt to ask for the 'last record
number from the previous report'. The record number will then be the
summation of this number and the running sum in the invisible box.

Thanks a lot!

Kanley


fredg said:
I have two reports, the record source of these two reports are coming
from two queries that use different tables.

At the moment, I use "running sum over all" as the record number.
Therefore, the starting record number of both reports are =1.

Is it possible if I want the first record number of the second report
to follow the last number of the first report?

I have thought about if I could specify the starting number of the
second report that might work...but I couldn't go on.

Thanks for your help in advance.

Kanley
Click to expand...

Air code ****
I haven't tried this, but it should work.
In the first report, let's name that =1 counter control "RecCount"

Add a new table to your database

TableName: "tblHoldRecCount"
FieldName: "LastRec" Number datatype, Integer FieldSize
Enter a Zero as the only record

Code the Report Footer Format event (of just the first report):

CurrentDb.Execute "Update tblHoldRecCount Set tblHoldRecCount.LastRec
= Me![RecCount];", dbFailOnError


In the second report, up in the Report's code Declaration's section:

Option Explicit
Dim intLastRec as Integer

(The Declaration section is at the very top of the code sheet, above
the first Sub procedure)

Code the second report's Report Header Format event:
intLastRec = DLookUp("[LastCount","tblHoldRecCount")

Add an unbound control to the Detail section.
Set it's control source to = 1.
Set it's Running sum to Over all (just like RecCount in the first
report). Name this one RecCount2. You can make this control not
visible.

Add another unbound control to the detail section.
Set it's Control Source to:
= intLastRec + [RecCount2]
(This one will show the actual number.)
.
Let me know if I've got it right...
Click to expand...
 
F

fredg

I followed your instructions and I got some errors after I edit the
code. It probably should work but i just didnt do them right...

I dont know what i have done wrong. Since I am not familiar with VB, i
just copied and pasted your code. I am using Access 97, would that be
a problem? When I pasted the first code to the repor footer, i got an
error said "Expected: End of Statment" and the semi-colon was
hightlighted. I got some syntax errors for the other code as well,
but i dont know how to fix them.

However, your design have given me some really good insights. Of cos
it's not as good as your original design, I am using the 'second part'
of your design now.

On the second report, I added an invisible unbound box to do the
running sum. Then a box will promopt to ask for the 'last record
number from the previous report'. The record number will then be the
summation of this number and the running sum in the invisible box.

Thanks a lot!

Kanley

fredg said:
I have two reports, the record source of these two reports are coming
from two queries that use different tables.

At the moment, I use "running sum over all" as the record number.
Therefore, the starting record number of both reports are =1.

Is it possible if I want the first record number of the second report
to follow the last number of the first report?

I have thought about if I could specify the starting number of the
second report that might work...but I couldn't go on.

Thanks for your help in advance.

Kanley
Click to expand...

Air code ****
I haven't tried this, but it should work.
In the first report, let's name that =1 counter control "RecCount"

Add a new table to your database

TableName: "tblHoldRecCount"
FieldName: "LastRec" Number datatype, Integer FieldSize
Enter a Zero as the only record

Code the Report Footer Format event (of just the first report):

CurrentDb.Execute "Update tblHoldRecCount Set tblHoldRecCount.LastRec
= Me![RecCount];", dbFailOnError

In the second report, up in the Report's code Declaration's section:

Option Explicit
Dim intLastRec as Integer

(The Declaration section is at the very top of the code sheet, above
the first Sub procedure)

Code the second report's Report Header Format event:
intLastRec = DLookUp("[LastCount","tblHoldRecCount")

Add an unbound control to the Detail section.
Set it's control source to = 1.
Set it's Running sum to Over all (just like RecCount in the first
report). Name this one RecCount2. You can make this control not
visible.

Add another unbound control to the detail section.
Set it's Control Source to:
= intLastRec + [RecCount2]
(This one will show the actual number.)
.
Let me know if I've got it right...
Click to expand...
Click to expand...

As I wrote, it was untested.
Change the CurrentDb.Execute to:

CurrentDb.Execute "Update tblHoldRecCount Set tblHoldRecCount.LastRec
= " & Me![RecCount] & ";", dbFailOnError

The above should all be on 1 line.
Notice I've added some quotes. Make sure you include them.

Don't forget to input the 0 in the field in the table tblHoldRecCount.
 
Top