Sequentially Increment a Text Field Based on Select Criteria

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

0
Utf
5/11/2010 12:56:02 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

11 Replies
1451 Views

Similar Articles

[PageSpeed] 40

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" <Barry@discussions.microsoft.com> wrote in message 
news:638C6E09-B2D8-433C-B532-A0C59CB9DC79@microsoft.com...
>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
> 


0
Arvin
5/11/2010 1:49:10 PM
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
> 
0
John
5/11/2010 2:00:03 PM
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 [MVP]" wrote:

> 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" <Barry@discussions.microsoft.com> wrote in message 
> news:638C6E09-B2D8-433C-B532-A0C59CB9DC79@microsoft.com...
> >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
> > 
> 
> 
> .
> 
0
Utf
5/11/2010 2:32:00 PM
John,

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

"John Spencer" wrote:

> 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
> > 
> .
> 
0
Utf
5/11/2010 2:45:01 PM
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" wrote:

> 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
> > 
> .
> 
0
Utf
5/11/2010 3:23:02 PM
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?
> 
> "John Spencer" wrote:
> 
>> 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
>>>
>> .
>>
0
John
5/11/2010 4:08:42 PM
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" wrote:

> 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?
> > 
> > "John Spencer" wrote:
> > 
> >> 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
> >>>
> >> .
> >>
> .
> 
0
Utf
5/11/2010 4:50:04 PM
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" <Barry@discussions.microsoft.com> wrote in message 
news:7255A1E1-5C73-427A-8DD5-C1AD8822543D@microsoft.com...
> 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" wrote:
>
>> 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?
>> >
>> > "John Spencer" wrote:
>> >
>> >> 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
>> >>>
>> >> .
>> >>
>> .
>> 


0
Douglas
5/11/2010 5:04:00 PM
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" wrote:

> 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" <Barry@discussions.microsoft.com> wrote in message 
> news:7255A1E1-5C73-427A-8DD5-C1AD8822543D@microsoft.com...
> > 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" wrote:
> >
> >> 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?
> >> >
> >> > "John Spencer" wrote:
> >> >
> >> >> 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
> >> >>>
> >> >> .
> >> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
5/11/2010 6:27:01 PM
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

Barry wrote:
> 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" wrote:
> 
>> 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" <Barry@discussions.microsoft.com> wrote in message 
>> news:7255A1E1-5C73-427A-8DD5-C1AD8822543D@microsoft.com...
>>> 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" wrote:
>>>
>>>> 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?
>>>>>
>>>>> "John Spencer" wrote:
>>>>>
>>>>>> 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
>>>>>>>
>>>>>> .
>>>>>>
>>>> .
>>>>
>>
>> .
>>
0
John
5/11/2010 6:41:51 PM
Thanks to everyone for there help.  It is now working great!

"John Spencer" wrote:

> 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
> 
> Barry wrote:
> > 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" wrote:
> > 
> >> 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" <Barry@discussions.microsoft.com> wrote in message 
> >> news:7255A1E1-5C73-427A-8DD5-C1AD8822543D@microsoft.com...
> >>> 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" wrote:
> >>>
> >>>> 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?
> >>>>>
> >>>>> "John Spencer" wrote:
> >>>>>
> >>>>>> 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
> >>>>>>>
> >>>>>> .
> >>>>>>
> >>>> .
> >>>>
> >>
> >> .
> >>
> .
> 
0
Utf
5/11/2010 7:26:01 PM
Reply:

Similar Artilces:

Carriage Return in General Text Box?
Have a cell defined as GENERAL with wrapped text. How can I put a carriage return in the middle of my wrapped text and start a new paragraph, all in one cell. Use Alt+Enter -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Al Franz" <albert@nospam.netmation.com> wrote in message news:eJmtR4kAFHA.2608@TK2MSFTNGP10.phx.gbl... > Have a cell defined as GENERAL with wrapped text. How can I put a > carriage return in the middle of my wrapped text and start a new > paragraph, all in one cell. > > ...

Task exporting and missing fields
I am currently trying to get the following fields to export from the Task items into an Excel spreadsheet or even a .csv for that matter. Subject, % Complete, Created Date, Due Date, Date Completed, Owner, Requested by. I can get 4 of the 7 fields to export, but am unable to export the others. I have defined them in my views so they are visible. When I try to define custom mappings, I am unable to see 'Owner, Requested by, Created Date' fields in order to map them. I have tried following the KB#236903 with no success and I am unable to find the field names on the list pro...

paste text into a used cell
I need to move text from one row to a single cell that has been formatted to wrap text and repeat the process for other rows. Paste is not working. Please advise. When you copy and paste, you paste the format as well as the contents If you want to copy text to a cell that is formatted differently instead of paste, slelct paste special, values -- ElsiePO ----------------------------------------------------------------------- ElsiePOA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=390 View this thread: http://www.excelforum.com/showthread.php?threadid=26962 ...

Dynamics 9 upgrade and field service error in utilities
I am following the upgrade instruction to the tee. I installed version 9 on the server to a new location, launhed utilities, then cancelled, in order to install SP #2. I now launch utilities and in the product validation, I get an error in 1 of our 15 databases: Unable to get extended version info for product 949 (field service). We don't use this, nor did I install this. Why is it trying to update a product we do not use, nor did I select during the 9.0 install? ...

Text boxes and lines, rectangles etc
Mine question is a bit like Gregs. When I click on Text boxes, lines, circles, rectangles, etc, only the eight dots (handles) appear on the page. No outlines. What must I configure? Actually I think we have the same problem. Greg "Baudinot" wrote: > Mine question is a bit like Gregs. When I click on Text boxes, lines, > circles, rectangles, etc, only the eight dots (handles) appear on the page. > No outlines. What must I configure? It's driving me mad, Greg. Whats the answer? "Greg Bowers" wrote: > Actually I think we have the same problem. > ...

Create new text file for export
I have minimal knowledge of VBA. I have been surching google all day yesterday to solve this problem and found no solutions that work. I am trying to create a new FDF file to export data to a PDF form and launch the file on a button click. I am having trouble with creating the new file. The code I am using (and is not working) is the following: Private Sub FDF_Click() Dim stPDFdata As String Dim chkName As String 'I want to check to see if the fdf file exists and if not make a new one. I know this works for directories but it does not check for the file. Need h...

CTRL-A Select All
In Word 2007, the keystroke combination CTRL-A causes no action at all. To select all, I must use the menu bar. CTRL-A works in all of my other Office 2007 applications. All help will be appreciated. Please see the reply to your previous duplicate question. It would appear that the keyboard shortcut has become unassigned and needs to be reassigned. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Marvin Buzz" <MarvinBuzz@discussions.microsoft.com> wrote in message news:F17B91EF-7750-4997-A42A-FB21...

Default value for a field in the create form
Hi all, It's possible to define a default value for a field, in the create form ? Thanks, Hugo You can default a picklist value with out of the box customization but that's it. >-----Original Message----- >Hi all, > >It's possible to define a default value for a field, in the create form ? > >Thanks, >Hugo > > >. > ...

Report Writer Conditional Field Question
I should know the answer to this but my head is working on a different problem at the moment. In Report Writer, on a SOP Blank Invoice, I want to conditionally show the SOP_Line_Work_PrimaryShipToAddressCode ONLY when it is not equal to 'Primary' or 'Ship To'. In essence, I need to show the SOP Line Level Address when it's an outlier from their two standard address codes. I built a conditional field in RW that checks for Primary and returns blank ("") or the address. Works great. But now I need to add an OR condition and I'm stuck. I can't seem to be able...

VBA selecting an input range of cells
Hi I have a small request for help with code. I need a simple macro to have popups request the first and last cells in a range , and then to select the range indicated in the popups. Can someone help? Grateful for any assistance Something like: Sub PickaRanage() Dim s1 As String, s2 As String, s3 As String s1 = Application.InputBox(prompt:="Enter address of first cell", Type:=2) s2 = Application.InputBox(prompt:="Enter address of last cell", Type:=2) Range(s1 & ":" & s2).Select End Sub -- Gary''s Student - gsnu201001 ...

Password Protecting Fields
I want signature blocks on my form only accessible to the person who signed in. I have user level security so the person must sign in to have access to the form but beyond that I only want that person to be able to fill his/her name in, in their spot only to show agreement - electronic signature. I have this on one form but cant duplicate it on the second. This is what was used on the first - as best I can tell. Private Sub Signature_BeforeUpdate(Cancel As Integer) Dim Response As String Response = InputBox("Enter Password", "Password Required") If Resp...

Select values from list
Hi all, I got a very stupid problem, but like any stupid problem is bothering me... Some time ago I created a Worbook, and in a few columns of a worksheet I created a kind of combobox, integrated in the sheet, to help the user to select values from a list positioned elewhere in the sheet. To put it simple, when the user selects a cell, appears a down arrow on the right side, and clicking it it's shown a list to choose from. The values of the list are in a cell range on the right side of the sheet. Well, now I need to add new columns, but I can't find out how to re-create these co...

MS Access Copy record to another table based on a form input 10-08-07
Hello. I have 6 Tables, all with identical fields. I want to copy a record from one table into another table, which table depends on what table is selected in a form. So if the table names are One, Two, Three, Four, Five, Six, and the record that I want to copy and insert into another table has been selected by a query in a form. I want the form to direct where to insert the table. So if the form has table Two selected (from the drop down list) then it should insert the record into table Two. If the form has table Four selected then it should insert it into table Four. The table the record is...

Selecting Item when Outlook hasn't got Focus
Outlook 2003 Following situation: Another window than Outlook is focused. An item in Outlook is selected. Now I click into the Outlook window to select another item. But this item will not be selected as expected. What happens is that the item that was selected before will be opened. => strange behaviour, isn't it? Does anyone know why? Is there any bug fix for this? Thanks, Peter I'm guessing that your first click merely gives Outlook focus but does not actually select that second item. You probably have to click three times - once to give Outlook focus, the second time to se...

Export (transfer Text) to a text file.
I have a macro set up to transfer a table to a text file using a format spec. I have set up a table and a query to fill the table with the data that I want to export to a text file. All fields in the table are text fields. The data in one field came from a currency field from a table and was converted as follows: Insert into MyExportTable (chkamount) Select Format(nbrCheckAmount, "0.00") From MySourceTable When I export the table MyExportTable, the text field chkamount data gets truncated as if it were a real numeric field. For example 3.14 and 3.10 and 3.00 are stored l...

email problem
We have email problems with several of our clients and customers. To these customers, every time when we email out (using Outlook) with attachments, such as .doc and .pdf and .jpg files. These email was received with one large garble inline text message, but no attachment, by these customers. However, if we send out same email by web based email, such as yahoo or Gmail, the attachments are received OK by the same customers. One major customer is always have this same problem with our email with attachment. They use Gmail by Google. This customer is using web based Gmail browser to receive o...

find and replace data in a yes/no field
How do you find and replace a Yes/No field? I want to find all no entries and globally replace them with a yes entry. Any assistance is deeply appreciated. Write a query UPDATE MyTable SET MYyesno = Yes WHERE MYyesno = No; The where clause is unnecessary if every row has a value, since you'll be making them all True anyway. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Frank" <fhsmith7@bellsouth.net> wrote in message news:BvoPi.155$Q6.102@bignews7.bellsouth.net... > > How do you find and replace ...

Reverse engineering huge code base
Hi I have a huge code base in VC++, which I need to reverse engineer and come up wit a low-level design for. I understand that there might be some tools available to com with the class diagrams like Rational Rose or Visio. But can I come up with the sequence diagrams as well? What is the best possible way to reverse engineer suc huge code chunks TIA Sucharit Genitor sells such a product. I'm not taken by it (it imposes bizarre restrictions on how comments are placed; for example, it misinterprets my change log as a module description) but what I saw of its ability to reverse-enginee...

how do i do a "drop down selection menu"?
Hi Gurus, Does anyone know how to do a drop down selection menu in excel? i want to do a drop down selction so i can select the a date eg select jan, feb, mar, april etc etc cheers :) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ First you need to make your list (one entry per cell). Click the (cell) location where you want the drop down list to be, click data, validation and on the settings tab, in the allow box, choose "list", place the cursor in ...

Text Not Showing in Cell
I'm using excel to produce reports with almost all text. Some cells have a lot of words. Text Wrap is on. Row Height is set to AutoFit. Most cells show up just fine. Some have hidden words that run on under the next cell below. I have even encountered some cells that cut off right through the middle of the text. I can, of course, manually change the height of each row, but this report is over 200 lines long. Same problem happens when printed. Please help. Excel is not the greatest text editor. Have you considered doing the report in Word, using the Tables function? You co...

Why are some messages missing it's text?
I used a previous version of outlook (2002) and had no problem receiving messages; now I'm using the trial version 2003 and questions from ebay are not showing the text. It shows I have a message but in preview or the actually message nothing is there. No filters have been set by me so I have no clue what to do. ...

Big prodject! How to insert row or rows beneath certain text
Is there a code that when a certain text is in collumn (A3) ALL CORE i insert one row beneath it, Also if there is one or more text next t each other, Like Collumn (A9 and A10) HUNTER-#2 and HUNTER-#2 an there're the exactly the same then insert two rows beneath the las Hunter-#2. But the thing is I only want it to happen to specifics text and can it have exceptions like at A5 and A6 the text reads COPE & DRA #2 and under it says COPE & DRAG #3 can the code it insert two row beneath COPE & Drag #3 but if COPE & DRAGE #3 does not appear the insert one row beneath COPE &...

How do copy text from a picture/image & make it available for edi.
I am trying to copy text from an image. And I want that text to be copied to Excel/word not as a picture or image but as a text hi, this cannot be done with excel. the text on the image is part of the image and excel only see the total image not individual parts of the image so it cannot seperate. sorry. >-----Original Message----- >I am trying to copy text from an image. And I want that text to be copied to >Excel/word not as a picture or image but as a text >. > Thanks, can it be done in Word or notepad..... "anonymous@discussions.microsoft.com" wrote: &g...

Text Box angled
Hi: I am working with a powerpoint document I have inserted a text box I want to angle the text box i.e. slant it; katty corner it Can I make this happen? I can send an attchment of my document if this helps? thanks kathy Might try a PowerPoint newsgroup... Try the autoshapes, make a square, right-click, add text, then rotate. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "kathy" <kathy@discussions.microsoft.com> wrote in message news:B3DD257A-FA1F-4EDD-A768-E68B3CE111CC@microsoft.com... > Hi: > I am working w...

Query Criteria 02-10-10
I have a query in which I only want the top 10 stores for item velocity for multiple groups. I have used the top value and max criteria but it only brings back the top or max for the entire dataset, not for each group within the data. What am I missing??? Post your table and field names with datatype that you would use in the query along with sample data (need not be real but workable). -- Build a little, test a little. "gabbyccf" wrote: > I have a query in which I only want the top 10 stores for item velocity for > multiple groups. I have used the top...