Sequentially Increment a Text Field Based on Select Criteria

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

I need to automatically assign a sequential number based on the last four
positions of a text field on a form based on the maximum value of part of the
field +1 and based on the matching the value of the same field to that of a
record in another table.

In Table Service-Contracts I have a text field titled ARL TRACKING NO.
Sample values are as follows:

ARL-2009-0001
ARL-2009-0002
ARL-2009-0003
ARL-2010-0001
ARL-2010-0002
ARL-2010-0003
ARL-2010-0004

In Table FISCAL-YEAR I have a field called FY and there is one record which
equals “2009â€.

In the sample above, I expect to see ARL-2009-0004 displayed on the form,
but see ARL-2010-0000.

VBA Code is below.

I am relatively new to VBA so any assistance will be greatly appreciated.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]", "[ARL
TRACKING NO]" Like "[FISCAL-YEAR].[FY]")
If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2010-0000"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) & Val(Right(varResult, 3))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub
 
You cannot reference another table and field like that in a DMAX function.
You need to pass in the value.

Dim vYear as V
vYear = DLookup("FY","[FISCAL-YEAR]")

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"[ARL TRACKING NO] Like '*" & vYear & "*'")

Also get the last 4 characters not the last 3
Me.[ARL TRACKING NO]=Left(varResult, 10) & Val(Right(varResult, 4)) + 1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
I am quite new to VBA, but I do not see in your example that it is selecting
records based on the value of a field in another table.

Arvin Meyer said:
I have something close, but you'll need to modify it:

http://www.datastrat.com/Download/AlphaNumeric2K.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


Barry said:
I need to automatically assign a sequential number based on the last four
positions of a text field on a form based on the maximum value of part of
the
field +1 and based on the matching the value of the same field to that of
a
record in another table.

In Table Service-Contracts I have a text field titled ARL TRACKING NO.
Sample values are as follows:

ARL-2009-0001
ARL-2009-0002
ARL-2009-0003
ARL-2010-0001
ARL-2010-0002
ARL-2010-0003
ARL-2010-0004

In Table FISCAL-YEAR I have a field called FY and there is one record
which
equals "2009".

In the sample above, I expect to see ARL-2009-0004 displayed on the form,
but see ARL-2010-0000.

VBA Code is below.

I am relatively new to VBA so any assistance will be greatly appreciated.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]", "[ARL
TRACKING NO]" Like "[FISCAL-YEAR].[FY]")
If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2010-0000"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) & Val(Right(varResult,
3))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub
Click to expand...


.
Click to expand...
 
John,

Thanks so much. I changed Dim vYear as V to Dim vYear as Variant and it
worked like a charm!

John Spencer said:
You cannot reference another table and field like that in a DMAX function.
You need to pass in the value.

Dim vYear as V
vYear = DLookup("FY","[FISCAL-YEAR]")

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"[ARL TRACKING NO] Like '*" & vYear & "*'")

Also get the last 4 characters not the last 3
Me.[ARL TRACKING NO]=Left(varResult, 10) & Val(Right(varResult, 4)) + 1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I need to automatically assign a sequential number based on the last four
positions of a text field on a form based on the maximum value of part of the
field +1 and based on the matching the value of the same field to that of a
record in another table.

In Table Service-Contracts I have a text field titled ARL TRACKING NO.
Sample values are as follows:

ARL-2009-0001
ARL-2009-0002
ARL-2009-0003
ARL-2010-0001
ARL-2010-0002
ARL-2010-0003
ARL-2010-0004

In Table FISCAL-YEAR I have a field called FY and there is one record which
equals “2009â€.

In the sample above, I expect to see ARL-2009-0004 displayed on the form,
but see ARL-2010-0000.

VBA Code is below.

I am relatively new to VBA so any assistance will be greatly appreciated.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]", "[ARL
TRACKING NO]" Like "[FISCAL-YEAR].[FY]")
If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2010-0000"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) & Val(Right(varResult, 3))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub
Click to expand...
.
Click to expand...
 
One more question. Instead of using Like, would it be possible to use =,
looking at positions 5 thru 9 of the ARL Tracking Number?

John Spencer said:
You cannot reference another table and field like that in a DMAX function.
You need to pass in the value.

Dim vYear as V
vYear = DLookup("FY","[FISCAL-YEAR]")

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"[ARL TRACKING NO] Like '*" & vYear & "*'")

Also get the last 4 characters not the last 3
Me.[ARL TRACKING NO]=Left(varResult, 10) & Val(Right(varResult, 4)) + 1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I need to automatically assign a sequential number based on the last four
positions of a text field on a form based on the maximum value of part of the
field +1 and based on the matching the value of the same field to that of a
record in another table.

In Table Service-Contracts I have a text field titled ARL TRACKING NO.
Sample values are as follows:

ARL-2009-0001
ARL-2009-0002
ARL-2009-0003
ARL-2010-0001
ARL-2010-0002
ARL-2010-0003
ARL-2010-0004

In Table FISCAL-YEAR I have a field called FY and there is one record which
equals “2009â€.

In the sample above, I expect to see ARL-2009-0004 displayed on the form,
but see ARL-2010-0000.

VBA Code is below.

I am relatively new to VBA so any assistance will be greatly appreciated.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]", "[ARL
TRACKING NO]" Like "[FISCAL-YEAR].[FY]")
If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2010-0000"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) & Val(Right(varResult, 3))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub
Click to expand...
.
Click to expand...
 
Yes, that is possible.

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"MID([ARL TRACKING NO],5,4) ='" & vYear & "'")

Sorry about the DIM vYear as V. Obviously I meant to type Dim vYear as variant.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
One more question. Instead of using Like, would it be possible to use =,
looking at positions 5 thru 9 of the ARL Tracking Number?

John Spencer said:
You cannot reference another table and field like that in a DMAX function.
You need to pass in the value.

Dim vYear as V
vYear = DLookup("FY","[FISCAL-YEAR]")

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"[ARL TRACKING NO] Like '*" & vYear & "*'")

Also get the last 4 characters not the last 3
Me.[ARL TRACKING NO]=Left(varResult, 10) & Val(Right(varResult, 4)) + 1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I need to automatically assign a sequential number based on the last four
positions of a text field on a form based on the maximum value of part of the
field +1 and based on the matching the value of the same field to that of a
record in another table.

In Table Service-Contracts I have a text field titled ARL TRACKING NO.
Sample values are as follows:

ARL-2009-0001
ARL-2009-0002
ARL-2009-0003
ARL-2010-0001
ARL-2010-0002
ARL-2010-0003
ARL-2010-0004

In Table FISCAL-YEAR I have a field called FY and there is one record which
equals “2009â€.

In the sample above, I expect to see ARL-2009-0004 displayed on the form,
but see ARL-2010-0000.

VBA Code is below.

I am relatively new to VBA so any assistance will be greatly appreciated.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]", "[ARL
TRACKING NO]" Like "[FISCAL-YEAR].[FY]")
If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2010-0000"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) & Val(Right(varResult, 3))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub
Click to expand...
.
Click to expand...
Click to expand...
 
Thanks so much. That worked but there is still one problem.

If my last Record is ARL-2012-0002 it returns ARL-2012-03 instead of
ARL-2012-0003.

If my last Record is ARL-2012-0212 it returns ARL-2012-0213, which is correct.

Any suggestions?

Code is below.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
Dim vYear As Variant
vYear = DLookup("FY", "[FISCAL-YEAR]")

DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "SERVICE-CONTRACTS", "MID([ARL
TRACKING NO],5,4) ='" & vYear & "'")

If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2011-0001"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) & Val(Right(varResult, 4))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub


John Spencer said:
Yes, that is possible.

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"MID([ARL TRACKING NO],5,4) ='" & vYear & "'")

Sorry about the DIM vYear as V. Obviously I meant to type Dim vYear as variant.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
One more question. Instead of using Like, would it be possible to use =,
looking at positions 5 thru 9 of the ARL Tracking Number?

John Spencer said:
You cannot reference another table and field like that in a DMAX function.
You need to pass in the value.

Dim vYear as V
vYear = DLookup("FY","[FISCAL-YEAR]")

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"[ARL TRACKING NO] Like '*" & vYear & "*'")

Also get the last 4 characters not the last 3
Me.[ARL TRACKING NO]=Left(varResult, 10) & Val(Right(varResult, 4)) + 1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Barry wrote:
I need to automatically assign a sequential number based on the last four
positions of a text field on a form based on the maximum value of part of the
field +1 and based on the matching the value of the same field to that of a
record in another table.

In Table Service-Contracts I have a text field titled ARL TRACKING NO.
Sample values are as follows:

ARL-2009-0001
ARL-2009-0002
ARL-2009-0003
ARL-2010-0001
ARL-2010-0002
ARL-2010-0003
ARL-2010-0004

In Table FISCAL-YEAR I have a field called FY and there is one record which
equals “2009â€.

In the sample above, I expect to see ARL-2009-0004 displayed on the form,
but see ARL-2010-0000.

VBA Code is below.

I am relatively new to VBA so any assistance will be greatly appreciated.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]", "[ARL
TRACKING NO]" Like "[FISCAL-YEAR].[FY]")
If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2010-0000"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) & Val(Right(varResult, 3))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub

.
Click to expand...
Click to expand...
.
Click to expand...
 
Try

Me.[ARL TRACKING NO] = Left(varResult, 10) & Format(Val(Right(varResult, 4))
+ 1, "0000")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Barry said:
Thanks so much. That worked but there is still one problem.

If my last Record is ARL-2012-0002 it returns ARL-2012-03 instead of
ARL-2012-0003.

If my last Record is ARL-2012-0212 it returns ARL-2012-0213, which is
correct.

Any suggestions?

Code is below.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
Dim vYear As Variant
vYear = DLookup("FY", "[FISCAL-YEAR]")

DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "SERVICE-CONTRACTS", "MID([ARL
TRACKING NO],5,4) ='" & vYear & "'")

If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2011-0001"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) & Val(Right(varResult,
4))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub


John Spencer said:
Yes, that is possible.

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"MID([ARL TRACKING NO],5,4) ='" & vYear & "'")

Sorry about the DIM vYear as V. Obviously I meant to type Dim vYear as
variant.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
One more question. Instead of using Like, would it be possible to use
=,
looking at positions 5 thru 9 of the ARL Tracking Number?

:

You cannot reference another table and field like that in a DMAX
function.
You need to pass in the value.

Dim vYear as V
vYear = DLookup("FY","[FISCAL-YEAR]")

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"[ARL TRACKING NO] Like '*" & vYear & "*'")

Also get the last 4 characters not the last 3
Me.[ARL TRACKING NO]=Left(varResult, 10) & Val(Right(varResult, 4)) +
1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Barry wrote:
I need to automatically assign a sequential number based on the last
four
positions of a text field on a form based on the maximum value of
part of the
field +1 and based on the matching the value of the same field to
that of a
record in another table.

In Table Service-Contracts I have a text field titled ARL TRACKING
NO.
Sample values are as follows:

ARL-2009-0001
ARL-2009-0002
ARL-2009-0003
ARL-2010-0001
ARL-2010-0002
ARL-2010-0003
ARL-2010-0004

In Table FISCAL-YEAR I have a field called FY and there is one record
which
equals "2009".

In the sample above, I expect to see ARL-2009-0004 displayed on the
form,
but see ARL-2010-0000.

VBA Code is below.

I am relatively new to VBA so any assistance will be greatly
appreciated.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]", "[ARL
TRACKING NO]" Like "[FISCAL-YEAR].[FY]")
If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2010-0000"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) &
Val(Right(varResult, 3))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub

.
Click to expand...
.
Click to expand...
Click to expand...
 
Thanks for everyones help. I am still having one problem.

If the Max Value is ARL-2014-0002, it returns ARL-2014-0003, which is correct.

If the Max Value is ARL-2014-0023, it returns ARL-2014-0024, which is correct.

If the Max Value is ARL-2014-0234, it returns ARL-2014-0235, which is correct.

However, if the Max Value is ARL-2014-1234, it returns ARL-2014-11235, which
is not correct as it should return ARL-2014-1235.

Any suggestions on how to correct this.

VBA Code is below.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0


Dim vYear As Variant
vYear = DLookup("FY", "[FISCAL-YEAR]")

DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "SERVICE-CONTRACTS", "MID([ARL
TRACKING NO],5,4) ='" & vYear & "'")

If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-" & vYear & "-0001"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) &
Format(Val(Right(varResult, 4)) + 1, "000")


End If
Else
MsgBox "No new record created!"
End If
End Sub

Douglas J. Steele said:
Try

Me.[ARL TRACKING NO] = Left(varResult, 10) & Format(Val(Right(varResult, 4))
+ 1, "0000")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Barry said:
Thanks so much. That worked but there is still one problem.

If my last Record is ARL-2012-0002 it returns ARL-2012-03 instead of
ARL-2012-0003.

If my last Record is ARL-2012-0212 it returns ARL-2012-0213, which is
correct.

Any suggestions?

Code is below.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
Dim vYear As Variant
vYear = DLookup("FY", "[FISCAL-YEAR]")

DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "SERVICE-CONTRACTS", "MID([ARL
TRACKING NO],5,4) ='" & vYear & "'")

If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2011-0001"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) & Val(Right(varResult,
4))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub


John Spencer said:
Yes, that is possible.

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"MID([ARL TRACKING NO],5,4) ='" & vYear & "'")

Sorry about the DIM vYear as V. Obviously I meant to type Dim vYear as
variant.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Barry wrote:
One more question. Instead of using Like, would it be possible to use
=,
looking at positions 5 thru 9 of the ARL Tracking Number?

:

You cannot reference another table and field like that in a DMAX
function.
You need to pass in the value.

Dim vYear as V
vYear = DLookup("FY","[FISCAL-YEAR]")

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"[ARL TRACKING NO] Like '*" & vYear & "*'")

Also get the last 4 characters not the last 3
Me.[ARL TRACKING NO]=Left(varResult, 10) & Val(Right(varResult, 4)) +
1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Barry wrote:
I need to automatically assign a sequential number based on the last
four
positions of a text field on a form based on the maximum value of
part of the
field +1 and based on the matching the value of the same field to
that of a
record in another table.

In Table Service-Contracts I have a text field titled ARL TRACKING
NO.
Sample values are as follows:

ARL-2009-0001
ARL-2009-0002
ARL-2009-0003
ARL-2010-0001
ARL-2010-0002
ARL-2010-0003
ARL-2010-0004

In Table FISCAL-YEAR I have a field called FY and there is one record
which
equals "2009".

In the sample above, I expect to see ARL-2009-0004 displayed on the
form,
but see ARL-2010-0000.

VBA Code is below.

I am relatively new to VBA so any assistance will be greatly
appreciated.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]", "[ARL
TRACKING NO]" Like "[FISCAL-YEAR].[FY]")
If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2010-0000"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) &
Val(Right(varResult, 3))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub

.

.
Click to expand...
Click to expand...


.
Click to expand...
 
Try the following. You seem to want the first 9 characters (not the first 10)
and you should be calculating value of the last 4 characters and then
formatting that with a "0000"

Me.[ARL TRACKING NO] = Left(varResult, 9) & _
Format(Val(Right(varResult, 4)) + 1, "0000")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks for everyones help. I am still having one problem.

If the Max Value is ARL-2014-0002, it returns ARL-2014-0003, which is correct.

If the Max Value is ARL-2014-0023, it returns ARL-2014-0024, which is correct.

If the Max Value is ARL-2014-0234, it returns ARL-2014-0235, which is correct.

However, if the Max Value is ARL-2014-1234, it returns ARL-2014-11235, which
is not correct as it should return ARL-2014-1235.

Any suggestions on how to correct this.

VBA Code is below.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0


Dim vYear As Variant
vYear = DLookup("FY", "[FISCAL-YEAR]")

DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "SERVICE-CONTRACTS", "MID([ARL
TRACKING NO],5,4) ='" & vYear & "'")

If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-" & vYear & "-0001"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) &
Format(Val(Right(varResult, 4)) + 1, "000")


End If
Else
MsgBox "No new record created!"
End If
End Sub

Douglas J. Steele said:
Try

Me.[ARL TRACKING NO] = Left(varResult, 10) & Format(Val(Right(varResult, 4))
+ 1, "0000")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Barry said:
Thanks so much. That worked but there is still one problem.

If my last Record is ARL-2012-0002 it returns ARL-2012-03 instead of
ARL-2012-0003.

If my last Record is ARL-2012-0212 it returns ARL-2012-0213, which is
correct.

Any suggestions?

Code is below.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
Dim vYear As Variant
vYear = DLookup("FY", "[FISCAL-YEAR]")

DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "SERVICE-CONTRACTS", "MID([ARL
TRACKING NO],5,4) ='" & vYear & "'")

If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2011-0001"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) & Val(Right(varResult,
4))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub


:

Yes, that is possible.

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"MID([ARL TRACKING NO],5,4) ='" & vYear & "'")

Sorry about the DIM vYear as V. Obviously I meant to type Dim vYear as
variant.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Barry wrote:
One more question. Instead of using Like, would it be possible to use
=,
looking at positions 5 thru 9 of the ARL Tracking Number?

:

You cannot reference another table and field like that in a DMAX
function.
You need to pass in the value.

Dim vYear as V
vYear = DLookup("FY","[FISCAL-YEAR]")

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"[ARL TRACKING NO] Like '*" & vYear & "*'")

Also get the last 4 characters not the last 3
Me.[ARL TRACKING NO]=Left(varResult, 10) & Val(Right(varResult, 4)) +
1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Barry wrote:
I need to automatically assign a sequential number based on the last
four
positions of a text field on a form based on the maximum value of
part of the
field +1 and based on the matching the value of the same field to
that of a
record in another table.

In Table Service-Contracts I have a text field titled ARL TRACKING
NO.
Sample values are as follows:

ARL-2009-0001
ARL-2009-0002
ARL-2009-0003
ARL-2010-0001
ARL-2010-0002
ARL-2010-0003
ARL-2010-0004

In Table FISCAL-YEAR I have a field called FY and there is one record
which
equals "2009".

In the sample above, I expect to see ARL-2009-0004 displayed on the
form,
but see ARL-2010-0000.

VBA Code is below.

I am relatively new to VBA so any assistance will be greatly
appreciated.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]", "[ARL
TRACKING NO]" Like "[FISCAL-YEAR].[FY]")
If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2010-0000"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) &
Val(Right(varResult, 3))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub

.

.
Click to expand...

.
Click to expand...
Click to expand...
 
Thanks to everyone for there help. It is now working great!

John Spencer said:
Try the following. You seem to want the first 9 characters (not the first 10)
and you should be calculating value of the last 4 characters and then
formatting that with a "0000"

Me.[ARL TRACKING NO] = Left(varResult, 9) & _
Format(Val(Right(varResult, 4)) + 1, "0000")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks for everyones help. I am still having one problem.

If the Max Value is ARL-2014-0002, it returns ARL-2014-0003, which is correct.

If the Max Value is ARL-2014-0023, it returns ARL-2014-0024, which is correct.

If the Max Value is ARL-2014-0234, it returns ARL-2014-0235, which is correct.

However, if the Max Value is ARL-2014-1234, it returns ARL-2014-11235, which
is not correct as it should return ARL-2014-1235.

Any suggestions on how to correct this.

VBA Code is below.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0


Dim vYear As Variant
vYear = DLookup("FY", "[FISCAL-YEAR]")

DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "SERVICE-CONTRACTS", "MID([ARL
TRACKING NO],5,4) ='" & vYear & "'")

If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-" & vYear & "-0001"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) &
Format(Val(Right(varResult, 4)) + 1, "000")


End If
Else
MsgBox "No new record created!"
End If
End Sub

Douglas J. Steele said:
Try

Me.[ARL TRACKING NO] = Left(varResult, 10) & Format(Val(Right(varResult, 4))
+ 1, "0000")


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Thanks so much. That worked but there is still one problem.

If my last Record is ARL-2012-0002 it returns ARL-2012-03 instead of
ARL-2012-0003.

If my last Record is ARL-2012-0212 it returns ARL-2012-0213, which is
correct.

Any suggestions?

Code is below.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
Dim vYear As Variant
vYear = DLookup("FY", "[FISCAL-YEAR]")

DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "SERVICE-CONTRACTS", "MID([ARL
TRACKING NO],5,4) ='" & vYear & "'")

If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2011-0001"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) & Val(Right(varResult,
4))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub


:

Yes, that is possible.

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"MID([ARL TRACKING NO],5,4) ='" & vYear & "'")

Sorry about the DIM vYear as V. Obviously I meant to type Dim vYear as
variant.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Barry wrote:
One more question. Instead of using Like, would it be possible to use
=,
looking at positions 5 thru 9 of the ARL Tracking Number?

:

You cannot reference another table and field like that in a DMAX
function.
You need to pass in the value.

Dim vYear as V
vYear = DLookup("FY","[FISCAL-YEAR]")

varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]",
"[ARL TRACKING NO] Like '*" & vYear & "*'")

Also get the last 4 characters not the last 3
Me.[ARL TRACKING NO]=Left(varResult, 10) & Val(Right(varResult, 4)) +
1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Barry wrote:
I need to automatically assign a sequential number based on the last
four
positions of a text field on a form based on the maximum value of
part of the
field +1 and based on the matching the value of the same field to
that of a
record in another table.

In Table Service-Contracts I have a text field titled ARL TRACKING
NO.
Sample values are as follows:

ARL-2009-0001
ARL-2009-0002
ARL-2009-0003
ARL-2010-0001
ARL-2010-0002
ARL-2010-0003
ARL-2010-0004

In Table FISCAL-YEAR I have a field called FY and there is one record
which
equals "2009".

In the sample above, I expect to see ARL-2009-0004 displayed on the
form,
but see ARL-2010-0000.

VBA Code is below.

I am relatively new to VBA so any assistance will be greatly
appreciated.

Private Sub Form_Load()
Me!Combo125.SetFocus
Me!Combo125.SelLength = 0
DoCmd.GoToRecord , , acNewRec
If Me.NewRecord Then
Dim varResult As Variant
varResult = DMax("[ARL TRACKING NO]", "[SERVICE-CONTRACTS]", "[ARL
TRACKING NO]" Like "[FISCAL-YEAR].[FY]")
If IsNull(varResult) Then 'just in case the table is empty
Me.[ARL TRACKING NO] = "ARL-2010-0000"
Else
Me.[ARL TRACKING NO] = Left(varResult, 10) &
Val(Right(varResult, 3))
+ 1
End If
Else
MsgBox "No new record created!"
End If
End Sub

.

.


.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top