Unique identifer for Lead Number

[AdelaideKiwi]

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

 

[CJ_London]

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]

set the "lead number" textbox Locked property to Yes.
add code to the Form's beforeInsert event:

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]

set the "lead number" textbox Locked property to Yes.
add code to the Form's beforeInsert event:

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 "

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

 

[AdelaideKiwi]

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]

if it is now a Text:


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

 

[arnelgp]

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

 

[AdelaideKiwi]

Perfect - thank you arnelgp

 

[arnelgp]

goodluck

 

[Pat Hartman]

Rather than using Like which will prevent the database engine from using an index. store the year as a separate field