Creating a Custom Auto numbering system for table? 5

[Clara]

I am having to use Access XP - on an XP Professional operating system.
I have a database --DOS based not 2000 compliant --that I'm importing into Access. There is a unique ID field that took the last two digits of the year of the data entry and then appended the next count for the entry.
The fifth entry of the year 1999--Example: 01/6/1999 -- Created the following TA# 99-0005
I have records dating back to 1992 that I must retain and the numbers are already in place. I was already changed the 1900 and 1901 years to 2000 and 2001.
Now the problem. I know how to use autonumbering and how to set a new start number...
I can update the 2002 entries.. but can I get Access to do an automatic increment using the record event date entered in the form?
How do I create the number -- as the entry may be made a month after the event -- I can't use the date function of the operating system.
And I need to get the count to reset to 0001 at the beginning of a new year -- every year.
I teach basic applications, but I'm not a programmer and don't know vba or if that would be applicable here.
Thanks for any help...

 

[oharab]

Hi Clara.
What you will need to do is build the autonumber yourself.
Create a table in your database with 2 fields, a long field called "Autonumber" and a date field called "DateReset".
Put 1 and todays date in 1st record.

Copy this code into a module:

Function GetNextID() As String
Dim sTable As String
sTable = "tblAutoNumber"

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intRetry As Integer
Dim intNum As Integer, intA As Integer, intB As Integer
Dim strANum As String
On Error GoTo ErrorGetNextID

Set db = CurrentDb()
Set rst = db.OpenRecordset(sTable, dbOpenDynaset) 'Open table with the counter
rst.MoveFirst
rst.Edit

If Date = DateSerial(Year(Date), 1, 1) And rst!DateReset <> Date Then
'If today is the first day of the year and the date has not been reset then reset it to 1
rst!AutoNumber = 1
rst!DateReset = Date
End If

rst!AutoNumber = rst!AutoNumber + 1
'move the autonumber on.
'if another user is trying to get it, then an error is called.
'The error handler tries 100 times to get a number.
rst.Update
GetNextID = Format(Date, &quot;yy&quot & &quot;-&quot; & Format(CLng(rst!AutoNumber) - 1, &quot;00000&quot
'format our auto number into how we want it.
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
ExitGetNextID:
Exit Function
ErrorGetNextID: 'If someone is editing this record trap the error
If Err = 3188 Then
intRetry = intRetry + 1
If intRetry < 100 Then
Resume
Else 'Time out retries
MsgBox Error$, 48, &quot;Another user editing this number&quot;
Resume ExitGetNextID
End If
Else 'Handle other errors
MsgBox Str$(Err) & &quot; &quot; & Error$, 48, &quot;Problem Generating Number&quot;
Resume ExitGetNextID
End If
End Function

Now you can use this code on your form to add a new ID every time a record is added.

Let me know if you need any more help with this.

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[johngordon]

Ben,

Is there a way of inserting a save record function so the main table can be saved as soon as the record number is generated? The main table being named &quot;tblProcess&quot;.

Thanks for the help!

John Gordon

 

[oharab]

John,
The standard way for databases to work is to save the record at the end of the process, rather than at the beginning. If you save at the beginning and the user decides not to continue with the record, then you are left with partial or even empty records in your data, which is not good programming practice.
I totally suggest you use the before update event to input your autonumber so that the number is only issued if the record is saved.
If you MUST save at the beginning, then you will need to use the before insert event, which fires as the person typed the first letter of a new record, to insert your auto number then use:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
to save the record.


hth

Ben


----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[MichaelRed]

see faq700-184. In particular, if the db will be a Multiuser app (capability for more than one user to enter new data).




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[oharab]

Michael,
Have a star for superbly commented code. Comments are something I know are important & I know should be done as I write the code, but somehow I don't always get round to doing them!
Your code is a lesson to us all!

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

&quot;Where are all the stupid people from...
...And how'd they get so dumb?&quot;
NoFX-The Decline
----------------------------------------------

 

[MichaelRed]

sigh, alas a rather forced necessity in many instances, but thanks for the compliment

MichaelRed
m.red@att.net

Searching for employment in all the wrong places