HELP WITH AUTO GENERATE STRING

[Guest]

Please I need a help from you all. I have an MS-Access Database Program that
I wrote by converting from Lotus Application into Access. This program is now
working very well

My problem now is that my boss wants me to add an Auto generated Field in
the program. But this field will be partly generated from three other fields
that is the puzzling aspect of this program. In essence, he does not want me
to use the inbuilt auto generate, date and year that is in the system, rather
the ones the user will enter in other fields in the program.

TO BRIEF YOU:
I have a field called (1) DateRec, another (2) Municipality and another (3)
Type. These three fields invariably will automatically generate a string of
number for another field that will be called FileNumber. This FileNumber is
already the Primary Key in the program. The (1) is the date received, that is
the date the application of the client was received. (2) being the
Municipality will have one or two letter characters of each Municipality
within the County where the applicant is from. And (3) which is the type is
the state of the application, it will only be involved in the FileNumber,
when it is an "EXEMPT", then, the nomenclature will be; an 'E'.

Here comes the drill! When the user enters the date received, it will be a
complete date, with day, month and year. The only thing that will go to the
FileNumber will be the year, like 2006. When a Municipality is chosen by the
user, for instance Atlantic City, it will generate a nomenclature like 'AC',
if it is for Brigantine it will be a 'B'. Then the program will run a query
that will check for the last FileNumber entered under that Municipality
chosen and update it with one number. So the FileNumber will be something
like AC-2-2006, for another entry on this same Municipality it will be
AC-3-2006, AC-4-2006 etc. Then, when there is an Exemption in the
application, the user will select EXEMPT from Type, it will emerge the 'E'
with the middle number; like AC-5E-2006. This is all that is needed.

I definitely, need a help to get this working. The purpose for this is to
debar the user from entering wrong Filenumbers.

Thanks in anticipation for your help.
Anthony

 

[BruceM]

Do I understand you that the file number is now assigned manually, and you
want to automate the process? If so, a few questions before I can maybe
suggest a solution:
1) What is the source of the municipality code?
2) Is the municipality code unique for every municipality, or do two cities
whose names begin with "B" have the same code?
3) Will there be (to borrow from your example) a file number AC-5-2006 and a
file number AC-5E-2006, or is the 5 only used once, whether or not exempt?
4) What happens if the middle number goes above 9? The reason I ask is that
if you are able to pad the number with a 0 (AC-05-2006), or with as many
zeros as is necessary to ensure that all numbers will be the same length, it
will simplify things quite a bit.

 

[Ron2005]

Another question.

Do you have a table of cities and codes to use? Because if you do not
then a town like Brentwood and Brigantine will generate the same code.
Basically this is the same question as BruceM asked in question 2.

And as he suggested, having a fixed format (always the same number of
characers in each of the name parts would make it a whole lot easier to
break down and sort later.

Ron

 

[tony]

Sine the township and the year are predetermined based upon the user
input
you have to exaime the prevoius numbered file no's for the township
Use the left() function to select the last township filenumber used
then
Using the Mid() function , examine the actuial sequential number that
was last used
since wou wioll be dealing with 09 and 99 and 199 etc. you will need to
create a string conditional on those situations... you will need to use
the IIF() function ie.
IIF(mid([filenumber],6,1)="9",IIF(mid([filenumber]4,2)="99'),
FORMAT([filenumber]+1),"0"&fFORMAT([filenumber]+1))etc and FORMAT
function to properly format the final concatinated string.

I just completed a similar process for creating permit numbers for
temporary food events in our county our numbers look like this
2785-003-2006
Hope that helps

 

[Guest]

Hi BruceM & Ron,
I want to answer your questions at thesame time.
Yes the File Number is now manually entered, and we want to change it to
automation.
The Municipality has a field in the table and a combo box with the names of
all the Municipalities ( 23 in all to be exact). But there is no code yet
attached to these muncipalities.
The municipality code will be unique to each one. I just have the codes hand
written not yet sure how I will relate them into the program. (They are like
these: AB, AC, B, BB, BV, C, ET, EC, E, F, G, HL, HM, LW, LP, MG, MU, N, PL,
PR, S, V, W).
There will not be anything like AC-5-2006 and AC-5E-2006. The 5 will be used
only once because it will always increment at every new application within
the municipality. So whether or no exempt, it must increment each time.
Look at the middle number as a counter that will help the program to track
how many files that has been opened in a certain municipality, within a
certain year. So it can increment in a geometric progression, beyond tens,
and hundreds assuming there is that much file opened for a particular
municipality.

Thanks Guys. I still waiting for your response or for other inputs.

 

[Ron2005]

I am just tossing out some ideas here.

1) definitely add to the Municipality table the abreviation that you
want to use for it and check that it is NOT already in use when the
municipality entry is created.

2) I would have to think more about this, but if the table that ended
up having the document key as a primary key also had the 4 components
of that key as fields (or perhaps actually have the primary key(s) for
the file be the combination of those 4 fields and whenever you needed
to print the document key just construct it from those fields. That
would make finding the next # for instance as simple as a "sum" query
conditioned by the municipality abreviation and Year and "last" for the
# component and that could be used in a dlookup. It eliminates ALL the
hastle of trying to de-construct the document key to find the
components and adds all sorts of extra versatility into counting or
selecting or quering by municipality and/or year and/or Exception. Even
if the component fields were duplicated (redundent on the record I
think it would add a degree of versatility and ease of use that outways
the breaking of the normalization rule). For instance, as of now it
would seem to be really difficult to find/count all the entries for a
year since you do not know where the year starts - makes writing the
query a bear to maintain 6 months from now after you have forgotten all
the ins and outs of the application.

Just some thoughts....

Ron

 

[BruceM]

I haven't had much time today, but I will take a moment to weigh in with a
slightly different angle than Ron is taking.

If you have a Municipality table with columns for the Municipality and the
code, you can use that as a combo box row source. Set the Bound column to
1, the column count to 2 and the column widths to 1";0". The second column
will be referenced in code as Column(1), since the first column is 0. With
that in mind, something like this is a start as, perhaps, the After Update
event for the combo box (cboMuni) from which you select the Municipality.
It could also be in the form's Before Update event, or maybe another place.
We can leave the specifics for another time.

Private Sub cboMuni_AfterUpdate()

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

strWhere = "FileNumber Like """ & Me.cboMuni.Column(1) & "*"""
varResult = DMax("FileNumber", "tblMunicipality", strWhere)

If IsNull(varResult) Then
MsgBox "varResult is null"
Me.txtFileNumber = Me.cboMuni.Column(1) & "-001"
Else
Me.txtFileNumber = Left(varResult, 3) & _
Format(Val(Mid(varResult, 4, 3)) + 1, "000")
End If
End If

End Sub

This code doesn't do all that you need, in that it doesn't add the date to
the end. I have used variants of this code, but only when the incrementing
part of the number is at the end. When it is in the middle it doesn't quite
work the same way. It may be necessary to extract that portion of
FileNumber through the incrementing part, increment that, and reassemble it
with the date. The problem is that next year you want the numbers to start
over with 1. I haven't quite worked that out yet.

Note that I used Mid instead of Right in the expression. This is in
anticipation of the year being added to the end of the number.

I have a sort of similar project coming up, so I hope to put some more
thought into this on Monday, in part so that I can apply it to my own work.

By the way, in a multi-user environment you will need some way to handle a
duplicate file number being created, which could happen if two users start a
record about the same city at about the same time. The one who finishes
second will need a different number, so maybe the form's After Update event
would be a good place for the code, as it will minimize the chance of
duplication errors.

 

[tony]

Here's the complete code for the creation of the middle part of the
number
I used this in a query for a sub form on my input form .

THe process is similar to yours
We select a municipality from a combo box there is a 4 digit
municipality code, "on exit " event of that selection uses a query to
select all of the previous events from that municipality for that
permit year.
Then we examine the middle( sequential number) using the MID funtion to
pull it out of the permit number, we select one record, sorting in
descending order( that gives us the last number used for that
municpality)
Then we examine that number more closely using this formula
Expr4:
IIf(Left([expr2],1)="0",IIf(Left([expr2],2)="00",IIf(Right([expr2],1)="9","0"
& Format([expr2]+1),"00" & Format([expr2]+1)),"0" &
Format([expr2]+1)),Format([expr2]+1))

expr2 is the sequential portion of the last number used

Expr4 now becomes the number we will use for the new permit

we then concatinate the original town code & expr4& the end tag ( in
our case it is "2006"
when we get to the next year we just modify the end tag to read 2007(
that end tag can be part of the original search query so you can keep
years of permits in a group for counting purposes)

as before our final creation looks like this 2856-001-2006

Hope that gets you a bit further

 

[BruceM]

From my previous posting:

If you have a Municipality table with columns for the Municipality and the
code, you can use that as a combo box row source. Set the Bound column to
1, the column count to 2 and the column widths to 1";0". The second column
will be referenced in code as Column(1), since the first column is 0. With
that in mind, something like this is a start as, perhaps, the After Update
event for the combo box (cboMuni) from which you select the Municipality.
It could also be in the form's Before Update event, or maybe another place.

Now, back to today's posting. Here is some sample code:

Private Sub cboMuni_AfterUpdate()

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

strWhere = "FileNumber Like """ & Me.cboMuni.Column(1) & "-" & "*" &
"-" & Format(Date, "yyyy") & "*"""
varResult = DMax("FileNumber", "tblMunicipality", strWhere)

If IsNull(varResult) Then
Me.txtFileNumber = Me.cboMuni.Column(1) & "-001-" & Format(Date,
"yyyy")
Else
Format(Val(Mid(varResult, 4, 3)) + 1, "000") & _
"-" & Format(Date, "yyyy")
End If
End If

End Sub

It seems to work, with some caveats. This assumes a two-letter municipality
code and a three-digit sequence code (eg. AC-001-2006). If the municipality
code is to be either one or two letters in length, you will need to do
something like test the length of Column(1), then run a variant of the code
if the length is 1. Perhaps there could be an ElseIf section of the code,
maybe something like:

ElseIf Len(Me.cboMuni.Column(1)) = 1 Then
Me.txtFileNumber = Left(varResult), 2) &
' the rest of that section of code
Else
Me.txtFileNumber = Left(varResult, 3) & ' etc.

If the sequence code is not to include leading zeros, then things will get
pretty complicated, at least as far as I can figure out. The section of
code:
Format(Val(Mid(varResult, 4, 3)) + 1, "000") would not contain Format, and
would vary for each of several conditions. For instance, if the
municipality code is two letters and the sequence number is a single digit,
the code may be something like:
Val(Mid(varResult, 4, 1)) + 1
If the municipality code is a single letter and the sequence number a single
digit, the code may be:
Val(Mid(varResult, 3, 1)) + 1
If the municipality code is two digits and the sequence number two digits,
the code may be:
Val(Mid(varResult, 4, 2)) + 1
And so forth. I have not tested these variations.
You would probably do something like check varResult to see if the second
character is a hyphen (meaning a one-letter municipality code). If it is
one letter, check to see if the length is 8 (which means that the sequence
number is a single digit). If it is a single digit, use a variant of the
code as suggested above. If it is two digits, use a different variation.
Inserting the "E" for exempt into the middle of the number is pretty
straightforward if you simply add it as needed to the number in an unbound
text box on the form or report (or if you bind the number text box to a
calculated query field that inserts "E" into the number as needed). If you
try to insert it into the stored number, determining the DMax value becomes
a problem I cannot solve. Remember that this is a database, and that you
will not be looking directly at the stored values. You can mainpulate them
as you choose for the prupose of viewing. If you want to see all Exempt
records, you can devise a query that will find just those records.

Whatever you do, you will need to guard against duplicate values in the
FileNumber field if you are in a multi-user environment. This could happen
with the above code if one user starts a record about a particular
municipality, then another user starts a record about the same municipality.
This first user to finish (and save the record) will receive the correct
number. The other will receive an error message. One way in which you can
avoid this is to defer applying the number code until the form's Before
Update event. This will guard quite effectively against duplicate primary
key (or in any case, unique) values in [FileNumber]. You can also use error
handling to go back and get another number if there is a duplicate value.

 

[Zetony]

Hi Tony,
I really due appreciate every one of you that has made a contribution
to this program. I have to tell you all that right now I have got this
aspect of the program to work in part.

I have written a program that is generating the Municipality
nomenclature and the year, effectively and effecient as it should be.
But the middle number that will increment is the area I'm now
struggling.

Trying to use Tony's code is not working out. Then I want to know in
this Tony's code, whether it's an IF() (function) or is it a different
function that is confusing me.?

Right now I'm trying to use DMax function, But it runs with an error.
It's producing an error like "wrr-123s". The number in quote which is
producing I don't have such a number in my table or any other place
within my program.

I will appreciate it very much if any one can help me to solve the
puzzle of this middle number.
Thanks.

 

[BruceM]

Not to take anything away from Tony's answer, but did you look at the VBA
code I suggested? I don't understand what Tony is doing with his code, so I
will leave him to address questions about it. There is often more than one
way to handle a particular problem, and I can't say which approach would be
better for you. I do know that my system works, but I still don't know if
you are able to use a three-digit sequence number (the incrementing part):
001, 002 ... 999, or must it be 1, 2 ... 10, 11 ... 999. Also, I don't know
if the municipality code could be either one or two letters in length.

 

[John Vinson]

Right now I'm trying to use DMax function, But it runs with an error.
It's producing an error like "wrr-123s". The number in quote which is
producing I don't have such a number in my table or any other place
within my program.

Please post your *actual* code and the *actual* error message,
together with an example of the data that the code is using.

John W. Vinson[MVP]

 

[tony]

Hi again
sorry about all the confusion regarding this process
In looking at this again, it may be necessary to further separate the
components of the number, particularly the middle. What you need to do
there is have the sequential number and then have the added "E" if
necessary or a blank space "" if it is not needed. the need to examine
the leading 0's is only htere if you are using a 3 digit number in the
middle.. if not then separate the last used number using dmax or using
the decending order in the form /query for the last single record.
We prefer the 3 digit because we are not going to have more than 999
permits in a partuicular town for a partticular year. Using a 3 digit
code allows easy location of that part of the last number used.ie
Mid([xfield],6,3)
if you were going to issue more than 999 items you could use a 4 digit
number in the middle...
I chose the parsing format because of the text/number issue, that is
why I used the format function

if you post the sql of a simple select query that contains the fields
you need to create the number , I would give it a go at creating the
formulation to create your new numbers

Tony

 

[tony]

Hi again
sorry about all the confusion regarding this process
In looking at this again, it may be necessary to further separate the
components of the number, particularly the middle. What you need to do
there is have the sequential number and then have the added "E" if
necessary or a blank space "" if it is not needed. the need to examine
the leading 0's is only htere if you are using a 3 digit number in the
middle.. if not then separate the last used number using dmax or using
the decending order in the form /query for the last single record.
We prefer the 3 digit because we are not going to have more than 999
permits in a partuicular town for a partticular year. Using a 3 digit
code allows easy location of that part of the last number used.ie
Mid([xfield],6,3)
if you were going to issue more than 999 items you could use a 4 digit
number in the middle...
I chose the parsing format because of the text/number issue, that is
why I used the format function

if you post the sql of a simple select query that contains the fields
you need to create the number , I would give it a go at creating the
formulation to create your new numbers

Tony