Make a number field behave like an autonumber (1 Viewer)

[tmyers]

I have a weird one that some of my users just requested. This goes back to a previous post I had done where I asked about splitting a string (my quote number) and incrementing.
They want our quote number to be fully automated. I cant use the autonumber field since I already am using it as a unique ID in the table. How can I make it "automated"?
So essentially I would have our quote number be 12345 then when a new quote is created, make it 12346. I assume this can be done using code, but am curious if there are other avenues. I am essentially trying to "force" a number field to behave like an autonumber.

 

[Isaac]

Other than code? I don't think so?

 

[tmyers]

Other than code? I don't think so?

I figured. How do you think I could do it? My main issue is have the code figure out what the previous number was then increment it.

 

[Isaac]

Dmax + 1

 

[Gasman]

I figured. How do you think I could do it? My main issue is have the code figure out what the previous number was then increment it.

You would use DMax() and increment by 1.
However if user cancels out of 12346, and in the meantime another user is working on 12347, then you will get gaps.?

So some careful thought needs to go in to how it will actually work.

This has been asked many times here, so perhaps search the site.?

 

[tmyers]

You would use DMax() and increment by 1.
However if user cancels out of 12346, and in the meantime another user is working on 12347, then you will get gaps.?

So some careful thought needs to go in to how it will actually work.

This has been asked many times here, so perhaps search the site.?

I have never used DMax before. Ill have to research it a little bit.

The easiest method would have been an autonumber, but it is way too late for that change to be made.
With how few users I have, the likelihood of that happening is pretty slim. We also usually just have one person creating the jobs and assigning them.

 

[Gasman]

If and when you do a search, you will see that it is consistently said there is no guarantee that the autonumber will be sequential, just that it will be unique, so not ideal.?

 

[tmyers]

If and when you do a search, you will see that it is consistently said there is no guarantee that the autonumber will be sequential, just that it will be unique, so not ideal.?

Yeah. I am seeing that. I also don't think I could do this since they also eventually want to add in revisions so 12345-1 and such.
I am going to have to brain storm this one a little bit.

 

[Gasman]

Yeah. I am seeing that. I also don't think I could do this since they also eventually want to add in revisions so 12345-1 and such.
I am going to have to brain storm this one a little bit.

I am getting Deja Vu about that statement now, even with my bad memory?

Might have been this one?
https://www.access-programmers.co.uk/forums/threads/vba-to-split-string-then-increment.314522/page-2

 

[theDBguy]

Yeah. I am seeing that. I also don't think I could do this since they also eventually want to add in revisions so 12345-1 and such.
I am going to have to brain storm this one a little bit.

There are several demo files available showing this feature. You might be able to use one of them.

 

[plog]

I cant use the autonumber field since I already am using it as a unique ID in the table

Your "since" doesn't make sense. Why can't you use the autonumber as the quote number? An example would be good.

 

[The_Doc_Man]

The greater issue is that you want the number to actually mean something besides just being a unique record ID. WHENEVER your field is to have a specific meaning, it is NOT appropriate to actually use an autonumbered field.

The next most common way to do this is, when doing this via form, you fill in the record number at the last possible moment, in the Form_BeforeUpdate event, to make the field take the value DMax(Fieldname, Table or query name, "[Fieldname}+1"). But THEN, to verify that you don't accidentally "step on" another user in the same table at the same time, put a unique index on the field. It is possible for the index to exist without actually being a key. If you have collisions, you have to be prepared to step back and retry the process with a new number, which means error trapping. There is also the factor that if someone tries to store a number and then is allowed to "undo" or delete the record AFTER another user sees the tentative number, you COULD end up with gaps anyway.

Adding to the fracas, that comment about "revisions" means that you will have the greater complication that either you will need to decompose a text field into a sequence number and a revision number (highly NOT recommended) or have a separate revision number field that when displaying the composite sequence number, you use a query to format the two fields together for display purposes. If you do THAT, you run into the issue that you CAN'T have the sequence number with a unique index AND have a revision number too. In that latter case, you need the unique index to be a compound index of the sequence and revision number taken together.

Basically, what I am telling you is that the label on the can you just opened says "Worms."

 

[Minty]

There is another method, in which you create another table with an autonumber field.
When you want a new number simply add a record to this table and extract the ID, this is 100% reliable in not generating a duplicate ID. You can add various other control fields but this avoids the issue of DMax potentially being unreliable in a busy multi-user environment.

It won't solve your revision issue, but IMHO that should be split into another field.

 

[Gasman]

Hers is a thread where a member wanted/nneded to have a suffix to the order/quote number, but wanted an alpha extension, whereas yours is numeric.

https://www.access-programmers.co.uk/forums/threads/autonumber-advice.314336/page-3#post-1729005

 

[Pat Hartman]

Here's another opinion.
1. The primary key of the table should ALWAYS be an autonumber
2. Relationships are ALWAYS between the autonumber and the FK which is a long integer.
3. Sometimes users want a custom identifier. This is a real throwback to the time when your only way to access something was with the unique identifier. In today's world and in Access, there are many techniques we use to allow the users to find records without knowing their key.
4. Your request is for a two-tiered custom identifier. To do this is possible and the best way is to use TWO fields. One for the UI and the second for the Rev number. When you give the user a way to pick the UI from a combo, you will need a second, related combo to show the rev numbers.
5. The code in your app's BeforeUpdate event will need to ensure that an existing UI is chosen, in which case you will generate a rev or for a new item, you will generate the UI with a Rev of 0. So, all records with rev 0 are original versions.
6. The table will use an autonumber as the PK but to ensure uniqueness will require a composite two column unique index. You can't do this on the table in design view. Unless you are creating a PK, you have no way to group columns into a single index so you MUST use the indexes dialog. If you can't figure out how to do this, post back and I'll help. I've posted pictures of how to do this several times so you might be able to find one.

I've attached a sample showing two types of generated sequence numbers. One intended to be the main ID and the second is as a sequence number for child records. I think you don't want the revs to be children of the original record but siblings so you would use the sequence number technique twice in the same record using different arguments in the dmax() to get the next sequence number.

The example is just an example and meant to be easy to understand. In the real world, this is a little more complicated. In a multi-user environment, you need to generate the new IDs as close to the time the record saves as possible so the generation should be the LAST statement in the form's BeforeUpdate event. You will either be generating a new UI with a rev of 0 or using an existing UI and generating a rev but not both. If you can't figure out the code to distinguish the situation, we can help. The final problem is that there is a miniscule chance that you will conflict with another person doing the same thing. There is an instant in time between when you run the dMax() and when the save happens to commit your record. Someone could have generated the same number, in which case, you will get a duplicate error. This is as I said, a miniscule problem so don't loose sleep over it but do put in an error trap for a duplicate. You can mitigate the problem for the UI by using something similar to the table technique suggested by Mindy. This mitigates the problem because Access handles the conflict for you. I would use a DAO .AddNew statement so you can pick up the generated autonumber in the code and no have to do another query to find the generated number.

PS, if you agree in concept to what I am suggesting, I will update the sample database with a working example. Otherwise, I won't waste my time.

 

[Isaac]

I think the path you're going down is fine, as long as you follow a simple rule: (which I gather you're already doing) - Keep the true key values autonumbers. Give the users a different column, styled just however they like to look at.

 

[tmyers]

I think the path you're going down is fine, as long as you follow a simple rule: (which I gather you're already doing) - Keep the true key values autonumbers. Give the users a different column, styled just however they like to look at.

Yup! I have tried to follow the rule of not applying any meaning to an autonumber, other than knowing it will always be a unique number.

Pat, I will take a look at you example, but what you described sounds perfect. I may not get to it today however.

 

[tmyers]

Pat, I am looking at your example now and that seems nearly perfect for what I am after.
In your example I keep getting the pop up that it cant be saved, but the way it is outputting the number is pretty much what I am after.

I like that it includes the year. That would be very helpful for us. I think for our needs I would try to structure it to be something like:
20-650001-1
The year code first so it is super quick to identify when it was done, the main number then the revision number.

 

[Pat Hartman]

Is there a question? If you're getting an error in the example, please give me more information and picture of where it is happening.

 

[CJ_London]

The year code first so it is super quick to identify when it was done, the main number then the revision number.

On the contrary - if it is indexed and you want to find what was done in a particular year, then fine. But if you are looking for a quote number and don't know the year, then you cannot use indexing so all searches will be slow for any significant amount of records (say 10k+). Reason is to find a specific quote you need to use 'like '* quotenum *'. The use of the initial * precluded the use of indexing.

Further, a string index (which this would be) is much larger than the equivalent numeric index - so fewer records can be examined 'at the same time', slowing it further.

Listen to the advice given, build a good app by following good principles, or a bad one because that's the way you want it. Your choice

You might find this link helpful regarding indexing

https://www.access-programmers.co.uk/forums/threads/why-indexing-is-important-for-good-performance.291268/