RowNum using Numbers 2 Roman code

[Alex Y]

I am invoking the following code from a form to number rows (thanks to
Stephan Lebans, Chris Bergmans and Allen Browne):

Public Function RowNum(frm As Form) As Variant
On Error GoTo Err_RowNum
'Purpose: Numbering the rows on a form.
'Usage: Text box with ControlSource of: =RowNum([Form])

With frm.RecordsetClone
.Bookmark = frm.Bookmark
RowNum = .AbsolutePosition + 1
End With

Exit_RowNum:
Exit Function

Err_RowNum:
If Err.Number <> 3021& Then 'Ignore "No bookmark" at new row.
Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
End If
RowNum = Null
Resume Exit_RowNum
End Function

Question: Instead of ordering the rows with numbers, how can I incorporate
the following additional code to convert the row number into Roman numerals?

Public Function Num2Roman(ByVal N As Integer) As String

Const Digits = "IVXLCDM"

Dim I As Integer, Digit As Integer, Temp As String

I = 1
Temp = ""
N = RowNum(frm as Form)
Do While N > 0
Digit = N Mod 10
N = N \ 10
Select Case Digit
Case 1
Temp = Mid(Digits, I, 1) & Temp
Case 2
Temp = Mid(Digits, I, 1) & Mid(Digits, I, 1) & Temp
Case 3
Temp = Mid(Digits, I, 1) & Mid(Digits, I, 1) & _
Mid(Digits, I, 1) & Temp
Case 4
Temp = Mid(Digits, I, 2) & Temp
Case 5
Temp = Mid(Digits, I + 1, 1) & Temp
Case 6
Temp = Mid(Digits, I + 1, 1) & Mid(Digits, I, 1) & Temp
Case 7
Temp = Mid(Digits, I + 1, 1) & Mid(Digits, I, 1) & _
Mid(Digits, I, 1) & Temp
Case 8
Temp = Mid(Digits, I + 1, 1) & Mid(Digits, I, 1) & _
Mid(Digits, I, 1) & Mid(Digits, I, 1) & Temp
Case 9
Temp = Mid(Digits, I, 1) & Mid(Digits, I + 2, 1) & Temp
End Select
I = I + 2
Loop
Num2Roman = Temp
End Function

Thanks for any help.

 

[John W. Vinson]

Question: Instead of ordering the rows with numbers, how can I incorporate
the following additional code to convert the row number into Roman numerals?

Just call it from the first function:

Public Function RowNum(frm As Form) As Variant
On Error GoTo Err_RowNum
'Purpose: Numbering the rows on a form.
'Usage: Text box with ControlSource of: =RowNum([Form])

With frm.RecordsetClone
.Bookmark = frm.Bookmark
RowNum = Num2Roman(.AbsolutePosition + 1)
End With

Exit_RowNum:
Exit Function

Err_RowNum:
If Err.Number <> 3021& Then 'Ignore "No bookmark" at new row.
Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
End If
RowNum = Null
Resume Exit_RowNum
End Function

 

[Alex Y]

Question: Instead of ordering the rows with numbers, how can I incorporate
the following additional code to convert the row number into Roman numerals?

Just call it from the first function:

Public Function RowNum(frm As Form) As Variant
On Error GoTo Err_RowNum
'Purpose: Numbering the rows on a form.
'Usage: Text box with ControlSource of: =RowNum([Form])

With frm.RecordsetClone
.Bookmark = frm.Bookmark
RowNum = Num2Roman(.AbsolutePosition + 1)
End With

Exit_RowNum:
Exit Function

Err_RowNum:
If Err.Number <> 3021& Then 'Ignore "No bookmark" at new row.
Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
End If
RowNum = Null
Resume Exit_RowNum
End Function

I am receiving a "Compile error: Syntax error" message, with "N =
RowNum(frm as Form)" from the Num2Roman procedure being highlighted in red.

The Num2Roman procedure is in the same module as the RowNum procedure.

Any suggestions?

 

[John W. Vinson]

I am receiving a "Compile error: Syntax error" message, with "N =
RowNum(frm as Form)" from the Num2Roman procedure being highlighted in red.

The Num2Roman procedure is in the same module as the RowNum procedure.

Please post the full VBA of both routines. There's no line N = RowNum anywhere
in the code I wrote or the code you posted...

 

[Alex Y]

Please post the full VBA of both routines. There's no line N = RowNum anywhere
in the code I wrote or the code you posted...

It is in the 9th line of the Num2Roman routine.

Here is the RowNum procedure, followed by Num2Roman:

Public Function RowNum(frm As Form) As Variant
On Error GoTo Err_RowNum
'Purpose: Numbering the rows on a form.
'Usage: Text box with ControlSource of: =RowNum([Form])

With frm.RecordsetClone
.Bookmark = frm.Bookmark
RowNum = Num2Roman(.AbsolutePosition + 1)
End With

Exit_RowNum:
Exit Function

Err_RowNum:
If Err.Number <> 3021& Then 'Ignore "No bookmark" at new row.
Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
End If
RowNum = Null
Resume Exit_RowNum
End Function

Public Function Num2Roman(ByVal N As Integer) As String

Const Digits = "IVXLCDM"

Dim I As Integer, Digit As Integer, Temp As String

I = 1
Temp = ""
N = RowNum(frm as Form)
Do While N > 0
Digit = N Mod 10
N = N \ 10
Select Case Digit
Case 1
Temp = Mid(Digits, I, 1) & Temp
Case 2
Temp = Mid(Digits, I, 1) & Mid(Digits, I, 1) & Temp
Case 3
Temp = Mid(Digits, I, 1) & Mid(Digits, I, 1) & _
Mid(Digits, I, 1) & Temp
Case 4
Temp = Mid(Digits, I, 2) & Temp
Case 5
Temp = Mid(Digits, I + 1, 1) & Temp
Case 6
Temp = Mid(Digits, I + 1, 1) & Mid(Digits, I, 1) & Temp
Case 7
Temp = Mid(Digits, I + 1, 1) & Mid(Digits, I, 1) & _
Mid(Digits, I, 1) & Temp
Case 8
Temp = Mid(Digits, I + 1, 1) & Mid(Digits, I, 1) & _
Mid(Digits, I, 1) & Mid(Digits, I, 1) & Temp
Case 9
Temp = Mid(Digits, I, 1) & Mid(Digits, I + 2, 1) & Temp
End Select
I = I + 2
Loop
Num2Roman = Temp
End Function

Thanks, John.

 

[John W. Vinson]

It is in the 9th line of the Num2Roman routine.

This one?

N = RowNum(frm as Form)

Just delete that line. The form reference is in the calling routine; the value
of N is passed as a parameter.

 

[Alex Y]

Awesome! Thanks for your help.

This one?

N = RowNum(frm as Form)

Just delete that line. The form reference is in the calling routine; the value
of N is passed as a parameter.