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

tmyers

Well-known member
Local time
Today, 03:02
Joined
Sep 8, 2020
Messages
1,090
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!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:02
Joined
Feb 19, 2002
Messages
42,981
the point I made when I told you how to do this was that it was a technique used by users when the application didn't give them searching capabilities on various data fields. If you provide multiple search options, no one cares what the actual quote number is. The number is irrelevant. They're looking for the quote they made last week or the quote they made for Sam's Autobody. So don't get hung up on the actual quote number. Pay attention to what they want to search by. That will be much more useful. Finding all the quotes for a particular client is relevant. Finding all the quotes that Pat made this month is relevant. Finding all the versions of a particular quote is relevant and it is the latter where the sequence number comes in. It allows the quote to have the same number in case that is what they are looking at and the Rev shows various iterations.
 

Yianni

Registered User.
Local time
Today, 03:02
Joined
May 15, 2017
Messages
40
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
 

tmyers

Well-known member
Local time
Today, 03:02
Joined
Sep 8, 2020
Messages
1,090
Ok. Pat, I finally had/have a moment to really sit down and take a look at your example.
the point I made when I told you how to do this was that it was a technique used by users when the application didn't give them searching capabilities on various data fields. If you provide multiple search options, no one cares what the actual quote number is. The number is irrelevant. They're looking for the quote they made last week or the quote they made for Sam's Autobody. So don't get hung up on the actual quote number. Pay attention to what they want to search by. That will be much more useful. Finding all the quotes for a particular client is relevant. Finding all the quotes that Pat made this month is relevant. Finding all the versions of a particular quote is relevant and it is the latter where the sequence number comes in. It allows the quote to have the same number in case that is what they are looking at and the Rev shows various iterations.
I did take this one to heart. I am putting less emphasis on the quote number, and am working on switching more of searching to the job name, as that is much more relevant to the user. We all know the job name, but who cares about the quote number.

I am fully looking at the example and seeing if I can break it down and repurpose it. If I (more like when I) run into a snag or have a question, I will post back here.
 

tmyers

Well-known member
Local time
Today, 03:02
Joined
Sep 8, 2020
Messages
1,090
Does this seem like the correct method?

I made a new table linked to my "master" table. I gave the new table a PK to relate it to the main table (JobID). I put in the fields [QuoteNum], [YearNum], [RevisionNum] and [FullNumber] (I dont think this was needed because of what follows).

On a form where a job is created, during a save button click, I use DMax to get my next QuoteNum (I populated it with starting data), then I set [RevisionNumber] to 1 via:
Code:
If IsNull(Me.RevisionNum.OldValue) Then  'only generate for new record
    Me.RevisionNum = Nz(DMax("RevisionNum", "tblQuoteNumGeneration", "QuoteNum"), 0) + 1
End If

I then combine the quote number derived from DMax with a year
Code:
MyYear = Right(Year(Date), 2)
and the [RevisionNum] (which in this particular case, should always be 1) via
Code:
Me.QuoteNum = Nz(DMax("[QuoteNum]", "tblJobDetails")) + 1 & "-" & MyYear & "-" & Me.RevisionNum

The end result is something like 12345-20-1. Here is where I am conflicted. Do I then update this new tables [FullNumber] with that value? Or just directly update tblJobDetails (the main table) and its field [QuoteNum] which may now be redundant?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:02
Joined
Feb 19, 2002
Messages
42,981
I don't know why you created a separate table. What are you trying to do? If you are using it to try to prevent sequence number conflicts, you must save the new quote ID immediately in the new table. You still have to deal with potential duplicates. I wouldn't put a great deal of effort into it since they would be rare.
 

tmyers

Well-known member
Local time
Today, 03:02
Joined
Sep 8, 2020
Messages
1,090
I don't know why you created a separate table. What are you trying to do?
I mimicked your example (probably more closely then I needed to).
I created the table to store all the unique bits of the number, rather than try to cram it into my already rather large "master" table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:02
Joined
Feb 19, 2002
Messages
42,981
I created the table to store all the unique bits of the number, rather than try to cram it into my already rather large "master" table.
Wrong technique. Do not create the separate table. There is no need to duplicate the data. If your master table has too many columns, it is almost certainly not correctly normalized. Do you want to post it so we can look at it.
 

Users who are viewing this thread

Top Bottom