Make a number field behave like an autonumber

[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

 

[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.

 

[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/

 

[tmyers]

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/

I didn't think about that. Thank you for the insight!

 

[Yianni]

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.

Hi,
Take a look at the following. DMAX is the way to go

Private Function AssignNextCheckNum() As Long

Dim LargestChkNum As Long

LargestChkNum = Nz(DMax("ChkNum", "CheckRegT", "AccountID=" & AccountCombo), 0)
LargestChkNum = LargestChkNum + 1
AssignNextCheckNum = LargestChkNum

End Function


Private Sub ChkNum_DblClick(Cancel As Integer)

If Not IsNull(ChkNum) Then
MsgBox "Check number already assigned"
Exit Sub
End If

ChkNum = AssignNextCheckNum
ShowHidePrintCheckBtn

End Sub

HTH
John