Counters that reset by day/year

Hey experts,

For the database project I'm working on now, there is a form that gets numbered sequentially by day, as in the first form created would be P001, second form P002, and resets every day, so tomorrow it would start back over at P001, etc.

I am looking for a way to get this done. I have been trying to figure out a counter that would handle it, but I don't know how I would reset the counter each day/month/year.

Dev Comp is using Access 2003, though it could be in production on a comp with Access 2000/02/03 so if using a new function that is required to do this, please point out so I can be able to adjust according when the time comes.
LVL 1
bng0005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jerryb30Commented:
If the form is being sequerntially numbered now, how is that done?  Autonumber?  Query? Code?
Does form contain date of creation info?
jerryb30Commented:
sequerntially = Texan for the next num'er in line
bng0005Author Commented:
the way it's being numbered now is to manually type the number in word. The user would just open the word document, type in the information from the excel doc, and then add whatever number it would be for that day.

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

jerryb30Commented:
I see Word and Excel mentioned.  What is the Access portion?  
bng0005Author Commented:
access is being used for the database I'm making them. Threw all the excel data into tables, added reports that were being done by hand, and now working on the forms they need to create.

That's why I'm trying to figure out a way to have a counter reset each day/month/etc so they can continue using their numbering system without any problems.
will_scarlet7Commented:
   I would suggest adding a field for "Form Date" then in your form's "Before Insert" event trigger a check to see if there have been any entries that day (Can use DLookup) and if so number the form with the next in sequence, if not start again at the begining (P001). I am just putting forth the idea here as I'm on my way to bed. Perhaps someone else could help you put feet to it (if this is the way you want to go). I'll be back to check on it tomorrow If no one else digs in.

God bless!

Sam
bng0005Author Commented:
I might be using the term form differently than usual. In this case, the user would navigate to the record needed and then click on a cmd button. On the OnClick event, there is code that opens up a word template, pulls all the information from the database form and inserts into the bookmarks in the Word document. There is nothing being updated in the database for this event, just pulling info into Word to automate the document's creation. It's on the word document that needs the autonumber.

I won't be back in the office to test anything till Monday since I'm off for the weekend right now, but if any suggestions are posted, I will try them first thing monday and post back the results.



Bryan
will_scarlet7Commented:
Hi Bryan,
    Based on your description above I think this could be a bit simpler than I thought. I would suggest you create a table that store information needed for the sequencing (I think you will need a table in your database to keep track of this). I will propose a table and method based on a system that I have used for similar purposes, but you can adapt it as you desire.

Table:
    Name = "sys_ini"
Fields:
    Field Name    Data Type    Field size
    ------------------------------------------
    Section         Text             10
    Key              Text              10
    Value           Text              30

The data needed can be stored in the following 2 records in this table:
    Section        Key               Value
    ------------------------------------------
    Forms         FrmDate        12-Jul-2005
    Forms         FrmSeq         P001

    For my programs I use a table similar to above to keep track of multiple "Key" values such as paths, public variables, previous selections etc... and for that I wrote a whole set of functions to save and retrieve the data. Assuming that you will not need it for more than just keeping track of the sequencing of your forms I will suggest to keep the methods much simpler...

In your code that "opens up a word template" first check if the date matches the one stored in your "sys_ini" table, and if so then generate the next form number and record it, if not re-set the FrmDate to store the current date and the FrmSeq back at the top of your sequence. Like this:

    If DateValue(DLookup("[Value]", "sys_ini", "Section = 'Forms' AND Key = 'FrmDate'") = Date Then
        frmSequence = "P" & Format(DLookup("[Value]", "sys_ini", "Section = 'Forms' AND Key = 'FrmSeq'")+1, "000")  'Calculates the next form number
        CurrentDB.Execute "UPDATE sys_ini SET Value = '" & frmSequence & "' WHERE Section = 'Forms' AND Key = 'FrmSeq';"
    Else
        frmSequence = "P001"
        CurrentDB.Execute "UPDATE sys_ini SET Value = CStr(Date) WHERE Section = 'Forms' AND Key = 'FrmDate';"
        CurrentDB.Execute "UPDATE sys_ini SET Value = 'P001' WHERE Section = 'Forms' AND Key = 'FrmSeq';"
    End If

This will give you a form number in the memory variable "frmSequence" that you can pass to your Word document, and that is re-set each day.
bng0005Author Commented:
Hmm, that looks like it could work. It will be another day or two until I'm back in the office to try it out, but I'll post back with the results. Thanks again.
GRayLCommented:
There is no way you can do this without some reference to this date in the record.  So you need a table and alongside the formNo you will need to store the date as thisDate.  That said,
formNo=format("P" & iif(date=DMax("thisDate","myTable"),DMax("thisDate","myTable")+1,1,"000")

Now, whenever you ask for a new form number, it will always give it to you in the form Pnnn where nnn in incremental, unless it has become the next day in which case it will be P001.  Is that what you wanted?

will_scarlet7Commented:
GRayL,
In my post above I am suggesting only storing the date and form number of the last created form. Are you saying it would be better to keep a running log?

Sam
GRayLCommented:
Sam:  I was thrown a bit by the language:  "... the first form created would be P001...."  I am not really sure what bng0005 means.  Nor was it clear to me in your answer you were storing the last created number.  I got to read more thoroughly!

bng0005:  you said: "...is a form that gets numbered sequentially by day....", I presume means everytime the form is opened its caption is changed from say P045 to P046?  I guess the number is used somewhere?    As to storing the date and last number, I think we could store them in the tag Property of the form.  In the forms load event put this code:

Private Sub Form_Load()

If Me.Tag is null or CDate(left(Me.Tag,10))<>Date() Then
  Me.Tag = Date() & "001"
Else
  Me.Tag = Date() & Format$(Val(Right(Me.Tag,3))+1,"000")
Endif
Me.Caption = "P" & Right(Me.Tag,3)

End Sub
will_scarlet7Commented:
Hi Ray,
The thing is, bng0005 is creating "Forms" in Word via mail merge (I assume for the purpose of being printed or e-mailed). So he is not actually creating a form in Access.

Sam
will_scarlet7Commented:
bng0005,
    I modified the code sample a bit to factor in Null values, and fix an error:
-----------------------------------------------------------------------------------------------

    If Nz(DLookup("[Key]", "sys_ini", "Section = 'Forms' AND Key = 'FrmDate'"), "X") = "X" Then
        frmSequence = "P001"
        CurrentDb.Execute "INSERT INTO sys_ini ( [Section], [Key], [Value] ) VALUES ('Forms', 'FrmDate', CStr(Date()));"
        CurrentDb.Execute "INSERT INTO sys_ini ( [Section], [Key], [Value] ) VALUES ('Forms', 'FrmSeq', 'P001');"
    Else
        If DateValue(DLookup("[Value]", "sys_ini", "Section = 'Forms' AND Key = 'FrmDate'")) = Date Then
            frmSequence = "P" & Format(Val(Mid(Nz(DLookup("[Value]", "sys_ini", "Section = 'Forms' AND Key = 'FrmSeq'"), "00"), 2)) + 1, "000")
            CurrentDb.Execute "UPDATE sys_ini SET Value = '" & frmSequence & "' WHERE Section = 'Forms' AND Key = 'FrmSeq';"
        Else
            frmSequence = "P001"
            CurrentDb.Execute "UPDATE sys_ini SET Value = CStr(Date) WHERE Section = 'Forms' AND Key = 'FrmDate';"
            CurrentDb.Execute "UPDATE sys_ini SET Value = 'P001' WHERE Section = 'Forms' AND Key = 'FrmSeq';"
        End If
    End If
will_scarlet7Commented:
OOOPS.... Found a few more mistakes in the sample code (I reslised that I set a field name to a reserved word "Value", I'm not changing it below, just the way that it is referenced, but you might want to chane the name to something like "KeyVal" to avoid possible conflicts):

    If Nz(DLookup("[Key]", "sys_ini", "Section = 'Forms' AND Key = 'FrmDate'"), "X") = "X" Then
        frmSequence = "P001"
        CurrentDb.Execute "INSERT INTO sys_ini ( [Section], [Key], [Value] ) VALUES ('Forms', 'FrmDate', CStr(Date()));"
        CurrentDb.Execute "INSERT INTO sys_ini ( [Section], [Key], [Value] ) VALUES ('Forms', 'FrmSeq', 'P001');"
    Else
        If DateValue(DLookup("[Value]", "sys_ini", "Section = 'Forms' AND Key = 'FrmDate'")) = Date Then
            frmSequence = "P" & Format(Val(Mid(Nz(DLookup("[Value]", "sys_ini", "Section = 'Forms' AND Key = 'FrmSeq'"), "00"), 2)) + 1, "000") 'next form number
            CurrentDb.Execute "UPDATE sys_ini SET [Value] = '" & frmSequence & "' WHERE Section = 'Forms' AND Key = 'FrmSeq';"
        Else
            frmSequence = "P001"
            CurrentDb.Execute "UPDATE sys_ini SET [Value] = CStr(Date()) WHERE Section = 'Forms' AND Key = 'FrmDate';"
            CurrentDb.Execute "UPDATE sys_ini SET [Value] = 'P001' WHERE Section = 'Forms' AND Key = 'FrmSeq';"
        End If
    End If

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GRayLCommented:
Well, then I would have a button on the form I would click labelled "Next Number" and use the on click event of the button to generate the next number.  I guess what I'm saying, now that I've thought about it is you do not need an accompanying table to solve this problem.  The tag property of the form can store all the data you need and it can be used and manipulated in the manner I described in on load event code.
will_scarlet7Commented:
Maybe I am the one missunderstanding now... If there is not a Form in Access, how can the form have a Tag property? Are you looking at it from the angle that there would be a Access form to enter any needed data before the Mail-Merge creates the Word document (paper form)?
GRayLCommented:
will:  In http:Q_21485290.html#14401243, bng0005 said: "access is being used for the database I'm making them. Threw all the excel data into tables, added reports that were being done by hand, and now working on the forms they need to create."

I just took it from that there were Access forms. I'm probably wrong.
will_scarlet7Commented:
OK. I guess I was basing my assumptions on a few post later where he said:

>>I might be using the term form differently than usual. In this case, the user would navigate to the record needed and then click on a cmd button. On the OnClick event, there is code that opens up a word template, pulls all the information from the database form and inserts into the bookmarks in the Word document. There is nothing being updated in the database for this event, just pulling info into Word to automate the document's creation. It's on the word document that needs the autonumber.<<

Upon reading this closer though, I think you are correct in that you could store the "Form Number" in the "navigate/select" form and then pass that out to the Word doc...
GRayLCommented:
I hear you.  We'll see what the boss thinks.
bng0005Author Commented:
Sorry for the delay, weekend is almost over, will be able to test the code tomorrow morning and report back with what I find.

And sorry for the misuse of terms, user calls every word document a form so I've gotten into that habit from going back and forth with him so much. Dealing with government forms will do that to you.

To clarify exactly what I mean, the autonumber has no value to the individual record, it's just their randomly made up numbering convention for each word document. And the "form" really is just a word document, rather than an access form in the database. If further explanation is needed, just ask and I can explain in more detail.

From reading the examples, I understand the new table and adding in the code that way, but I am not too sure about the button and tag property. I'll try both ways tomorrow morning when I get into work and post back the results or any questions I have that arise from either solution.
bng0005Author Commented:
Ok, I have the new table added and the code working exactly how I needed it to be. Now the question for you two is this.

Which of the two ways talked about so far would be the better solution? The table works great and performance issue isn't a problem since it's only looking up 1 or 2 fields and the document that needs to be printed isn't something overly used throughout the day, maybe printing off a dozen a week or so.

I'm tempted to just stick with the table way since I have it working already, but I just wanted to see what you guys thought. Thanks again.


Bryan
will_scarlet7Commented:
What say you GRay?
My experience is limited to the way I suggested, so I will lean to your wisdom in this...
GRayLCommented:
Remember the elegance of simplicity. The fewer things involved in a process the better.  Look ahead to when someone else is looking after the app.  Tags are precisely for this kind of problem.  In fact there is a separate tag for just about every control on a form.  If you lookup Help Form Tags, you will see the Tag can carry a help prompt or a value you want to keep track of, (this case).  This URL http://www.helenfeddema.com/access.htm, click on item 12 for a good read on tags.
bng0005Author Commented:
Ok thanks again for the help and information. I'm gonna split the points since I will probably look into using tags for this at a later date and will's solution is working perfectly at this time and they want something up and running to use asap.


Bryan
will_scarlet7Commented:
ThanX bng0005! Glad we could help!
And GRay, ThanX for teaching me something!

God bless!

Sam
GRayLCommented:
Bryan, thanks and glad I could help.

Will:  You're such a smoothie.  Are you back from your GMT+1?
will_scarlet7Commented:
>> Will:  You're such a smoothie.
Actually, I'm self taught, so I try to learn as much as I can and file it away for later use.

>> Are you back from your GMT+1?
No. I do volunteer work as a missionary, so don't go back too regularly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.