Unique number on Report

  • Thread starter Thread starter johnb
  • Start date Start date
J

johnb

I have a report based on a query and I need to add unique
number to the report every time its run. The number needs
to increment by 1 each time the report is run. I think
using the query with user define function is the way to go.

Suggestions please

TIA johnb
 
John,

I would have a simple one-field, one-record table, where you store the
report RunNumber. Include this table in the Query that your report is
based on, so you can put it on the report. (An alternative would be
using an unbound textbox on the report, with a DLookup function to put
the RunNumber). As part of the procedure which you use to print the
report, add into it the running of an Update Query to update RunNumber
to [RunNumber]+1

- Steve Schapel, Microsoft Access MVP
 
John,

First, Add a table : tblAutoNums
Fields: Domain text (50)
NextNum LongInteger

Primary Key: Domain
Add Two entries to this table - showing then NEXT (i.e. as yet unused) value
"rptSomeReportName" 12
"SomeOtherReport" 355


Next, add the function below to a code Module, and you are done.

To use it, in the report's Load event
dim lngRunNumber as long
lngRunNumber = GetNextNumber(Me.Name)
if lngRunNumber > 0 then
me.txtRunNumber.value = lngRunNumber
Else
MsgBox "Wrong Number"
End if

That's all.

HS
'----------------code start--------------------
Function GetNextNumber(ByVal InDomain As String) As Long
On Error GoTo GetNextNumber_Err:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngNextNum As Long
Dim ssql As String

ssql = "Select NextNum from tblAutoNums where Domain = '" & InDomain & "'"

Set db = CurrentDb
Set rst = db.OpenRecordset(ssql)

' capture nextnum, increment it for the next time
rst.MoveFirst
rst.Edit
lngNextNum = rst("NextNum")
rst!NextNum = lngNextNum + 1
rst.Update

GetNextNumber = lngNextNum

GetNextNumber_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

GetNextNumber_Err:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical,
"Failed to get nextNumber"
GetNextNumber = -1
Resume GetNextNumber_Exit:

End Function


'----------------code end---------------------
 
Back
Top