Jane:
I assume each document is represented by a row in a table in the databse.
Firstly, store the values in two separate fields , one for the letters and
one for the numbers. You can easily concatenate then together to produce a
single value when required in queries, forms, reports etc. Create a unique
index on the combined two columns in table design.
If the database is a standalone application with only one user at a time its
very easy to implement. Say the two fields are called LetterPrefix and
SeqNumber you just look up the last number for the LewtterPrefix and add 1.
This would be dome in the AfterUpdate event procedure of the LetterPrefix
control on your data entry form:
Me.SeqNumber = Dmax("Seqnumber", MyTable", "SeqPrefix = """ & Me.SeqPrefix &
"""") + 1
In a multi-user environment on a network, however, this can cause conflicts
if two users are adding a record simultaneously, as they could both get the
same number. A common solution to this is to store the last used numbers in
a separte database on the server which is opened exclusively in code to get
the next number, thus avoiding conflicts. the following code is adapted from
some old code of mine which does this in a similar context to yours. Put the
following function in a standard module:
Public Function GetNextNumber (strCounterDb As String, strLetterPrefix As
String) As Long
' Accepts: Full path to database containing tblCounter table with
' long integer column NextNumber and text column LetterPrefix
' for which next serial number to be obtained
' Returns next number in sequence for specified letter prefix
' if external database can be opened and number obtained.
' Returns zero 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 strSQL As String
strSQL = "SELECT * FROM tblCounter WHERE LetterPrefix = """ & _
strLetterPrefix & """"
' 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
GetNextNumber = 0
Exit Function
End If
Err.Clear
Set rst = dbs.OpenRecordset(strSQL)
With rst
.Edit
' insert new row if no existing record for this letter prefix
If Err = NOCURRENTRECORD Then
.AddNew
!LetterPrefix = strLetterPrefix
!NextNumber = 1
.Update
GetNextNumber = 1
Else
' update row and get next number in sequence
!NextNumber = !NextNumber + 1
.Update
GetNextNumber = rst!NextNumber
End If
End With
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
End Function
To use this you first need to create a database, Counter.mdb in a shared
folder on the server with a table tblCounter containing the NextNumber and
LetterPrefix fields. Then in the AfterUpdate event procedure of the
LetterPrefix control on your form you'd put something like this:
Const conMESSAGE = "Unable to get number at present. Please try again."
Dim strpath As String
Dim lngNextNumber As Long
strPath = DLookup("CounterDbPath", "CounterDBLocation")
lngNextNumber = GetNextNumber (strP{ath, Me.LetterPrefix)
If lngNextNumber > 0 Then
Me.SeqNumber = lngNextNumber
Else
MsgBox conMESSAGE,vbInformation, "Warning"
Me.LetterPrefix = Null
Me.LetterPrefix.SetFocus
End If
The full path to the external counter database is stored in the
CounterDbPath field of a one row/one column CounterDbLocation table in the
current database. It can then be easily updated if the location of the
counter database on the server should be changed.
Note that the above code uses DAO do you might need to create a reference to
the Microsoft data Access Objects object library with the Tools|Refrences
menu item on the VBA menu bar. Since Access 2000 ADO Hs been used as the
default data access technology.
Ken Sheridan
Stafford, England
