Create and store unique record number.

  • Thread starter Thread starter Erik S. via AccessMonster.com
  • Start date Start date
E

Erik S. via AccessMonster.com

I've been searching through the threads trying to find a post that
matches my question but I can't seem to locate one. I need to have the
form automatically generate a unique number in a specific format, and I
need that number to be stored in the database so it would be searchable.
What I'm trying to do is generate a number that is based upon the year,
month, then a 3 digit number on the end such as 05.04.001 for 2005, April,
001. I also need this number to change based upon the month and year. I
have field for the record number to be displayed at the top of the form and
I'd like for this number to show in that field.
In my data table I have 2 fields that I can use to generate this number.
RecordID field and RecordDate field. I just don't know how to get it to
generate and display the number. I tried using some previous post
suggestions to figure this out, but I either get compiler errors or it
simply doesn't work. Please, if anyone can help I would greatly appreciate
it.

Thanks
 
Erik said:
I've been searching through the threads trying to find a post that
matches my question but I can't seem to locate one. I need to have
the form automatically generate a unique number in a specific format,
and I need that number to be stored in the database so it would be
searchable. What I'm trying to do is generate a number that is based
upon the year, month, then a 3 digit number on the end such as
05.04.001 for 2005, April, 001. I also need this number to change
based upon the month and year. I have field for the record number to
be displayed at the top of the form and I'd like for this number to
show in that field. In my data table I have 2 fields that I can use
to generate this number. RecordID field and RecordDate field. I just
don't know how to get it to generate and display the number. I tried
using some previous post suggestions to figure this out, but I either
get compiler errors or it simply doesn't work. Please, if anyone can
help I would greatly appreciate it.
Click to expand...

You don't say where the date comes from nor do you say how the 3 digit
number changes based on month and year.
You can just store the date and format it (yy.mm) to give the value you
want.
If the number starts over based on some value then you will need a table to
store the number. Retrieval, updating and resetting would be done in code.
It would be better to have to fields and use a calculated field to display
them
= YourFormattedDateField & "."& YourNumberField.

This approach usually costs a client extra and after a year or two they say
"You know, you were right. We really don't need and hardly ever use that
number anymore now that we have a computer."
 
I am indebted to Allen Browne and Tim Ferguson for helping me with a solution
to a similar problem. Allen provided the code, and Tim tried to explain
things about it that were not clear to me. I adpated the code to your
situation, but I started from scratch rather than incorporating the two
fields you already have. Keep those fields if you have reason to do so, but
you don't need them to generate the number. This code puts the entire number
together in one shot. I have to admit I don't fully understand how the code
works, in particular the variant. Having said that, it does seem to work,
and I expect it will start over at 05.05.001 in May, although I have not
tested it on a new month. Place the following code into your forms's
OnCurrent event. YourNumber is the name I have given to the record source
(table field) that will contain the number. tblYourTable is the name of the
table. Bind a text box to the record source if you want to view the number
on your form. You may want to add error handling to the code. All I have
included is what goes between Private Sub and End Sub. Line wrapping (after
Else) is at your preference. Remove the underscore if you don't want the
line to wrap in the code window.

If Me.NewRecord Then
Dim strWhere, strYrMo As String
Dim varResult As Variant

strYrMo = Format(Date, "yy") & "." & Format(Date, "mm")
strWhere = "NumberTest Like """ & strYrMo & "*"""
varResult = DMax("NumberTest", "tblYourTable", strWhere)

If IsNull(varResult) Then
Me.NumberTest = strYrMo & ".001"
Else
Me.NumberTest = Left(varResult, 6) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If
 
Thanks for the feedback guys. A little more info on the data. The data
will be stored in one table in different fields, however I need the number
that is auto generated to be stored in the table, not simply displayed on
the form.

I'm going to try the code you suggested Bruce. I'll let you know how it
goes. Again, thanks for your help!
 
Responses inline
Thanks for the feedback guys. A little more info on the data. The data
will be stored in one table in different fields, however I need the number
that is auto generated to be stored in the table, not simply displayed on
the form.
Click to expand...

YourNumber is the name I gave to the field in the table that will contain
the number(not to the text box in which the number displays). Call the text
box txtYourNumber to distinguish it from the field. Set the record source
for txtYourNumber to YourNumber to see the number on screen, but whether or
not you have a control on the form bound to that field, the On Current event
will store the number in the YourNumber field in the table.
If you must store the month and year in two digit format in separate fields,
set the value of the fields to Format(Date(),"mm") and the same for "yy", but
I must say it is difficult to imagine the purpose of that exercise. If you
want to look at the month, the year, and the last three digits as separate
entitities on the form you can use Left, Right, and Mid functions to parse
the number generated by the code.
 
Back
Top