I have used the code below for about 4 years in .mdb databases. It's function is to highlight the selected record on a continuous form, worked great. I found it on this forum and I now hope other people I are using this code too and have experience with the problem I experience since I upgraded to A2010 and have converted a few databases from the .mdb format to the .accdb format.
Opening a continuous form now triggers an error in the rs.FindFirst part of the code. Anyone any idea why this is and how I can solve the problem?
' Code Courtesy of
' James H Brooks
'The function "GetLineNumber" is modified from the Microsoft Knowledge Base
' (Q120913), the only difference here is that the following items have been hard
'coded:F, KeyName, KeyValue. This was done to add a slight performance
'increase. Change KeyName and KeyValue to reflect the key in your table.
Dim rs As Recordset
Dim f As Form
Dim KeyName As String
Set f = Form
KeyName = "DocID"
KeyValue = [DocID]
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 DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
rs.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value.
rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value.
rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
MsgBox "ERROR: Invalid key field data type!"
' Loop backward, counting the lines.
Do Until rs.BOF
CountLines = CountLines + 1
Bye_GetLineNumber: ' Return the result.
GetLineNumber = CountLines
CountLines = 0