Auto Numbering a specific field within a database

[lmott]

Good evening I have a database where I a command button that when pressed I wan to see the value from the previous record/field and then add 1 to it. I have never built a code where I had to look backwards for part of the equations to give me the value. Below is what I was playing with just to see if I could get it to work with out help, but I can not get it to look backwards for part of the value.

Should , acLast, Me.DRAWING_NO >= 105999, , acNewRec, Me.DRAWING_NO = 106000
Should , acLast, Me.DRAWING_NO = Me.DRAWING_NO <= 106000, , acNewRec, Me.DRAWING_NO = Me.DRAWING_NO + 1

I would be happy using auto number if I knew how to reset the first number it puts out instead of it being 1 I need it to start out at 10600.

Any help would be thankful.

Lloyd

[NeoPa]

Your code makes no sense to me. I can't even tell if it's SQL or VBA :-(

However, if you need to determine the highest value in a table field then you could use DMax(). As the whole context you're working in is so unclear there's not much more I can say at this stage. If you start there and reply with a clearer question if you get stuck then that should work.

[lmott]

What I have is a database that I want to have perform an autonumber function for each new entry. But I do not want to start out at 1. How can I change or get the autonumber feature to do this.

Above I was just playing around with different codes (VBA) trying to get my field to look at the previous entry of a specific field and then add 1 to it. I hope this helps

[NeoPa]

You cannot get AutoNumber to do this for you. AutoNumber only guarantees to provide unique values. It never stipulates even that they are provided in any order, let alone an order with a specific starting position.

As I mentioned in the earlier post you will need to use DMax() to determine the highest value contained within the table then add 1 to that.

[zmbd]

Using an insert query that duplicates the table format of the one with the autonumber field such that the related field is one less than the desired start number. Insert the record. Delete the record.

Tbl_WAutoNumber Example
[WAutoNumberExam ple_PK] : Autonumber, primary key
[WAutoNumberExam ple_someinfo] : text(50)

Tbl_NoAutoNumbe rExample
[NoAutoNumberExa mple_pk] : Numeric(Long), primary key
[NoAutoNumberExa mple_someinfo] : text(50)

Say you want to start:
Tbl_WAutoNumber Example.[WAutoNumberExam ple_PK]=2000

Then:
Tbl_NoAutoNumbe rExample.[NoAutoNumberExa mple_pk]=1999

Make a select query against Tbl_NoAutoNumbe rExample
Change to Insert query against Tbl_WAutoNumber Example

Delete the record with 1999 in pk
Next entry will start with 2000

There is also the method that uses the ADOX library; however, I've had issues with this when my other client pc run if this library isn't available: http://allenbrowne.com/func-ADOX.html#SetSeed

[TheSmileyCoder]

The normal(most common) reason for implementing a custom id is to avoid gaps. This is one thing that the autonumber cannot do. IF someone starts a record, and then cancels it, the number has been spent. In some cases people think of this as a problem, and implement a custom numbering mimicking the autonumber behavior.


If your requirement is simply to have the autonumber starting with 10600 then you can use this to reset the autonumber counter:

currentdb.Execute "ALTER TABLE tbl_Test ALTER COLUMN PK_Auto COUNTER(10600,1)"

Be aware that if you have any records allready existing in the table then you will get a key violation when you try to reuse that key. So whatever number you use, must be higher then the currently largest number in the table.

[NeoPa]

Amendment:

NeoPa:
You cannot get AutoNumber to do this for you.

While it may be possible to set the value returned by AutoNumber, it is not recommended to rely on it returning sequential values in the way you want it to. Certainly it generally does, in practice, but it isn't guaranteed to and was never, ever, designed to provide that functionality. It was designed to provide a guaranteed unique value in the table. If you continue down this path you are likely to find that gaps occur and you will be frustrated trying to avoid those. Again, it was never set up to provide that functionality. What it was set up to do it does well. Using it for other purposes can work, but frequently frustrates.

[TheSmileyCoder]

Just to further illustrate the point NeoPa is making, in a multiuser DB you could have user start a record in the morning "drawing" number 10687 but not saving that record for hours, in the mean time other users might "draw" and use numbers 10688 through 10712. It is important to notice that the number in this case is "drawn" or taken when the record is first STARTED and not when it is saved.

In some cases this matters little, in others its a problem. You need to find out whether its a problem or not.

Benefits of autonumber:
Its always unique.
Easy to implement.

[lmott]

I see what you all are talking about and yes using autonumber would be a problem for me.

I am going to try DMax() + 1, as I have not used this function before.

I hate to admitt but I am actualy enjoying this part of my database as I am getting to learn something new.

Lloyd

[NeoPa]

LMott:
I hate to admit it, but I am actualy enjoying this part of my database as I am getting to learn something new.

That's what we like to hear Lloyd. It's what makes helping here so much fun.

PS. Let us know if you struggle (and exactly what you're struggling on of course) and we can jump in and assist.

[lmott]

Well I have tried to figure out how to use and where to put the DMax function but I have come up short. Where and how do I use this function. thank you

Lloyd

[NeoPa]

It seems I wasn't clear enough when I suggested letting us know exactly what you're struggling on. Without that information it will be very hard for us to help you. Your existing code would be a very basic start to imparting it.

In very fundamental terms you would use the function at the point at which you need to add a new record. The function takes three parameters (The last of which is optional).

Expr: The value from the domain (table or query) to be summed.
Domain: The table or query to process to retrieve the information.
Criteria: If specified, define which records from the Domain are to be included in the processing.

[lmott]

No you were clear I was in a big hurry as I have been swamped with several rush projects as of late.

In my database I have a form linked to a table, here is some of the fields in the form that require input from the user;
Date User Input
Project Name User Input
Project Number User Input
Created By User Input
Procedure Description User Input


Once all of the fields have been filled out then there is a Tab named "Procedure Number" that is linked to a text box who's Control is in my table. What I want to happen is once the user clikcs on the "Procedure Tab" I want the text box to display the next numerical number, but I do not want to start out at 001 or 1. I need to start out with 300 or somehing like that.

I have never created a database where it needed to perform a function that had to get its numeric value from the previous record. So I am at a loss here, but enjoying the learning experience happening with this part of the database. If I need to provide additional details what I can do is create a word document and go through the entir form and each function in it if needed.

Lloyd

[NeoPa]

OK Lloyd. Let's start with some basics.

Details need to be posted within the post rather than in attached documents. That said, I appreciate the willingness to engage indicated by the statement ;-)

In post #12 I gave you the parameter details of the function DMax() that I first introduced in post #4. I've been looking for something in any of your responses that indicates that you're responding to this information. Nothing as yet unfortunately. To progress we will need two-way communication. If we get no feedback then we can spout till the cows come home and still not say anything that works for you, as we don't know what you can and cannot understand.

Ultimately, we'll need details (mainly names) of the table, the field, the control where this pseudo-sequential data is to be held. We also need a full and exact description of the intended data, including the base number, or the number that the sequence starts with when no existing records yet exist.

In human terms we :

1. Find in the table the maximum value of this field that matches what we're working on.
2. Extract the sequential portion.
3. Add 1 (or any other number) to that.
4. Reformulate the field data using the new result.
5. Assign it to the new record.

If we're to look at reproducing this in code then we'll clearly need all that information available to us.

[apca]

Auto Numbering a specific field

If your database table has following fields
Date
Project Name
Project Number
Created By
Procedure Description than you must have a field for "Procedure Number" with default zero (0).
At 1st instance of filling form Procedure Number will be 0.
Now you want that as 300 and subsequent number as 301, 302 and so on.
Let your Tab run a update query on field "Procedure Number"
Update to IIf([Table Name].[Procedure Number] = 0,300,[Table Name].[Procedure Number]+1)