autonumber on report 2

[lipin]

I have a report that I need a sequential number printed at the top. Like an invoice number. I can't use an autonumber in a field in the table because I don't need the number based on records. It doesn't matter how many records or pages the report includes, I need the number to increase by one when every time the report is printed(or ran).

So on the form where the Command Button is the print report, I added a hidden txt box and in the OnClick Event of the Print button I set txtbox.value = txtbox.value +1 to increase it.
And then on the report I referenced an unbound txt box to the form txtbox and it adds fine and prints sequential numebrs in the report header. That is until I close the application.
When opened again the number starts at 30000 which was default because thats what number I need to start at. I know why it goes back to default again when I restart the Application because it is default. Can't seem to get aroung it. But I need it to keep counting somehow. ANY ideas?

 

[CJTyo]

Why not make a table with a single column and overwrite the existing value with the new value of old+1?

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[lipin]

Sorry C, I need more explanation, what is tied to this table? The textbox on the form? And how does it increase value?

 

[CJTyo]

I'd tie the calculation to this field by pulling the value, adding one, then storing the new value back in the field. Since you want to increment each time you open or run the report, you should be able to do this with code.

Now, I'm just learning VBA myself so anyone with more programming experience who could provide actual coding advice just jump right in here (JeremyNYC??? ;-)).

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[JeremyNYC]

Heh. I read this and was about to close it, as it seemed there was enough to go on, but since you mentioned me by name...

The way I would go about this would first of all involve making it so that the user cannot print the report any way other than through a form--this means that the user wouldn't be able to open the report through the database window.

It would also require the creation of a table to store the number of times the report was printed. I've created tblConstant, with a numeric field called TimesPrinted.

There would be a button on the form that prints the report. The code behind that button would be something like this:

Private Sub btnPrintBraReport_Click()
On Error GoTo error

Dim db As DAO.Database
Dim strSql As String

Call DoCmd.OpenReport("rptBra", acViewNormal)

Set db = CurrentDb
strSql = "UPDATE tblConstant Set TimesPrinted = TimesPrinted + 1"
Call db.Execute(strSql, dbFailOnError)

exitPoint:
db.Close
Set db = Nothing

Exit Sub

error:
MsgBox Err.Number & ": " & Err.Description
GoTo exitPoint
End Sub

(Sorry for the "bra" content, that's what the person was working on last time I created a database to help someone!)

I hope this helps. If I've taken too big a leap here, let me know and I'll explain a bit more.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[CJTyo]

Jeremy,

If I'm reading it right I think it's exactly what I was saying but didn't know how to implement.

Thanks,
C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[CJTyo]

Here's the other thread I mentioned:

thread700-374310

Scroll down to some posts by Cosmo.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[lipin]

Many thanks to Jeremy & C! This was exactly what I needed and it works flawlessly.

 

[CJTyo]

You're welcome. Glad it worked.

Thanks, Jeremy, for putting my thoughts into code. ;-)

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[JeremyNYC]

Glad it's working. Just noticed a little tweak to be made, though.

Put the "set db..." line above the line that prints the report. That way, if there's a problem with printing the report, you won't also get a problem when the code closes the database object.

Take care.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.