Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-22-2011, 03:53 AM   #1
WalterInOz
Newly Registered User
 
Join Date: Apr 2006
Location: Brisbane
Posts: 93
Thanks: 3
Thanked 0 Times in 0 Posts
WalterInOz is an unknown quantity at this point
working fine in.mdb but not in .accdb database

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

WalterInOz is offline   Reply With Quote
Old 03-22-2011, 04:27 AM   #2
JANR
Newly Registered User
 
Join Date: Jan 2009
Location: Norway
Posts: 1,623
Thanks: 7
Thanked 165 Times in 158 Posts
JANR has a spectacular aura about JANR has a spectacular aura about
Re: working fine in.mdb but not in .accdb database

Disambigue your recordset.

Code:
Dim rs As DAO.Recordset
Dim CountLines
Dim f As Form
Dim KeyName As String
Dim KeyValue
JR
JANR is offline   Reply With Quote
Old 03-22-2011, 04:30 AM   #3
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: working fine in.mdb but not in .accdb database

First of all, what is the exact error message?

vbaInet is offline   Reply With Quote
Old 03-22-2011, 05:16 AM   #4
WalterInOz
Newly Registered User
 
Join Date: Apr 2006
Location: Brisbane
Posts: 93
Thanks: 3
Thanked 0 Times in 0 Posts
WalterInOz is an unknown quantity at this point
Re: working fine in.mdb but not in .accdb database

Quote:
Originally Posted by JANR View Post
Disambigue your recordset.

Code:
Dim rs As DAO.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.
WalterInOz is offline   Reply With Quote
Old 03-22-2011, 05:23 AM   #5
JANR
Newly Registered User
 
Join Date: Jan 2009
Location: Norway
Posts: 1,623
Thanks: 7
Thanked 165 Times in 158 Posts
JANR has a spectacular aura about JANR has a spectacular aura about
Re: working fine in.mdb but not in .accdb database

No problem

JR
JANR is offline   Reply With Quote
Old 03-22-2011, 06:03 AM   #6
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,462
Thanks: 51
Thanked 949 Times in 918 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: working fine in.mdb but not in .accdb database

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the top right of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 03-22-2011, 06:35 AM   #7
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,814 Times in 1,574 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: working fine in.mdb but not in .accdb database

Quote:
Originally Posted by gemma-the-husky View Post
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.


__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
access 2007 accdb database frontend and forms Andromeda31 Theory and practice of database design 3 11-04-2009 11:35 AM
Question Access 2007 accdb creates Database.mdb Maritza General 0 08-17-2009 04:53 AM
Creating 2 user accounts in accdb database Eeebs General 1 04-23-2009 06:39 AM
Help - .mdb vs. .accdb KAT1162 General 8 09-01-2008 09:39 AM
If statement was working fine now shows error BarryMK Modules & VBA 2 02-21-2006 07:34 AM




All times are GMT -8. The time now is 04:01 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World