AutoGenerate File number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that is used for data entry. Each new rocord is assigned a
specific file number and is use as the primary key on my table. Currently
this file number is entered by the user however I would like to have these
numbers produced by the application. Each file number is alphanumeric (eg:
0607HQ1234). Is there a way to do this in access?

Z
 
IF there is a predictable way in which these file numbers are created, then
it can probably be automated. For example, if your file number 06007HQ1234
is comprised of the year, month, customer's initials, and a sequential
number, then you could generate that on the fly for use as a primary key when
you create a new record.

Without a fixed pattern for the file number, it may be easier in the long
run to continue to manually enter it.
 
It can be done, but to show you how to do it, We will need to know the rules
for generating the number.
 
The rules for the numbering are quite simple.

Essentially the number is comprised of the current fiscal year (0607) the
region where the user is (eg: WA - Western Area, HQ-HEadQuarters, there are 5
option buttons on my form that relate to the various areas) , and the final 4
digits are based on the number of records (0001,0002,0003--1001). I would
like to have the four final numbers to be based on the number of records in
the respective region. For example, the WA (western area) may have 150
records whereas the HQ might only have 26 therefore it would be nice to have
this displayed as 0607WA0150 aqnd 0607HQ0026.

Hope this is clear enough.

Z
 
That is about how I thougt it would be. I will leave the construction of the
first part to you. The code below will create the four digit number to
append to that. Normally, you would use the Current event of the form to
create a number like this if it is a new record
If Me.NewRecord Then....

However, since the other parts of the number have to be determined before
you can do this, I am not sure where you will need to put the code. If it
can wait until all other data for the new record has been created, then I
suggest the Before Insert event. The advantage here is that there is less
likely to be a conflict in a Multi User environment. The code below will
assume it is the Before Insert event and will include code to handle a
multiuser situation.

(strFirstPart) I will use as the other part of the number constructed from
the date and your other controls, so you will need to have it available for
this procedure. I will also assume the first part will always be 6
characters as in your example.

Do Until blnGoodNumber
strNextFileNumber = strFirstPart & Nz(DMax("[FILE_NUMBER]", _
"MyTableName", "Left([FILE_NUMBER], 6) = '" & strFirstPart & "'"),
0) +1
If DLookup("[FILE_NUMBER]", "MyTableName", "[FILE_NUMBER] = '" & _
strNextFileNumber & "'") Then
blnGoodNumber = True
End If
Loop


If Me.NewRecord Then
 
Great, thanks. I'll give this a shot and see how it goes.

Z

Klatuu said:
That is about how I thougt it would be. I will leave the construction of the
first part to you. The code below will create the four digit number to
append to that. Normally, you would use the Current event of the form to
create a number like this if it is a new record
If Me.NewRecord Then....

However, since the other parts of the number have to be determined before
you can do this, I am not sure where you will need to put the code. If it
can wait until all other data for the new record has been created, then I
suggest the Before Insert event. The advantage here is that there is less
likely to be a conflict in a Multi User environment. The code below will
assume it is the Before Insert event and will include code to handle a
multiuser situation.

(strFirstPart) I will use as the other part of the number constructed from
the date and your other controls, so you will need to have it available for
this procedure. I will also assume the first part will always be 6
characters as in your example.

Do Until blnGoodNumber
strNextFileNumber = strFirstPart & Nz(DMax("[FILE_NUMBER]", _
"MyTableName", "Left([FILE_NUMBER], 6) = '" & strFirstPart & "'"),
0) +1
If DLookup("[FILE_NUMBER]", "MyTableName", "[FILE_NUMBER] = '" & _
strNextFileNumber & "'") Then
blnGoodNumber = True
End If
Loop


If Me.NewRecord Then


Zoltar54 said:
The rules for the numbering are quite simple.

Essentially the number is comprised of the current fiscal year (0607) the
region where the user is (eg: WA - Western Area, HQ-HEadQuarters, there are 5
option buttons on my form that relate to the various areas) , and the final 4
digits are based on the number of records (0001,0002,0003--1001). I would
like to have the four final numbers to be based on the number of records in
the respective region. For example, the WA (western area) may have 150
records whereas the HQ might only have 26 therefore it would be nice to have
this displayed as 0607WA0150 aqnd 0607HQ0026.

Hope this is clear enough.

Z
Click to expand...
Click to expand...
 
Back
Top