Okay, emergency is over...
An example on how to do this...
tblControl
CtrlCode - primary key, string, 4 to 8 characters depending
CtrlNum - string, can also be numeric
CtrlName - descriptive text
Example of possible data...
[tt]
CtrlCode CtrlNum CtrlName
A/P AP000001 Accounts Payable invoice
PAY PR000001 Payroll
G/L GL000001 General Ledger
[/tt]
Although the control number can be numeric, it is often better to use a text string to allow for a more descriptive control number - for example, include the year.
The code to grab the next control number and add to it is fairly simple, although it still requires the creation of a module, and some coding. Perhaps something like this.
In the database window, select the "Modules" tab. Click on "New". Make sure the top of your code includes...
Code:
Option Compare Database
[COLOR=blue]Option Explicit[/color]
The actual module could look something like...
Code:
Function ControlNumber(strCtrlCode As String) As String
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String, strQ As String
Dim lngCtrlNum As Long, strCtrlNum As String
[COLOR=green]
'strQ used to replace double quote character[/color]
strQ = Chr$(34)
[COLOR=green]
'Select statement to retrieve and update control number
'I am assuming that
'- the prefix of 2 characters for the control number will be used
'to identify the system for the control number
'- the actual number will consist of 6 characters, 000000[/color]
strSQL = "SELECT * From tblControl WHERE CtrlCode = " & strQ & strCtrlCode & strQ
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)
With rst
.MoveFirst
[COLOR=green]
'Get the current control number[/color]
strCtrlNum = Nz(!CtrlNum, "UK000000")
[COLOR=green]
'Assign the control number to the returning function[/color]
ControlNumber = strCtrlNum
[COLOR=green]
'Strip off the 2 character string prefix to grab the number
'This needs to be done so we can add 1 to the number
'I could have used a more simple approach
'lngCtrlNum = CLng(Right(strCtrlNum, 6))
'But this code does the same and is more flexible[/color]
lngCtrlNum = CLng(Right(strCtrlNum, Len(strCtrlNum) - 2))
[COLOR=green]
'Increment by 1[/color]
lngCtrlNum = lngCtrlNum + 1
[COLOR=green]
'Create a new control number[/color]
strCtrlNum = Left(strCtrlNum, 2) & Format(lngCtrlNum, "000000")
[COLOR=green]
'Now update the control table[/color]
.Edit
!CtrlNum = strCtrlNum
.Update
End With
[COLOR=green]
'Free up resources [/color]
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function
Most of the above is comments, in green, is for your benifit.
The calling staement is pretty easy.
For the BeforeUpdate event, or your "button click" event, assuming your field name is CtrlNumber, run the code
Me.CtrlNumber = ControlNumber("A/P")
Enjoy
Richard
