sequential number - restart october 1 every year

[Jeremy Ellison]

Please help with code for this problem. I have copied tons of code and tried
to put it together to make it work, but I am just not getting it to run or
come out right.

I need to create a sequential number for a form that will be printed. Each
form needs a unique and sequential number given to it when created. There
are four groups of people working on this database and each group will need
it's own set of numbers. Group #'s are 71, 72, 73 and 74.

Example:

Group 72

72-01-0001

(72 = Group#, 01=Month (January), 0001=sequentail Number)

The sequential numbers need to start over on October 1, every year. The
numbers can continue each month, starting at 1 on 10-1 and endingn at what
ever on 9-30.

Currently I am using a table called Telephone Info Request Table and I have
a field called FormNumber.

I would like to use just the one field to houes the long number for the
form, but am open to suggestions that may work better?

[Jeremy Ellison]

Another item to note is that this is a database used in a multi user
environment and I want to avoid duplicate numbers. Is there a way to do this?

[Klatuu]

It is highly unusual to be creating all these forms. In addition, forms are
not really designed for printing. Reports are better suited to that.

If you can post back with what you are doing, perhaps we can suggest a good
way to accomplish you goal.
--
Dave Hargis, Microsoft Access MVP

[Jeremy Ellison]

The number needs to be created in the form, but yes.... it will be printed on
a report...

I am using form to collect the data and then output that to a report that
prints the "work related document" --- I used the term form, which led you
believe I was creating forms...not the case... The document I am creating is
a piece of paper that is used internally at work and needs to have a unique
number assigned to it....

[Klatuu]

Good, thanks for the info.
How do you know which user belongs to which group? This will be important
to get this right.

The basic concept is you will have to have a table that carries the last
number created for each group.
When a user (I assume) clicks a command button to print the report, you will
need to know which group they belong to so you can find the current highest
number, add one to it, and write the new high number back to the table.

Now, some considerations.
In a multi user environment it is possible two users could grab the highest
number, so you will need to lock the record long enough for the user to get
the number and write it back out.
Now there is another issue. If the user starts to produce the report, but
cancels it or there is an error printing the report, you will end up with a
gap. in the numbering.

This leads me to a totally different approach. That is, to get the number
in the Load event of the report. The above concept would be the same, except
it would happen when the report is preparing to print.

[Jeremy Ellison]

I want to be as smart as you! ---> OK

The group number is currently stored in EmployeeTable and each employee has
a group number tehy are assigned to.

The employee table is linked to the Telephone Info Request Table by
EmployeeNumber.

I don't know if it matters but, an employee may create 5 + or maybe just 1
document to print out (using the form in the Data Entry Mode) and then after
creating all the documents use a command button to print the report (this is
a preview commmand then the employee uses the onscreen print button to
actually send the data to the printer). Does that make sense?

[Jeremy Ellison]

In speaking with some of the employeees ... I found out that they may be
assigned ot group 72, however they may be workign ona document that is going
to be used for another group.... I can create a radio control (is this the
right term?) where tehy can select which group the document is going to be
used for and that would then assist in creating the document number??????

[Klatuu]

I guess I missed the part about the Telephone Info Request Table and the
FormNumber field.

So, I am guessing you want to store the form number in the FormNumber field?

This would be better than an additional table. Now since I dont' know your
table structures, I can only give a generalized example of how it would work,
but basically, you will want to detemine the group by using a DLookup on the
employee table:

=DLookup("[GroupNumber]", "EmployeeTable", "[Employee = " & ???
??? means I don't know how you know which employee is using the form

Once you have the group number, you can use the DMax to return the highest
current form number and add 1 to it. The code would be similar to:

strNextNum = Nz(DMax("[FormNumber]", "[Telephone Info Request Table]",
"Left([FormNumber], 2) = " & TheGroupNumber),0)

strGroup = Left(strNextNum,2)
intMonth = Cint(Mid(strNextNum, 4, 2))
intNumber = Cint(Mid(strNextNum, 7))

If intMonth < Month(Date) Then
intMonth = Month(Date)
intNumber = 0
End If

intNumber= intNumber + 1

strGroup = strGroup & "-" & Format(intMonth, "00") & "-" & Format(intNumber,
"0000")

This will give you the next number.

[Jeremy Ellison]

Another Q -

I also have a "Save before Printing" butting that I have them push because
there is a check box on the form that depending on if it is checked or not,
the form won't print. So therefore i make them save the document and then
print which will grab all the documents that are supposed print out....
Maybe we could make this code Save the docuemnt, create the new docuement
number, then save it again (reducing the risk of duplicate
numbers)....although then this would keep incremeting the number becuase if
they push it 2 times it will grab a new number????unless we can have it only
grab a new number if the number is blank???

thinking out loud..

Jeremy

[Jeremy Ellison]

The form will know which employee is using the form by a combobox that
selects the EmployeeNumber from the EmployeeTable and then stores that in the
Telephone Info Request Table under EmployeeNumber...

I think I get how this is working, but how about getting it to reset to 0
for all groups on 10-01-2007 and then again in 08 and so on....

[Klatuu]

That would be a good idea. I would suggest an option group for this.
Set the option group's Default Value to Null. That way you can test to be
sure a group has been selected. Then for each control in the option group (I
would use Option Buttons with the group number as the caption), assign the
option value property of each button the Group Number. So the option group
will return Null if no group is selected, or the number of the selected group.

[Klatuu]

Good thinking, Jeremy. I would have to have a detailed description of how it
all works to offer much of a suggestion.
What kind of documents are these?
If they are not printed, how are they saved?

I do think the number needs to be created whereever the document is created.
The actual printing of the document is a different thing.

[Jeremy Ellison]

The document is always printed, but only printed one time and then it is just
stored electronically in the database..the original is sent out...The
document is a request for information sheet I created.

I am interested in pursuing this type of a function that after teh employee
enters all the data into the form they click ont he button to create a
docuement number which will then be immediately saved. Then they can move on
to creating their next or printing or what ever....

The other issue is the restarting the count at 0 on the 1st of October
(fiscal year).... not sure even where to begin that code.

What other information can I give you that would help with the code to
create this type of functionality?

[Klatuu]

It would be similar to the code I posted earlier, but at the time, I forgot
about the October, for some reason I was thinking it changed the numbe each
month. To restart it on Oct 1:

If Month(Date) = 10 And intMonth < 10 Then

intMonth = Month(Date)
intNumber = 0
End If

--

[Jeremy Ellison]

Im getting ready to leave work now, but I will work on putting it together at
home tonight...the only q I have now is in the first part you said you were
not sure how I was going to let the program know which employee should the
group number come from...what is the rest of the code now that you know that
the employee number will come from the employee table? I am lost on that
part a bit... Reference:

>=DLookup("[GroupNumber]", "EmployeeTable", "[Employee = " & ???
>??? means I don't know how you know which employee is using the form

The unique Identifyer in the EmployeeTable is EmployeeID ? Does that need
to be in there somehow???? this is what is used ot select the employee using
the combobox....

the other q I had is in the code you just listed for starting at 0 in
October.

If Month(Date) = 10 And intMonth < 10 Then
intMonth = Month(Date)
intNumber = 0
End If

Can you explain how that works?? I read it to say that it if the date is
October or less than October then the Month = the month, otherwise the number
is 0 ??? I'm confused as to how it works and what happens in Nov and Dec and
how will it know to reset to 0 ...what if the first document isn't created in
2008 until December and the last one in 2007 was September???

[Klatuu]

This code:

=DLookup("[GroupNumber]", "EmployeeTable", "[Employee = " & ???

Is for looking up the Group Number; however, if you plan to put it on your
form and allow the user to select it, it will not be necessary.

The problem still, is how do you know what user has the form open?

I just had a thought. If you have users sharing the the same front end mdb,
all of this is going to be a problem. You should have a copy on each user's
computer linked to the back end mdb where the table are.

[Jeremy Ellison]

I was playing around with the idea of doing that as well. I am now pulling
my hair out trying to get this to work........

Ok - I am goign to have the employees fill out the form completely and then
have a button at the end that says, "Get Document Number". I want the code
to then look at the data that the employee has filled in, which will include
the group number (needs to be obtained from the employee table based on the
employee number that is in the current form), the month (which can be taken
from the datesent field or from now() ??? ) and then of course the unique 4
digit number for each document being created (unique for each group
number.....& starting over at 0 on October 1st). The resulting number will
then need to be stored in a field called FormNumber. example of the result
of the calculation would be 72-01-0001

The end of this command should save the record and thus reduce the risk of
duplicate numbers.

????

I just don't know how to write that into a code to make it work....

[Klatuu]

Working late last night, were you?

I will be happy to help with this, but I do need to know how to know which
employee is using the form.

One other idea to pass by you regarding getting the group number. As you
noted, we can retrieve the group number from the employee table, but it is
also possible they can be working on another group's work. My idea is to use
an option group as I described previously to allow them to select a group,
but have the option group default to their normal group.

[Jeremy Ellison]

Each employee will be selecting their name while filling out the form, using
the combobox. This combobox allows them to select (or type obviously) their
name and it is tied into their EmployeeID (which they never see and is the
primary key). Is that what you wanted to know? In Reference: > I will be

happy to help with this, but I do need to know how to know which
> employee is using the form.

---
And yes, I like that idea too of having the default group number being the
group number the employee is assigned to...so this will default when the
employee is filling out the form? OR - Is there a way to get the
information they used to sign into the database and use that to look up their
EmployeeID ??? They all are required to sign into the database (as this was
the only way I figured out how to set the security so they couldn't make
changes) using their first initial middle initial and last name. Not
sure if this information is available to retrieve or not..?

Thanks again for your help.. (I really need it!)

[Klatuu]

Great info, thanks Jeremy. It is really easy to help someone who askes good
questions and provides good info.

Let's start with the combo where they select their name. Make it a 3 column
combo based on the Employee table:
SELECT EmpID, EmpName, GroupNum FROM Employees;

Set the following properties of the combo box:
Column Count 3
Bound Column 1
Column Widths 0";2"; 0"
(This will make only the Name visible - Adjust it's width to suit)
(Note that combo columns are zero based. That is, cboEmployee.Column(0)
will return the EmpID. The Bound Column is 1 which is the EmpID, but it is
Column(0). Thank you, Microsoft!)

Now, in the After Update event of the combo box, we can set the value of the
Option Group:

Me.opgGroupNumber = Me.cboEmployee.Column(2)

Remember, in the Option Group, there needs to be a button for each group
number. Make the Caption the same as the Number, and make the Option Value

of each button the Group Number.

At this point, we have (I think) all the data we need to create the record.

Let's get this far first, then we can see what we need to do next.

[Jeremy Ellison]

ARRRRRRRRRRRRRRRRRRRRRRRRGGGGGGGGGGGGGGGGGGGGG ---> OK so you said that part
about the first column being 0 and I read it...but after 3 hours of changing
things around it finnally sunk in and I got it right now :) ---> The issue
I was having is becuase I wanted ot have the combo box have the first and
last names in it as well so I ended up with a total of 5 columns (column 5
being the group number) ...and so being as I wanted to use column 5 (which
I should have been calling it 4 in my programming language)...well, you get
the point...

NOW we are where we should have been three hours ago... I have the combo
box set up to put the group number value in a field called GroupNumber which
is where the Option Group (called Group) also stores its value...so when I
do the drop down it then puts the value into the field GroupNumber and the
optiongroup then displays the correct group and if I want to change the group
for which the document will be made, it will then change that value with the
option group buttons :) Whew....thnx for the help on that... now I think
we can move on to creating the numbers... right???? I think you have all
you need to help with that right????

[Klatuu]

Isn't this fun :)

I just thought of a problem while I was begining to write the code. You
want to start renumbering each October 1st, but there is nothing in the
number that tells you which year it is. What is your plan for dealing with
multiple years?

Also, a numbering question. So the first record for October for Group 72
would be:
72-10-0001
Let's say the last number in October happen to be 72-10-0905
Now it is November 1st. (Only 3 weeks til Turkey Day!)
will the first record for November be
72-11-0906 or 72-11-0001

Let me know so I can get on it.
BTW, where are you located? (just curious)

[Jeremy Ellison]

I am in St. Paul MN - Sorry about the delay, I was in training all day and
not able to get access to a computer!

The start over date will be 10-01-2008, and 2009 etc...so the last number in
September could be 72-09-0905 and the first # in October will be
72-10-0001

I don't know why my boss wants teh code to contain the month of the year and
not the year.... just a note,, there are fields within the form that contain
the Datesent (full date) and TimeSent if that helps to get the date or
maybe we dont need it?

I am thinking I might want to talk to my boss and tell him that we should
track the documents using the number format below instead of teh previous one
with a month /...

72-07-0001
Group-Year-Number

??? Just a thought... i think it makes more sense...but I would imagine the
code would not be that different to take the month / versus the year....

[Klatuu]

Or Group-Year-Month-Number

Without the year, you will not be able know which record is which, because
come October of 2008 you will have two records with
72-10-0001
So, which is which?
I would recommend Group-Year-Month-Number because it will sort well that way
and queries against it will run faster.