How can I create a field (not autonumber) which automatically increments

[Found33]

How can I create a field (not autonumber) which automatically increments but allows me to reset its value to 1 at the start of each year?



Early last year I created a database of rescue animals in which I have, for each animal record, an autonumber animal ID (ID) and a field for year (of) which is auto completed. I also have an animal reference number (Ref) which is the one I use for our website etc. What I want is for me to be able to manually reset that reference number to 1 for the first record each year and for it to then auto increment throughout the year. I can then easily display our reference numbers as nnnn/yy.

I have been reading through a number of forums and Microsoft support pages but my knowledge of Access is still so basic that when I read suggested answers, I have no idea as to which screen and field to enter the given information. Please can anyone help? Be gentle with me please as this is my first post. Thank you.

[Stingaway]

You should consider keeping the autonumber as a unique identifier, even if you don't use it as the animal record identifier that you use in practice. While it is nice to be able to change that in the real world, in database speak you are setting yourself up for a world of hurt if you don't have a unique identifier on a record that never changes.

My advice would be to set up two fields - the first the autonumber that uniquely identifies the record to the database, and the second a field of your choice that can be modified to increment +1 from the previous record.

From your description I would also recommend you set up a transactional table that indicates the current year - that way any changes that you make to a record will not affect historical records. Setting the reference number to display as you would like is actually something very easily done on the form or in a query - but I would caution to avoid duplicating records on the same animal by changing or adding new records for each year.

[Found33]

Hi Stingaway, thank you for the speedy reply. As you suggest, I accept that I need to keep to keep the autonumber identifier as the unique identifier for each record. I also hope to use my Ref field / control as the incremented field.
I am rather concerned though that you feel that I need a table for "year" rather than the control in the form that I have at the moment which automatically enters the current year in a record. I thought that having entered it once it then left it alone. As I'm the only one updating this database I thought that that was sufficient. Oh dear.
I certainly take your point about replicating animal records to give them the current year's numbers and the danger that that would present.
Thank you for all of those thoughts.
All I need now (I hope) are some simple instructions as to how to make my Ref field increment automatically. Can anyone help please?

[Stingaway]

The transactional table isn't a standalone for year, it just contains the transactional data that occurs; part of that data would include the year info.

Couple of ways you could increment the field in your records, I would suggest an update query to update the records periodically, in which the field in question is updated with the new info - for example if your field is called fieldname and you want to update it to match the auto id or some other number, you could update it with [fieldname] + X (for numeric where X is a number) or [fieldname] & "TEXT" (for a string value).

[June7]

Here is one approach: http://forums.aspfree.com/microsoft-...ta-403208.html

[Found33]

Hi Stingaway, what I was hoping for was for the Ref. No. to increment automatically on entry of the record just as the autonumber animal ID field does at the moment.
The only difference that I want is to be able to reset the Ref. No. to 1 at the start of each year.
The year field seems to be working fine already.

Hi June7, thanks for the link. I've had a look at it but I don't understand what to do with all that code. Is there not a simple field property or data type or combination of those that I can set to get my Ref No to increment so that it just adds 1 to the last Ref No allocated?

[Stingaway]

If your database is not relational and/or normalized, and I STRESS IF your database is not relational and/or normalized (meaning no tables in the database are referencing the auto id or the data assigned to that auto id of the animals in the table in question...)

Then (and I cannot say "CAUTION, DANGER WILL ROBINSON" enough on this) it is possible to reset your animal id field. Just make sure you make a BACKUP COPY OF YOUR DATABASE , preferably before you make said change to reset.

To reset your autonumber field, simply change the autonumber type to a number in the table design, then reassign the numbers as needed. Once done, you can change it back to an autonumber and all should work fine.

Again, KIDS DO NOT TRY THIS AT HOME. If you change your autonumber, then change the numbers - any and all related records in other tables that were originally tied to that particular ID will now be ORPHANED. Meaning you could find yourself in a position where Spot had data at one time, but now he doesn't and nobody knows why..

[June7]

No, the Autonumber datatype is the only auto-incrementing field. Resetting an autonumber is very dangerous and I would not recommend it.

My suggested code auto-increments a unique identifier and reinitializes the sequence each year. The ID is composed of Year and sequence number, like YYYYA-####.

The example code would certainly require some effort on your part to adapt to your situation. The code that actually creates the ID is a function called by other procedures (as show in code sample). What is it you don't understand?

[Stingaway]

Yeah, I guess I didn't put enough red in the post. :-)

Instead of resetting autonumbers you could just copy the database and start over each year with a fresh copy of all data. It would be safer. Totally crazy, but safer.

[HMEpartsmanager]

How can I create a field (not autonumber) which automatically increments but allows me to reset its value to 1 at the start of each year?

Table: tblCounter
Field Name: nxtCountName; Text; 16 Characters - Counter Name
Filed Name: nxtCountInteger; Number; Long Integer - Next Incremental Number

Form: PMS Purchase Orders - Main Form
In the BeforeUpdate event on the form, I have the following;

Private Sub Form_BeforeUpdate(Cancel As Integer)
'
If Me.NewRecord Then
Dim strOrderName As String
Dim intTestCount As Long
'
strOrderName = "orderCount"
intTestCount = 0
'
intTestCount = Nz(DLookup("[nxtCountInteger]", "tblCounter", "[nxtCountName] = '" &
strOrderName & "'"))
intTestCount = intTestCount + 1
[ordId] = intTestCount
CurrentDb.Execute "UPDATE tblCounter SET nxtCountInteger = " & intTestCount & " WHERE
[nxtCountName] = '" & strOrderName & "'"
'
End If
End Sub

So if I should save this purchase order I just wrote; it will update the incremental counter that I have created. Since this form deals with purchase orders, the counter I am calling orderCount. I set the name of the counter I want to get, and then I set it to zero so no errors pop up if it doesn't exist yet (new form start). Then it will add 1 to the count it
gets. Then my field I want updated in my Purchase Order Form (Field ordId), is updated with this incremented number. Then update the counter to increment higher. And back out.

What this does is gets a new increment number for you, updates your form to this new number, increment the counter plus one because you are using one, and that's it. The reason I have a table for counters, is that I have many counters in my database. One to issue new PO #'s, new line item numbers, new stock items, new vendor items, etc.... So a table holding all the counters was appropriate. But then that above code could be used in any form that needs to get an incremental number.

There are probably lots of ways to get an incremental number, and maybe some that are neater and/or faster; but the way I have mine, works. I too went to the web a few years ago to create this database and came up with help from others to get that above working how I want.

Hopefully this will get you started finding a solution you like.

BTW, I don't like the idea of resetting the count each year. If you created counters for each year, say 'RescueYr2012' and another counter called 'RescueYr2013', that would be two counters you could increment. So in the form, you could call the 2012 counter up until next year. Then just edit the form's subfunction to use the 2013 counter. Since it will be the
first record using 2013 counter, it will automatically start at #1 just like your 2012 will when you get it in your database.

It's a start, and maybe some ideas to help you along your way.


Tim

[Found33]

Hi Stingaway, I love your reply. That is definitely my level. The only problem is that it IS a relational database and has dependencies. I did try your suggestion but it wouldn't let me reinstate the autonumber so I deleted the database and reverted to a copy of my backup.

June7 what I don't understand is where I need to put all of that code. In setting up the database I've simply created a few very basic tables, defined fields (and their properties) and relationships. I have created forms for data entry and extra tables for drop down boxes on those forms and cut and paste a concatenation piece of code to create a drop down names list on the form but I don't really understand that one. Other than that, I've defined loads of simple queries. That's it. I haven't used any code for anything and have no idea where it would need to be put. It really is just a very searchable record of each animal and who's got it.

Hi Tim, your answer had me rushing to my copy of Access 2007 Inside Out and it looks as if you are talking macros or VBA here which I fear is beyond me at the moment. My reason for wanting the counter which resets each year is simply because that is the reference number that we use on our website, Facebook etc and if it's auto generated I won't forget to complete it or create duplicate numbers in any year (as I generally do my updating last thing at night!). Our chief made it clear to me when I set up the database that she didn't want long reference numbers, such as the autonumber will quickly become. Until this database it had all been done on paper, complete with duplicate animals, missed numbers etc.

I was really hoping that there would be a simple answer rather than one that needs programming skills which will take me too long to learn to solve the problem for the start of this year. Given that Microsoft included a simple autonumber property and a simple incrementing year property, I hoped that they might have included a similarly simple annual autonumber function but am I right in thinking now that they haven't?

[HMEpartsmanager]

Hi Tim, your answer had me rushing to my copy of Access 2007 Inside Out and it looks as if you are talking macros or VBA here which I fear is beyond me at the moment.

You need to get over your 'scared' mentality; it really isn't that bad.

Really though, if you learn simple little things like what I posted, you will be able to use the same logical flow in other parts of your database as you grow.

No one is going to force you one way or another, but you will need to learn some bit of function creation to do the 'extras' that are not built in the way you want.

An example of what I posted is; 2 lines to declare I a working with a string and an long number, Name the counter string and set it to 0 in case it doesn't exist yet, get the current counter number and add 1 to it, make my field equal the number I just retrieved, and then just update the counter to next number for the next time we need it. Follow the logic here, and it really is not that bad, or scarey.

If you go into your form, and see the Before Update Event in the property, and open it up, you will see that they open up the routine for you and all you have to do is put in what you want to do when the event runs.

I think, like others, that resetting a counter to zero, is not the way to go. But with my suggestion above, you could just add a different counter and still accomplish what you want. You would only need a way to tell what year you are working with. Maybe we don't understand completely on why you need to reset to zero each year. How do you find record #234 in year 2008 when all the records for the past 3 years are in the same database?

Keep at it, you can do it!

Tim

[June7]

Right, the only auto-incrementing is the Autonumber datatype field. My code example requires the sequence generator function to be in a general code module. Then code elswhere in the project can call the function.

Check this article for a jump start in programming http://www.databaselessons.com/unbound-forms-1.php

[Found33]

Hi Tim, thanks ever so for your patience, I'll give it a try. I have gone all through your 2 postings to try to understand what it is doing but it's a bit of an uphill struggle still. I'll let you know how I get on.

I have to say that if there was a simpler way, without having to define a routine, I would prefer that but if all I have to do is set up a small new table and copy this little routine into a field property sheet, hopefully I can manage that and it will do the trick.

I actually have 2 separate main tables (with input forms): one for Cats and one for Dogs (don't ask!) so it is similar to what you are doing in that more than one counter is required. I'll give it a go in the form of each in a dummy copy and see how it all works together. Will get back to you.
Thanks ever so once again

[Found33]

Hi June7, sorry I hadn't seen that you had posted when I answered HMEpartsmanager.
I'll give his table idea a go as I'm familiar with tables and see how I get on but thanks for your ideas. I'll explore those a bit more once I've got over this little hurdle. I do like the idea of it automatically recognising when it's a new year and dealing with it but, as they say, one step at a time.
Thanks again