Need a sequencial autonumber on a report each time the report is view

[Guest_imported]

I am using a report as a way to show data, and want a text box to show a sequencing number each time the report is viewed, but the number does not have to be linked to any table.
Example being: sequwnce number is 3005 of a report, and next time this report is viewed (could be of a different customer) the number would be 3006. The reports would sequence, but not by any other criteria other than it is the next report viewed.
Any help would be appreciated.

Richard S

 

[scriverb]

To do this you would have to store and update a sequencing number in a server based table so that all users could use it. Create a table on your back-end database and call it let's say tblDBParameters. create a field called: Rpt_Seq Number/Long Integer.

In a database Class module create the following:
Global vRpt_Seq as long
Function Rpt_Seq()
Rpt_Seq = vRpt_Seq
end Function

In you front-end database link to this table. Now this front-end application can read and update the table. Wherever your execute the opening of reports in this application you must use the following code:

Dim db as DAO.database
dim rs as DAO.recordset
Set db = currentdb
Set rs = db.OpenRecordset("tblDBParameters", dbOpenDynaset)
rs.movefirst
vRpt_Seq = rs("Rpt_Seq&quot
rs.edit
rs("Rpt_Seq&quot = rs("Rpt_Seq&quot + 1
rs.update
rs.close
db.close

Now in the TextBox in your report enter the following in the ControlSource property:
=Rpt_Seq()

This should do it for you. I don't know why you are doing this but this will perform the requested function.



Bob Scriver

 

[JesseL]

I would like to do the same thing. What kind of link do you mean?, and would this field Rpt_Seq_Number be linked to the other table? Would this be a one to one relationship?

thanks
ksharp

 

[JesseL]

Sorry, I would also like to know what a back-end database would be. I wouldn't create this tblDBParameters in the same database? elaborate please

 

[scriverb]

I don't have a lot of time right now and my DSL connection at home is out right now but I will give you a little bit and get back to you guys later.

The conventional server based ACCESS application consists of a front-end database(on the PC) and a back-end database(on the Server). The front-end database is distributed to all users and it is linked through your network to the tables in the back-end database. The front-end includes static tables(don't change), queries, forms, macros and modules. This is where the action is in your database. The back-end database is where your shared tables and data are kept. More than one user can have access to these tables at the same time. This is where all of the front-ends are writing their data to.

When I talk about Linked tables I am talking about tables that are stored in the back-end by accessed by the front end through the network as a linked table. You will see these in your front-end data base as having a black right arrow next to the table name.

My suggestion to this problem talks about creating a shared table in the back-end database that could be read and updated with the next current RecordID# as any user is creating a new record.

I have to go now but will check back with you all after you digest this update. Bob Scriver

 

[Guest_imported]

OK, I have the sequencial number working in a report. After writing the code and modifying it to fit the database, I wrote a query to look at the table, then added a sub report pointing to that query, to the original report, that shows the incremented number each time a new report is called for.

Thanks,

Richard S