working fine in.mdb but not in .accdb database (1 Viewer)

WalterInOz

Registered User.
Local time
Tomorrow, 06:42
Joined
Apr 11, 2006
Messages
93
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:
 ' Code Courtesy of
' James H Brooks
'
Function GetLineNumber()
'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 CountLines
Dim f As Form
Dim KeyName As String
Dim KeyValue

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, _
            DB_DOUBLE, DB_BYTE
               rs.FindFirst "[" & KeyName & "] = " & KeyValue
            ' Find using date data type key value.
            Case DB_DATE
               rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
            ' Find using text data type key value.
            Case DB_TEXT
               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
 

JANR

Registered User.
Local time
Today, 20:42
Joined
Jan 21, 2009
Messages
1,623
Disambigue your recordset.

Code:
Dim rs As [B][COLOR=red]DAO.[/COLOR][/B]Recordset
Dim CountLines
Dim f As Form
Dim KeyName As String
Dim KeyValue

JR
 

vbaInet

AWF VIP
Local time
Today, 19:42
Joined
Jan 22, 2010
Messages
26,374
First of all, what is the exact error message?
 

WalterInOz

Registered User.
Local time
Tomorrow, 06:42
Joined
Apr 11, 2006
Messages
93
Disambigue your recordset.

Code:
Dim rs As [B][COLOR=red]DAO.[/COLOR][/B]Recordset
Dim CountLines
Dim f As Form
Dim KeyName As String
Dim KeyValue

JR

Thanks JANR, the DAO addition solved my problem.

@vbaInet
Error message was Complie error. member not found.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:42
Joined
Sep 12, 2006
Messages
15,614
ARe you sure it isn't something to do with the constants. what are the DB_ constants - access datatype constants start with AC - so you must have added something with all these DB_ references. Maybe the problem is in that bit of code.
 

boblarson

Smeghead
Local time
Today, 12:42
Joined
Jan 12, 2001
Messages
32,059
ARe you sure it isn't something to do with the constants. what are the DB_ constants - access datatype constants start with AC - so you must have added something with all these DB_ references. Maybe the problem is in that bit of code.

The problem (as the OP has confirmed) that the declaration of the recordset object needs to be non-ambiguous (because they have both ADO and DAO references checked). Good to be explicit anyway because you never know where something is going to be running.
 

Users who are viewing this thread

Top Bottom