RowNum using Numbers 2 Roman code

  • Thread starter Thread starter Alex Y
  • Start date Start date
A

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.
 
Question: Instead of ordering the rows with numbers, how can I incorporate
the following additional code to convert the row number into Roman numerals?
Click to expand...

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
 
John W. Vinson said:
Question: Instead of ordering the rows with numbers, how can I incorporate
the following additional code to convert the row number into Roman numerals?
Click to expand...

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
Click to expand...

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?
 
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.
Click to expand...

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...
 
John W. Vinson said:
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...
Click to expand...

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.
 
It is in the 9th line of the Num2Roman routine.
Click to expand...

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.
 
Awesome! Thanks for your help.

John W. Vinson said:
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.
Click to expand...
 
Back
Top