Report/Invoice numbering convention

Sorry, this is my second post.

We have the same problem, I have had a peer to peer network, in which the
shared mdb resides in desktop pc, two laptops accessing the shared SharedData.

mdb, okay, we used the primary key whici is the autonumber, but...we have the
record number which is unique, my record number starts TSND00001, TSND00002...

AND SO ON, when the two users entering the product code, it happens that they
are on the same unique number......Please help me regarding this, is there
any chance to correct this scenario, we just starting only this year to
transfer our data form excel sheets, we are very new in vb code......thanks!

================================================== ================
We are NGO (Non-Government Organization) and solicitation comes from variety
of good people / oraganization, we are non-profit organization dealing on
relief goods for less fortunate people, everyday, one company is being added
to our database NGGO and we want to create UNIQUE PRIMARY KEY BASED ON THE
FOLLOWING:

Fields1(Counter) Fields2(strNGGO) NGONumber

000001 NGGO NGGO000001
000001 NGGO NGGO000002
000001 NGGO NGGO000003
000001 NGGO NGGO000004
000001 NGGO NGGO000005

and so on, and so forth......

two desktop pc's accessing the database in shared/split format. everytime,
the two user creating making/creating new NGONumber they are making
duplicates and we are facing problems on this.

BY THE WAY, I'VE CREATED A COMMAND BUTTON FOR NEW NUMBER

Is there any chance you could help us regarding the above problem?

Ken Sheridan wrote:
In a single user environment you don't need your tblNCDateStart table at all
as you can compute the next NCRepNum value from the data in the
tbleNCReportLog table by looking up the highest sequential number for the
current year and adding 1. You'd do this in the frmNCReportLog form's
BeforeInsert event procedure with:

Dim strYear as String
Dim strWeek as String
Dim strCriteria As String

strYear = Format(VBA.Date,"yy")
strWeek = Format(VBA.Date,"ww")

strCriteria = "Left(NCRepNum,2) = """ & strYear & """"

Me.NCRepNum = strYear & strWeek & _
Format(Nz(DMax(Right(NCRepNum,3)),-1)+1,"000")

In a multi-user environment where the tables are in a shared back end a
conflict could arise if two or more users are adding a record simultaneously.
Provided that the NCRepNum column is indexed uniquely (no duplicates) then
what would happen is that the first user to save the record would be
successful, but the other users would incur an error when they attempt to
save their record. There are various solutions to this, but one, which I've
used successfully for many years, is to use a tblNCDateStart table as you are
doing but to put this in a separate database in a shared folder on the
network and open it exclusively when a user is getting the next number. As
you are starting your number sequence each year you only need the year in a
ReportYear column in tblNCDateStart rather than the year and week, which
needs no DefaultValue or Format property set, but should still be an integer
number data type and a NextNumber column of integer data type. You only need
store the last number used per year, not all numbers. You can then get the
next NCRepNum value by means of the following function, which should go in a
standard module in your database's front end:

Public Function GetNextNCRepNum(strCounterDb As String)

' Accepts: Full path to database containing tblNCDateStart table with
' integer column NextNumber and integer column ReportYear.

' Returns next number in sequence for specified Report Year
' if external database can be opened and number obtained.
' Returns Null if unable to get next number.

Const NOCURRENTRECORD As Integer = 3021
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim n As Integer, I As Integer, intInterval As Integer
Dim intYear As Integer
Dim strSQL As String

intYear = Year(VBA.Date)

strSQL = "SELECT * FROM tblNCDateStart WHERE ReportYear = " & intYear

' make 10 attempts to open external database exclusively
DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, "Attempting to get new number"
On Error Resume Next
For n = 1 To 10
Err.Clear
Set dbs = OpenDatabase(strCounterDb, True)
If Err = 0 Then
Exit For
Else
intInterval = Int(Rnd(Time()) * 100)
For I = 1 To intInterval
DoEvents
Next I
End If
Next n
SysCmd acSysCmdClearStatus
DoCmd.Hourglass False

If Err 0 Then
GetNextNCRepNum = Null
Exit Function
End If

Err.Clear

Set rst = dbs.OpenRecordset(strSQL)

With rst
.Edit
' insert new row if no existing record for this Report Year
If Err = NOCURRENTRECORD Then
.AddNew
!ReportYear = intYear
!NextNumber = 0
.Update
GetNextNCRepNum = Format(VBA.Date, "yyww") & "000"
Else
' update row and get next number in sequence
!NextNumber = !NextNumber + 1
.Update
GetNextNCRepNum = Format(VBA.Date, "yyww") & _
Format(rst!NextNumber, "000")
End If
End With

rst.Close

End Function

In the frmNCReportLog form's BeforeUpdate event procedure you can then call
the function, passing the path to the database containing the tblNCDateStart
table as its argument. If the back end database is simply an Access .mdb
file, and the database file containing the tblNCDateStart table, which I'll
call Counter.mdb for this example, is in the same shared folder as the back
end, then you can get the path with the following function:

Public Function ConnectPath() As String

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strConnectString As String, strDbName As String, intSlashPos As
Integer

Set dbs = CurrentDb
' loop through tabledefs collection until
' first linked table is encountered and
' get its Connect property
For Each tdf In dbs.TableDefs
If tdf.Connect "" Then
strConnectString = tdf.Connect
End If
Next tdf

' remove table name from connect string
intSlashPos = 1
strDbName = strConnectString
Do While intSlashPos 0
intSlashPos = InStr(strDbName, "\")
strDbName = Right(strDbName, Len(strDbName) - intSlashPos)
Loop

' remove part of connect string before path
ConnectPath = Mid(strConnectString, 11, Len(strConnectString) _
- (10 + Len(strDbName)))

End Function

You can then call the GetNextNCRepNum function in the frmNCReportLog form's
BeforeInsert event procedure like so:

On Error GoTo Err_Handler

Const conMESSAGE = "Unable to get NCRepNum number at present."
Dim strCounterDb As String, varNCRepNum As Variant

strCounterDb = ConnectPath() & "Counter.mdb"

varNCRepNum = GetNextNCRepNum(strCounterDb)

If Not IsNull(varNCRepNum) Then
Me!NCRepNum = varNCRepNum
Else
MsgBox conMESSAGE, vbExclamation, "Error"
Cancel = True
End If

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

Note that, as with an autonumber, once a number is obtained it won't be
re-used even if the record is not saved or is subsequently deleted.

Ken Sheridan
Stafford, England

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
[quoted text clipped - 48 lines]

Thanks in advance.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200812/1