Re: Custom ID Field

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thank You very much for the code. As I read through it, it is starting to
make sense. Declare the variable/datatype, the SQL statement, point to the
current database, open the Recordset, find the number, etc.

I understand the concept, but am having trouble tying everything together.

1). Code is better saved in a new Module or on the Form? The form is the
only place I can see where I'd use it.

2). Where to insert my Job Number format? Under the field's properties in
the table?

3). Triggering the code? Call it from the "New Record" button?

4). The ResetJobNumber() function is initiated by me, or automatically?

Thanks.

"Arvin Meyer [MVP]" wrote:

You are in luck, because I've already written all the code you need. First
you need a table with a single field. In my code it's tblNextNumber with a
field JobNumber. Here's the code:

Public Function GetNextJobNumber() As Long
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select JobNumber From tblNextNumber"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
GetNextJobNumber = !JobNumber
.Edit
!JobNumber = !JobNumber + 1
.Update
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Function

Sub ResetJobNumber()
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "Select * From tblNextNumber"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

With rst
.Edit
!JobNumber = 1
.Update
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub

Your JobNumber will look like:

= "TS-" & Format(Date, "yy") & Format(GetNextJobNumber, "00000")

In the new year, you'll run the ResetJobNumber() function.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"DBenedict" <DBenedict@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:50B7884A-1B6F-4F4F-8895-E104A9F97028@xxxxxxxxxxxxxxxx
Office Professional 2003.
I've looked through a few posts but haven't found any that address all of
my
needs (or requests).

I'm assisting with a project database that has an ID field like,
TS-0700001.
The "TS-" is text representing the department, the "07" is the current
year, the "00001" is a number that increases by 1 for each new project.

(I think I will modify it so the text portion "TS" is taken from a combo
box
so we can select other departments. In that case, letters could be "TS",
"QC", "RD" and it would come from a field. This make it easier to insert
into an ID than just inserting plain text?)

Currently, the ID field in the table is set to AutoNumber and the Format
is
set to "TS-07"00000. This is wrong. Each year the format has to be
updated to increase the year portion of the ID. To do this, they create
a
new table and change the Auto Number format to "TS-08"00000. This
prevents
old "07" records from being changed to "08" and it also starts the project
numbers over at 00001.

What I need to design is this. For each new project, an ID is generated
using a button on a form.

The ID is built using the combo box field "TS-", then it adds the year in
the format of "yy", then it finds the next project number and finally,
saves
the ID in the ID field.

In addition, they want project numbers to recycle or restart at "0" each
year. If the last record for 2007 is TS-0704515, the new record for 2008
should be TS-0800001. There is no possibility of passing 99999 projects.

Also, I have to consider a multi-user environment. I think I should
create
the ID and save it to the table instantly so it can't be duplicated or
conflict with another user.

So this is it. From the open form I would create the ID, save it to the
table, populate the form with the new ID so it can be edited without
interfering with someone else who may be creating their own record.

I could leave the table field as a number and use formatting on the Forms
and Reports to create the illusion of a custom ID. (TS-0704515). But I'd
prefer to create it and save it as TS-0704515.

I'm sure the Data Type can't be Auto Number in this case, so probably
text?
I am open any suggestions.
Thanks, Dan



.

Relevant Pages

  • Re: Booking System - Vacant room report
    ... in what format? ... within the date range as a field in the recordset, ... Dim colDates As Collection ... Set rst = CurrentDb.OpenRecordset ...
    (comp.databases.ms-access)
  • Re: SQL Query between 2 dates
    ... dates which are being input are in the format of dd/mm/yyyy. ... Dim rs AS DAO.Recordset ... Set rst = dbEngine.OpenRecordset ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: Custom ID Field
    ... current database, open the Recordset, find the number, etc. ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ... Each year the format has to be ...
    (microsoft.public.access.formscoding)
  • Re: Custom ID Field
    ... field JobNumber. ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ... the ID field in the table is set to AutoNumber and the Format ...
    (microsoft.public.access.formscoding)
  • Custom ID Field
    ... created the table called tblNextNumber and the text field called JobNumber. ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.formscoding)