How I can increment a number field every text field is changed.

[Guest]

Please help me!
I have two fields in my table COUNTRY and DESP. NO. I want every time a new
country is enter in the COUNTRY field the DESP. NO to be increase by 1. And
If is the same Country that is enter the DESP. NO to continue the sequense
Example:

COUNTRY DESP. NO
UK 1
FRANCE 1
UK 2
GREECE 1
UK 3
GREECE 2

 

[peregenem]

I have two fields in my table COUNTRY and DESP. NO. I want every time a new
country is enter in the COUNTRY field the DESP. NO to be increase by 1. And
If is the same Country that is enter the DESP. NO to continue the sequense
Example:

COUNTRY DESP. NO
UK 1
FRANCE 1
UK 2
GREECE 1
UK 3
GREECE 2

Build a table of ISO country codes (GREECE = GRC, FRANCE = FRA, United
Kingdom of Great Britain and Northern Ireland = GBR). Use the abbrev as
a FOREIGN KEY in your table. If you want to determine the relative
order the codes were added to your table then use a DATETIME column.

 

[Guest]

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).