Unique identifer for Lead Number (1 Viewer)

AdelaideKiwi

New member
Local time
Tomorrow, 01:53
Joined
Feb 7, 2021
Messages
11
Hello All,

This is my first post so bare with this newbie. This has probably been answered before so perhaps a point in the right direction might be good.

I have a table/form called "Lead Generation" It has a field called "Lead Number". It is not the primary and is not an autonumber. I want it to have an auto generated unique identifier that is YY - nnnn example 21 - 0001 being - this year "21" and then " - " and then a number starting from 0001 up to 9999

Thanks AdelaideKiwi
 

Attachments

  • Lead Number.GIF
    Lead Number.GIF
    52.2 KB · Views: 238

CJ_London

Super Moderator
Staff member
Local time
Today, 15:23
Joined
Feb 19, 2013
Messages
16,553
google 'dmax+1' which sounds like what you want.

Also look at the similar threads section of this thread - at least two would appear to match your requirement

edit: be aware if this is for a multi user application, you need to take extra care for the situation where two or more users are entering leads at the same time - there are workarounds but depends on your requirements
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:23
Joined
May 7, 2009
Messages
19,169
set the "lead number" textbox Locked property to Yes.
add code to the Form's beforeInsert event:
Code:
private sub form_beforeinsert(cancel as integer)
dim strNewLeadNum as string
dim v as variant
strNewLeadNum = nz(dmax("[lead number]", "[lead generation]", "[lead number] like " & left$(year(date()), 2) & "*"),  "")
if len(strNewLeadNum ) < 1 then
    strNewLeadNum = right$(year(date()), 2) & "-0001"
else
    v = split(strNewLeadNum, "-")
    strNewLeadNum = v(0) & "-" & format$(val(v(1))+1, "0000")
end if
me![lead number] = strNewLeadNum
end sub
 

AdelaideKiwi

New member
Local time
Tomorrow, 01:53
Joined
Feb 7, 2021
Messages
11
set the "lead number" textbox Locked property to Yes.
add code to the Form's beforeInsert event:
Code:
private sub form_beforeinsert(cancel as integer)
dim strNewLeadNum as string
dim v as variant
strNewLeadNum = nz(dmax("[lead number]", "[lead generation]", "[lead number] like " & left$(year(date()), 2) & "*"),  "")
if len(strNewLeadNum ) < 1 then
    strNewLeadNum = right$(year(date()), 2) & "-0001"
else
    v = split(strNewLeadNum, "-")
    strNewLeadNum = v(0) & "-" & format$(val(v(1))+1, "0000")
end if
me![lead number] = strNewLeadNum
end sub
Hi Arnelgp,

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strNewLeadNum As String
Dim v As Variant
strNewLeadNum = Nz(DMax("LeadNo", "tblLeadGeneration", "[LeadNo] like " & Left$(Year(Date), 2) & "*"), "")
If Len(strNewLeadNum) < 1 Then
strNewLeadNum = Right$(Year(Date), 2) & "-0001"
Else
v = Split(strNewLeadNum, "-")
strNewLeadNum = v(0) & "-" & Format$(Val(v(1)) + 1, "0000")
End If
Me![lead number] = strNewLeadNum
End Sub



It has a problem with ..... "[LeadNo] like "



1612744318298.png

I have changed the field LeadNo from number to short text in this associated table - is this correct?
 

AdelaideKiwi

New member
Local time
Tomorrow, 01:53
Joined
Feb 7, 2021
Messages
11
google 'dmax+1' which sounds like what you want.

Also look at the similar threads section of this thread - at least two would appear to match your requirement

edit: be aware if this is for a multi user application, you need to take extra care for the situation where two or more users are entering leads at the same time - there are workarounds but depends on your requirements
Thanks CJ - it worked perfectly but I need the prefix of 21 - xxxx
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:23
Joined
May 7, 2009
Messages
19,169
if it is now a Text:


strNewLeadNum = Nz(DMax("LeadNo", "tblLeadGeneration", "[LeadNo] like '" & Left$(Year(Date), 2) & "*'"), "")
If Len(strNewLeadNum) < 1 Then
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:23
Joined
May 7, 2009
Messages
19,169
here is a New code:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strNewLeadNum As String
Dim v As Variant
strNewLeadNum = Nz(DMax("[leadno]", "[tblleadgeneration]", "[leadno] like '" & Format$(Date, "yy") & "*'"), "")
If Len(strNewLeadNum) < 1 Then
strNewLeadNum = Format$(Date, "yy") & "-0001"
Else
v = Split(strNewLeadNum, "-")
strNewLeadNum = v(0) & "-" & Format$(Val(v(1)) + 1, "0000")
End If
Me![lead number] = strNewLeadNum
end sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:23
Joined
May 7, 2009
Messages
19,169

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Feb 19, 2002
Messages
42,970
Rather than using Like which will prevent the database engine from using an index. store the year as a separate field
 
Last edited:

Users who are viewing this thread

Top Bottom