Create a Custom Project Number

[Jazflux]

Hi:
I want to create a Custom Proposal Number that works like this:

AA: Does not change
2007: This Year
ABCD: Client Initials
001: First Proposal This Year for Client Initials

Looks like this:
First Contract of the Year 2007 for Client ABCD
AA2007ABCD001

Then, each time I create a new contract, the system will perform a query and
create the next sequential number.

AA2007ABCD002

I need help calculating this number for Forms and Reports, and constructing
the proper query to create the next, unique contract number. Thanks!!!

 

[Allen Browne]

This example assumes the customer initials part is always 4 characters long,
and the proposal number is always 3 digits.

The code goes into the BeforeUpate event procedure of the *form*. That's the
last possible moment before the record gets saved, so it reduces the chance
of a duplicate if several users are adding records together.

It executes only for a new record, and always for a new record (which covers
the case where the first attempt failed for some reason, and then the user
changed the client.)

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strStub As String
Dim strWhere As String
Dim varResult As Variant

If Me.NewRecord Then
If Len(Nz(Me.ClientInitials, vbNullString)) <> 4 Then
Cancel = True
MsgBox "Client initials must be 4 characters.
Else
strStub = "AA" & Year(Date) & Me.ClientInitials
strWhere = "[Custom Proposal Number] Like """ & strStub & "*"""
varResult = DMax("[Custom Proposal Number]", "Table1", strWhere)
If IsNull(varResult) Then
varResult = 1
Else
varResult = Val(Right(varResult,3)) + 1
If varResult >= 1000 Then
Cancel = True
MsgBox "Proposal number too high."
End If
End If
If Not Cancel Then
Me.[Custom Proposal Number] = strStub & Format(varResult,
"000")
End If
End If
End If
End Sub

 

[Jeanette Cunningham]

Hi Jazflux,

An answer to a very similar question was given by MVP Arvin Meyer in the
formscoding subgroup of this newsletter.
It is the answer to the question called Custom ID field, posted 15/12/07.

If that answer doesn't fully answer your question, post back.

Jeanette Cunningham

 

[Jazflux]

Thank you very much. I will try to implement this!

 

[Jazflux]

Hi Jeanette:
I am just now trying to implement this procedure, and I am confused.
I am a beginner with Code and Modules.

Where do I put the Code that Allen mentioned?
How do I reflect it on the form?
Can I save the Proposal Number to a Table?
Do I need a special table to contain the JobNumber as was mentioned with
Dennis's posts?

Thank you very much

 

[Jeanette Cunningham]

You need a table to hold a single value for Proposal Number.
In Allen's code this is called Table1.
Allen's code goes in the Before Update event for the form.
Yes you do need to save the value of Proposal Number just used back to the
table.
You could do this in the After Update event for the form.
Code something like this:

Dim strSQL as String
Dim db as DAO.Database

Set db = dbengine(0)(0)

strSQL = "UPDATE Table1 " _
& "SET [Custom Proposal Number] = """ & me.[Custom Proposal Number] &
""""
'Debug.Print strSQL
db.Execute strSQL, dbFailOnError

Set db = Nothing

Jeanette Cunningham

 

[user]

My cat died on January 9th 2008 and feel me better