Custom ID Field

[DBenedict]

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

 

[Arvin Meyer [MVP]]

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.

 

[DBenedict]

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]]

Answers in line:

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.

I like to save code like this in a standard module. I usually have multiple
utilities that I save and I can the import them as a module from database to
database. It's easy to spot since I name it basUtilities

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

Depending upon how you want to do it, I often just use something like:

=GetNextJobNumber()

in the DefaultValue box in the control's property sheet.

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

If it's in the DefaultValue property, it would happen automatically as each
new record was first started. You can also have it run automatically from
the form's Current event:

Sub Form Current()
If Me.NewRecord = True Then
Call GetNextJobNumber ()
End If
End Sub

In the DefaultValue, at least 1 character has to be typed to dirty the
record, whereas using it from the form's Current event, it will fire the
moment the new empty record is displayed.

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

Since it only happens once a year, I just push a button. You could even just
go into the table and manually change it. I prefer to keep users out of
tables so I write code that allows 1 user to enable a button to run the
code.

 

[DBenedict]

Thank You for the assistance, again. Very much appreciated as I am learning.

First major problem. Formatting...I can't get the format thing to work
right. The GetNextJobNumber() function works fine by the way. From the
immediate window and from the control’s DefaultValue on the form.

But I don't know how to format it in the function itself so its result is
outputted correctly to my table. In the format of TS-0800002. 08 being
2008 and 00002 being the next number.

The Format property in the form and table just affects how data is
displayed. It doesn’t store it how I need it. Access also adds its own “
“ when I type this into the Format field (form or table).

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

Same results with the field’s data type set to Text or Number.

Second minor glitch. The form displays the contents of the table to let
the user apply standard filters and scroll through records. Since the
GetNext function is used in a field on the form, when the user gets to the
end of the records, the function automatically runs. Even though they have
not entered any data or created a new record, the tblNextNumber is already
updated and that number is wasted. Tips?

I could change the Form’s Recordset Type or the Record Locks or make them
query a record but this design is what they are used too.

Couple other issues I ran across.
In the table, I had the JobNumber field’s DefaultValue set to
=GetNextJobNumber() and would get errors. Unknown Function...and things
about exceeding columns, locks and duplicates. So I couldn’t save the
function at the table level.

I thought I wanted it in the table so that only records "added" to the table
would trigger the GetNextJobNumber function. This would avoid the problem
stated above with the form. Access didn’t think that was a good idea. Why
not?

I can use two fields called Date and JobNumber and string them together but
this is only displayed, not stored to the table or queryable. The calculated
control displays it correctly as TS-071213. Not exactly what I’m after.

Can the GetNextJobNumber function output in my format? I tried a few
things but nothing….

Can the GetNextJobNumber function only run when a Record is created in the
table? Call it from the “Add Record†button?

 

[Arvin Meyer [MVP]]

Thank You for the assistance, again. Very much appreciated as I am
learning.

First major problem. Formatting...I can't get the format thing to work
right. The GetNextJobNumber() function works fine by the way. From the
immediate window and from the control's DefaultValue on the form.

But I don't know how to format it in the function itself so its result is
outputted correctly to my table. In the format of TS-0800002. 08 being
2008 and 00002 being the next number.

The Format property in the form and table just affects how data is
displayed. It doesn't store it how I need it. Access also adds its own
"
" when I type this into the Format field (form or table).

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

Same results with the field's data type set to Text or Number.

The field's datatype MUST be text. The exact expression, copy and paste it,
should be:

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

Pay close attention where the quotes and ampersands are, because both of
your examples above were wrong.

Second minor glitch. The form displays the contents of the table to let
the user apply standard filters and scroll through records. Since the
GetNext function is used in a field on the form, when the user gets to the
end of the records, the function automatically runs. Even though they
have
not entered any data or created a new record, the tblNextNumber is already
updated and that number is wasted. Tips?

The number should only be wasted with the first keystroke in the form.

I could change the Form's Recordset Type or the Record Locks or make them
query a record but this design is what they are used too.

Couple other issues I ran across.
In the table, I had the JobNumber field's DefaultValue set to
=GetNextJobNumber() and would get errors. Unknown Function...and things
about exceeding columns, locks and duplicates. So I couldn't save the
function at the table level.

Use the textbox's DefaultValue in the form, not the table. Users should
never be allowed to work in tables, especially in databases which cannot
prevent them from editing data in a table's field. In practice, I hide the
database window from users, and if I see that any of them are unhiding it, I
write code to prevent them from seeing it.

I thought I wanted it in the table so that only records "added" to the
table
would trigger the GetNextJobNumber function. This would avoid the
problem
stated above with the form. Access didn't think that was a good idea.
Why
not?

Access has no Triggers and cannot run user designed functions at the table
level. If you import records, you'll need to run the function from a query
and update the table. Users should never even see a table.

I can use two fields called Date and JobNumber and string them together
but
this is only displayed, not stored to the table or queryable. The
calculated
control displays it correctly as TS-071213. Not exactly what I'm after.

Can the GetNextJobNumber function output in my format? I tried a few
things but nothing..

Can the GetNextJobNumber function only run when a Record is created in the
table? Call it from the "Add Record" button?

Once again, forget using tables. If you need a datasheet, build one in a
form. Tables are for storing data only, not for adding, editing, or deleting
data. That should be done through code, queries and forms.

 

[DBenedict]

Still no luck I'm afraid.

Yes, my previous post contained a typo. I missed the & after the ="TS-" but
not in Access. But I copied and pasted like you said in the form's
JobNumber textbox Format field. And Access updates it every time I exit the
field in design view. Tried with and without the = sign.

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

When I exit the format box, it auto corrects me EVERY time. I even tried
removing the leading text "TS-" in case there were too many quotations.

= Format(Date(), "yy") & Format(GetNextJobNumber, "00000") becomes
"= For"m"at("d"ate(), yy) & For"m"at(Get"n"extJob"n\um"ber, 00000)".

Why? Maybe its my version of Access or some setting I don't know of in the
database options. I'm at a loss.

Yes, unfortunately the function updates the table tblNextNumber even without
creating a new Record in the form/table. No typing, no nothing.

When you open the form you see all 1209 records in the table. When you are
at the end looking at 1209th record and move the mouse wheel or even click
the next arrow to the blank record, the function runs. The entire form is
blank except for the Job Number which has advanced by 1.

The 1210th record appears in the Job Number field and (AutoNumber) appears
in the original Project Number field that I haven't deleted yet. I haven't
typed a single keystroke or moved the cursor in or out of a field. Just
rolled the wheel to the end.

Even happens in Datasheet View when you exit the last record and enter the
blank line.

When I close the Form, its Table is completely unaffecfted but the data in
table tblNextNumber has already advanced.

My Form is set to Allow Filters, Edits, Deletions, and Additions. Data
Entry = No, Dynaset, No Locks, Fetch Defaults = "Yes".

Because the Fetch Defaults is set to Yes, the function runs and a new number
is wasted IF you don't continue to enter data.

Set the Fetch Defaults to "No" and the function doesn't run just by entering
the blank record because the Default Values are not "Fetched". But then the
GetNextJobNumber() function does nothing and the Job Number is left blank
when you actually start typing new data. Getting Default Values are not
turned on.

Not sure how to solve any of this. Yet. I think I'll start with a fresh
database and build up a small table, then a form, and go from there. Maybe
it is something in the background from an older version? These could be
converted databases starting from Access '97, or 2000, then to 2002-2003.

Again, Thank You for your patience. I know this is possible and I will
keep trying!!!!!!!

 

[Arvin Meyer [MVP]]

There is definitely something wrong. I suggest pressing Ctrl+G to bring up a
code window, and in the Immediate window, paste:

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

You should get the correct number. If not, check your references (Tools >>>
References from any code window) and recheck the code in GetNextJobNumber to
make sure that you accurately copied and pasted it.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[John W. Vinson]

Still no luck I'm afraid.

Yes, my previous post contained a typo. I missed the & after the ="TS-" but
not in Access. But I copied and pasted like you said in the form's
JobNumber textbox Format field. And Access updates it every time I exit the
field in design view. Tried with and without the = sign.

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

Whenever Access starts retyping what I enter, I'd check two things:

- is Name Autocorrupt ... er, Autocorrect... turned off? If not, uncheck it on
Tools... Options... General; compact the database and see if it stops being so
unhelpfully "helpful".
- How about the spelling options on Tools... Options... Spelling...
Autocorrect Options? Might you at some point have somehow gotten the "
character involved? It seems to be turning quoted strings "inside out",
quoting the unquoted and unquoting the quoted.

John W. Vinson [MVP]

 

[DBenedict]

YES!!!

No matter what I did to try and enter data in the Format field, Access would
always try to correct me. Track name AutoCorrect... and Perform name
AutoCorrect are both turned on. I looked at the Spelling tab and
AutoCorrect Options, nothing I did helped. Access still updates things.

Since the Format property doesn't affect how my data is actually stored, I
thought that was not the option I wanted to take. I wanted it stored in my
format!!

I played in the Immediate Window and finally got it to output the
GetNextJobNumber in my format!!!! It displays and saves corretly!!!

I inserted a line just before the .Edit in the GetNext... function.

It looks like this...

With rst
GetNextJobNumber = !JobNumber
GetNextJobNumber = "TS- " & Format(Date, "yy") & Format(GetNextJobNumber,
"00000")
..Edit
!JobNumber = !JobNumber + 1
..Update
End With

Perfect!!!! The result is displayed and stored correctly and the table is
increased by 1.

The form issue with the function running when no record is actually created
in the table. The problem is that entering the blank line runs the function
set as the Default Value.

I've always noticed in the table or the form, that if you have Default
Values set up, the last line always displays what the data will be. If the
Default is a Date, the Date always shows in the blank record. If its the
number 0, it shows you a 0. What ever the Default value is, it shows in the
empty record.

Apparently, a user-defined function runs when you enter the record. And
entering data in the line creates a new record. Creating a Default Value and
creating a Record must be two separate events. I don't want someone to
accidentally enter the line until they intend to create a new record.

So I set the Form's AllowAdditions property to False. The form opens with
all records except for the blank line at the end. The only way to create
new project is to use the Add Project button.

The Add Project button updates the AllowAdditions property to True and then
adds a new record.

Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec

When the user clicks the Save Project button, the record is saved and the
AllowAdditions property is set back to False. Preventing the blank record
at the end.

I may want to prevent the user from exiting the record until they save it
but this should be easy.

My Custom ID is now working as I hoped it would. Thank You. Thank You.

 

[Jeanette Cunningham]

Hi,
so pleased to hear from that you got it working correctly.
About Track name AutoCorrect
It you leave Track name AutoCorrect turned on, you will have numerous
problems in your database in the future.
Turn it off now!!

Jeanette Cunningham

 

[Ed V.]

I have Access 2007 and I am experiencing the same problem with the quotation
being changed after I enter in my form property sheet in the format field the
line:

=Format(Date(), "YY") & Format(GetNextJobNumber, "0000") Access changes it
to "= For"m"at("d"ate(),yy) & For"m"at(Get"n"extJob"n\um"ber,0000)"

I am trying to do the same thing that DBenedict was trying to do with the ID
Field and Arvin Meyer was kind enough of showing a nice code on doing what we
needed to do.

And I am sure it has to do with autocorrect, but in Access 2007 I really am
not sure were to go to turn that option off.

Any help on this will be grately appreciated.

Thanks,
Ed