Increment "text" not number

[Guest]

Hi All

I use this to show incremental numbers for record in a subform

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.PayItemID = Nz(DMax("[PayItemID]", "tblPaymentItems", "[PayID] = " &
Me.PayID)) + 1
End Sub

So you get
Main record ID = 123, 124, 125, etc

Sub form ID = 1, 2, 3, 4 (not indexed)

Concencated text box = 123 / 1, 123 / 2, 123 / 3, etc

Is it possible to increment Letters (A to Z) instead of number so you would
have
Sub form ID = A, B, C, D
Note there are never more than 15 subform records.

I just think it would look better and be easier to understand.

 

[Guest]

Wayne:

You can use the Asc function to return the ANSI character code of the letter
and increment to the letter with the next code value, using the Chr function
to return the letter for that code:

Me.PayItemID = Chr(Asc(Nz(DMax("[PayItemID]", "tblPaymentItems", "[PayID] =
" & Me.PayID),"@")) + 1)

The @ sign precedes the upper case A in the ANSI sequence, so returning this
if the DMax function call returns a Null will assign an upper case A when the
ANSI code value is incremented by 1

Ken Sheridan
Stafford, England

 

[Guest]

Many thanks Ken

--
Wayne
Manchester, England.

Wayne:

You can use the Asc function to return the ANSI character code of the letter
and increment to the letter with the next code value, using the Chr function
to return the letter for that code:

Me.PayItemID = Chr(Asc(Nz(DMax("[PayItemID]", "tblPaymentItems", "[PayID] =
" & Me.PayID),"@")) + 1)

The @ sign precedes the upper case A in the ANSI sequence, so returning this
if the DMax function call returns a Null will assign an upper case A when the
ANSI code value is incremented by 1

Ken Sheridan
Stafford, England

Hi All

I use this to show incremental numbers for record in a subform

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.PayItemID = Nz(DMax("[PayItemID]", "tblPaymentItems", "[PayID] = " &
Me.PayID)) + 1
End Sub

So you get
Main record ID = 123, 124, 125, etc

Sub form ID = 1, 2, 3, 4 (not indexed)

Concencated text box = 123 / 1, 123 / 2, 123 / 3, etc

Is it possible to increment Letters (A to Z) instead of number so you would
have
Sub form ID = A, B, C, D
Note there are never more than 15 subform records.

I just think it would look better and be easier to understand.