Starting a Quote number from 6000

[Ozzboss]

I'm not sure this is the best approach but this is what I have...
JobID = AutoNumber
QuoteNumber = Calculated = [JobID]+6000

This works but fails if a record is not completed or if a record is deleted as the QuoteNumber would then not match quotes that he sends out.
So How do I start a new database with a number field starting from 6000 and not tie it to the JobID?

 

[June7]

Try:

Nz(DMax("[somefield]", "tablename"), 0) + 1

Generating custom unique identifier is a common topic.

 

[Pat Hartman]

If you want the number to start from 6000, use 6000 rather than 0 in the Nz()

 

[Ozzboss]

Try:

Nz(DMax("[somefield]", "tablename"), 0) + 1

Generating custom unique identifier is a common topic.

Sorry, Where do I use this?

 

[June7]

Could use it in the DefaultValue property of textbox.

Be aware, if this is split multiple simultaneous user database, multiple users could generate the same number and cause error.

As Pat said, use 6000 instead of 0. My goof.

 

[Pat Hartman]

I put the code as the last line of the BeforeUPdate event.

If Me.SomeField & "" = "" Then
    Nz(DMax("[somefield]", "tablename"), 6000) + 1
End If

Putting it at the end of the BeforeUpdate event minimizes the chances of a conflict but doesn't eliminate them. If the user gets a duplicate error, instruct him to try again. If you have an extremely busy environment, you might want to use a different technique and put the generation code into a loop.

 

[Ozzboss]

I put the code as the last line of the BeforeUPdate event.

If Me.SomeField & "" = "" Then
    Nz(DMax("[somefield]", "tablename"), 6000) + 1
End If

Putting it at the end of the BeforeUpdate event minimizes the chances of a conflict but doesn't eliminate them. If the user gets a duplicate error, instruct him to try again. If you have an extremely busy environment, you might want to use a different technique and put the generation code into a loop.

I'm getting a compile error for some reason saying the +1 should be a =1

 

[June7]

Need to set some field with the calculated result.

Me.somefield = Nz(DMax("[somefield]", "tablename"), 6000) + 1

Replace somefield with your actual field name and tablename with your actual table name.

 

[Ozzboss]

OK I used the code as above on the BeforeInsert and it works great!!
QuoteNumber = Nz(DMax("QuoteNumber", "JobDetails"), 6000) + 1

Thanks to everyone for the help

I have one more request if it is possible to do lol

With an empty database, the form now creates the first number as 6000 (if Null) +1 = 6001
Is there a way to make it use 6000 as the first number and NOT add the extra 1 until each consecutive record?

 

[Ozzboss]

I thought I might be clever.. but failed lol. Is there something in this that I am missing or will this just not work as I'm thinking?
This works to add the 6000 in as the first record but it wont add the next record in as 6001

If IsNull(Me.QuoteNumber) Then
    QuoteNumber = Nz(DMax("QuoteNumber", "JobDetails"), 6000)
Else
    QuoteNumber = DMax("QuoteNumber", "JobDetails") + 1
End If
End Sub

 

[The_Doc_Man]

For an empty DB, use this

If DCount("QuoteNumber", "JobDetails"  ) = 0 then 
    QuoteNumber = 6000
Else
    QuoteNumber = DMax( "QuoteNumber", "JobDetails" ) + 1
End If

Note that if you are RETROFITTING the quote numbers on existing records, this approach is not correct because in that case, you can still count the records with zero for quote numbers.

 

[Pat Hartman]

OK I used the code as above on the BeforeInsert and it works great!!

I recommended the form's BeforeUpdate event for a reason. My choice was not arbitrary and I told you the reason. You might want to reread my response. Sorry about the typo in the example.

The BeforeInsert event runs only for new records (which is the right Idea), however, it is the first event to run as soon as the user starts typing in the form so there could be several minutes lag between when the sequence number is generated and when it is committed. It is a risk I choose to not take.

 

[Ozzboss]

For an empty DB, use this

If DCount("QuoteNumber", "JobDetails"  ) = 0 then
    QuoteNumber = 6000
Else
    QuoteNumber = DMax( "QuoteNumber", "JobDetails" ) + 1
End If

Note that if you are RETROFITTING the quote numbers on existing records, this approach is not correct because in that case, you can still count the records with zero for quote numbers.

Thank you mate that works perfectly, except I added the Nz back in to convert to Null to a 0

If Nz(DCount("QuoteNumber", "JobDetails")) = 0 Then
    QuoteNumber = 6000
Else
    QuoteNumber = DMax("QuoteNumber", "JobDetails") + 1
End If

 

[The_Doc_Man]

DCount should NEVER give you a null. It is counting records, for which the return value won't be null but CAN be zero. Your only potential flaw might be if you have records in place with nulls for QuoteNumber but have no records that have a non-null QuoteNumber. If you have even ONE non-null quote number, the DMax should also work straight up.

 

[Ozzboss]

DCount should NEVER give you a null. It is counting records, for which the return value won't be null but CAN be zero. Your only potential flaw might be if you have records in place with nulls for QuoteNumber but have no records that have a non-null QuoteNumber. If you have even ONE non-null quote number, the DMax should also work straight up.

Good to know. I was under the impression that if left blank it would be treated as null rather than 0. I'll remove it to tidy it up

 

[The_Doc_Man]

You can protect yourself against nulls that would lead to confusion by simply putting a "required" constraint on the field. Of course, if you can reliably define it for yourself, then that constraint isn't SO important. But the key word is "reliably."

 

[June7]

DCount is the only domain aggregate function that still returns a value if no records match. I think same is true for SQL aggregate functions.

 

[The_Doc_Man]

While I don't dispute you at all, June7, I will point out that the implementation of SQL aggregate functions is allowed to not be uniform in what they do if they are to return an error. That is, DMax(field) of an empty recordset or DAvg(field) of are allowed to be different from vendor to vendor in terms of what error they choose to return.

However, when they DO have data to return, the ANSI 92 standard directs what they will return.

 

[June7]

AFAIK, DMax and DAvg are Access domain aggregate functions and SQL would not use the D prefix. True, I was thinking only in the context of Access library.