Create and store unique record number.

  • Thread starter Erik S. via AccessMonster.com
  • 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
 
B

BruceM

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
 
E

Erik S. via AccessMonster.com

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!
 
B

BruceM

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.
 
Top