Good Day!
I have a form with a subform and I want to have a line number for each of the records that will be displayed.
Here is my setup:
I have a table with the field "ID" with the setup "Integer".
I have a module called GetLNumber and put this code
Expand|Select|Wrap|Line Numbers
- Function GetLineNumber(F As Form, KeyName As String, KeyValue)
- Dim RS As DAO.Recordset
- Dim CountLines
- On Error GoTo Err_GetLineNumber
- Set RS = F.RecordsetClone
- ' Find the current record.
- Select Case RS.Fields(KeyName).Type
- ' Find using numeric data type key value.
- Case dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbByte
- RS.FindFirst "[" & KeyName & "] = " & KeyValue
- ' Find using date data type key value.
- Case dbDate
- RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
- ' Find using text data type key value.
- Case dbText
- RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
- Case Else
- MsgBox "ERROR: Invalid key field data type!"
- Exit Function
- End Select
- ' Loop backward, counting the lines.
- Do Until RS.BOF
- CountLines = CountLines + 1
- RS.MovePrevious
- Loop
- Bye_GetLineNumber:
- ' Return the result.
- GetLineNumber = CountLines
- Exit Function
- Err_GetLineNumber:
- CountLines = 0
- Resume Bye_GetLineNumber
- End Function
but when I click Form View, the field for txtID contains #Type!
I tried this code also but still no luck.
Expand|Select|Wrap|Line Numbers
- Function GetLineNumber(F As Form, KeyName As String, KeyValue)
- Dim RS As Object
- Dim CountLines
- On Error GoTo Err_GetLineNumber
- Set RS = F.Recordset.Clone
- RS.Find "[" & KeyName & "] = " & KeyValue
- ' Loop backward, counting the lines.
- Do Until RS.BOF
- CountLines = CountLines + 1
- RS.MovePrevious
- Loop
- Bye_GetLineNumber:
- ' Return the result.
- GetLineNumber = CountLines
- Exit Function
- Err_GetLineNumber:
- CountLines = 0
- Resume Bye_GetLineNumber
- End Function
Your help is very much appreciated!
tia