In a single user environment look up the highest number for the country in
question and add 1. Do this in the Country control's AfterUpdate event
procedure in your form with:
Me.[Desp. No] = Nz(DMax("Desp. No]","YourTable","Country = """ & Me.Country
& """"),0)+1
In a networked multi-user environment this is liable to conflicts, however.
A common way around this is to store the last numbers used in an external
database which is opened exclusively in code to get the next number. The
following function does this:
Public Function GetNextNumberForCountry(strCounterDb As String, strCountry
As String) As Long
' Accepts: Full path to database containing tblCounter table with
' long integer column NextNumber and text column Country.
' Country for which next serial number to be obtained
' Returns next number in sequence for specified Country
' 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 Country = '" & strCountry & "'"
' 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
GetNextNumberForCountry = 0
Exit Function
End If
Err.Clear
Set rst = dbs.OpenRecordset(strSQL)
With rst
.Edit
' insert new row if no existing record for this Country
If Err = NOCURRENTRECORD Then
.AddNew
!Country = strCountry
!NextNumber = 1
.Update
GetNextNumberForCountry = 1
Else
' update row and get next number in sequence
!NextNumber = !NextNumber + 1
.Update
GetNextNumberForCountry = rst!NextNumber
End If
End With
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
End Function
Call the function in the Country control's AfterUpdate event procedure with
something like this:
Dim strCounterPath as String, lngNextNumber As Long
strCounterPath = "F:\SomeFolder\Counter.mdb"
lngNextNumber = GetNextNumberFor Country(strCounterpath, Me.Country)
If lngNextNumber > 0 Then
Me.[Desp. No] = lngNextNumber
Else
MsgBox "Unable to get Desp. No at present", vbInformation,"Warning"
End If
Note that the code for this function uses DAO so be sure you have a
reference to the DAO object library (Tools|References on the VBA menu bar).