Make a number field behave like an autonumber

[tmyers]

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]

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:

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

MyYear = Right(Year(Date), 2)

and the [RevisionNum] (which in this particular case, should always be 1) via

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?

 

[tmyers]

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.