Solved

Problems with RecordsetClone.RecordCount in Access 2007

Posted on 2009-07-07
14
1,717 Views
Last Modified: 2013-11-28
I have created a form to search the database that has several unbound fields in the form header that allow a user to enter criteria that gets dynamically filtered as they are typing.  And it works perfectly.....in Access 2003.  I have users that will be using both Access 2003 and 2007 so I figured that I would take it for a test run in 2007 and it works....for one letter.  I went thru and debugged the code to figure out why I was always getting "No Records Returned" after I entered a second letter....

For some reason this code works in 2003, but not 2007:
 If Me.RecordsetClone.RecordCount = 0 Then
       
        MsgBox "No records returned"

I can't just get rid of the code because of other access bugs dealing with unbound textboxes in the form header (I can show you the threads on EE if you like).  So what I need is a way to test the number of records in my form after a filter is applied to it that works everytime and not just the first time I run thru the code.

I have looked around and seen lots of problems with the RecordCount in 07, but they all seemed to happen when trying to use the information in a textbox.

Any ideas?

Access 07 is on a different computer with Vista where  the other computer is Access 03 and XP.  I also installed all the updates from Windows Update two days ago so Access 07 is up to date.

Oh the joys of Microsoft....

Thanks for any help in advance
0
Comment
Question by:jackrabbitman
  • 8
  • 4
  • 2
14 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 50 total points
ID: 24796250
For some reason I'm finding it hard to believe that

 If Me.RecordsetClone.RecordCount = 0 Then

does not work.  Are you saying it's not returning zero when it IS zero?  If you put a breakpoint on that line of code, what is the value of the RecordCount ?

mx
0
 

Author Comment

by:jackrabbitman
ID: 24796412
Alright you win, Me.RecordsetClone.RecordCount = 0 is right....so now I have to go find out why it brings back 0 records with the same Filtering criteria as  I used in 2003
0
 
LVL 75
ID: 24796442
I would say you win :-)

mx
0
 

Author Comment

by:jackrabbitman
ID: 24796492
Okay....I just don't get it.  I have made a button that runs the same code (and "turns off" the onChange event that normally occurs) and if I enter in the same criteria that I'm trying to enter in with the dynamic filter and it works.....but once again only one time and it comes back with 0 records the second time again.....
0
 

Author Comment

by:jackrabbitman
ID: 24796523
P.S. Looks like access is winning right now
0
 
LVL 75
ID: 24796544
Unfortunately, I'm not running A2007 yet.  A few experts here are however.

mx
0
 

Author Comment

by:jackrabbitman
ID: 24796586
Well thanks for your insight.....and I wish I didn't have to, but seeing as half the office is using 07 and the rest will eventually get there...something that works in 2003 and not 2007 isn't really an option.

Any help from someone with Office 07?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 50 total points
ID: 24796817
Unless I'm missing something - you've not actually given us anything to work with as yet?
(Other than a description of your scenario).
Expression suppose in 2007 has been hacked back yes. So whereas you could freely build expression in previous versions referring to the RecordCount (and even to objects such as CurrentDb) that isn't available in 2007.
However in code your comparisons should be fine.
It seems that you're saying that the problem is now with your search.
We don't know much about that though - other than you're running code for a dynamic filter based on entered text. :-s
0
 

Author Comment

by:jackrabbitman
ID: 24797029
Haha....good point.  I'm sorry that I made the assumption that people I've worked with before on this same form would be helping me (such as MX) so....i'll post the code that shows the filter information and perhaps you could provide some insight....

Sorry the style isn't that great, but I kept running into NULL problems.

And to point out what I said before...the code works with the button that I put in that ignores the onChange event for each text box and runs this same code.
Sub searchData()
 

    'Declare all variables

    Dim condition As Boolean

    Dim criteria As String

    Dim length As Integer

    Dim oldCriteria As String

    Dim varCname As String

    Dim varFname As String

    Dim varLname As String

    Dim count As Integer

    count = 0

    

    

    'Fill variables if they are not null in case of Error

    If Me.txtCname <> "" Then

        varCname = Me.txtCname

    Else

        varCname = ""

    End If

    

    If Me.txtFname <> "" Then

        varFname = Me.txtFname

    Else

        varFname = ""

    End If

      

    If Me.txtLname <> "" Then

        varLname = Me.txtLname

    Else

        varLname = ""

    End If   
 

    If Me.Filter <> "" Then

        oldCriteria = Me.Filter

    Else

        oldCriteria = ""

    End If

    

    criteria = ""

    condition = False
 

    With Me

    

        'Company Name Criteria

        .txtCname.SetFocus

        .txtCname2 = .[txtCname].Text

        

        If .txtCname2 <> "" Then

        

            length = Len(.txtCname2)

    

            criteria = "(((Left(CompanyName, " & length & ")) = [Forms]![Search].txtCname)) "

            condition = True

        

        End If

        

        'First Name Criteria

        .txtFname.SetFocus

        .txtFname2 = .[txtFname].Text

        

        If .txtFname2 <> "" And criteria = "" Then
 

            length = Len(.txtFname2)

    

            criteria = "(((Left(Fname, " & length & ")) = [Forms]![Search].txtFname)) "

            condition = True

        

        ElseIf .txtFname2 <> "" Then

    

            length = Len(.txtFname2)

    

            criteria = criteria & "AND (((Left(Fname, " & length & ")) = [Forms]![Search]![txtFname])) "

        

        End If

    

    

        'Last Name Criteria

        .txtLname.SetFocus

        .txtLname2 = .[txtLname].Text

        

        If .txtLname2 <> "" And criteria = "" Then

        

            length = Len(.txtLname2)

    

            criteria = "(((Left(Lname, " & length & ")) = [Forms]![Search].[txtLname])) "

            condition = True

    

        ElseIf .txtLname2 <> "" Then

        

            length = Len(.txtLname2)

    

            criteria = criteria & "AND (((Left(Lname, " & length & ")) = [Forms]![Search].[txtLname])) "

        

        End If
 

    End With

    

    'Apply Criteria to the filter

    If condition Then

    

        Forms!Search.Filter = criteria

        Forms!Search.FilterOn = True

    

    Else

    

        Forms!Search.Filter = ""

        Forms!Search.FilterOn = False

    

    End If
 

    

    If Me.RecordsetClone.RecordCount = 0 Then

        MsgBox "No records returned"

            

        'For whatever reason the filter doesn't like to = "" on the first time and if this

        'doesn't happen it can mess up the code, so this loop is set up to fix that problem

                

        Do

            Me.Filter = ""

            count = count + 1

        Loop Until Me.Filter = "" Or count = 10

        

        Me.FilterOn = False

        

        'Assign propper value to txtCname

        If varCname = "" Then

            Me.txtCname = ""

        Else

            Me.txtCname = varCname

        End If

        

        'Assign propper value to txtFname

        If varFname = "" Then

            Me.txtFname = ""

        Else

            Me.txtFname = varFname

        End If

        

        'Assign propper value to txtLname

        If varLname = "" Then

            Me.txtLname = ""

        Else

            Me.txtLname = varLname

        End If
 

        If oldCriteria <> "" Then

            Me.Filter = oldCriteria

            Me.FilterOn = True

        End If

    End If

End Sub

Open in new window

0
 

Author Comment

by:jackrabbitman
ID: 24797430
I've done some more testing in 07 now and I guess I was wrong again...it seems to be in my code that something isn't getting updated or something so...it will work more than once now so I can't say for sure what it is....
0
 

Accepted Solution

by:
jackrabbitman earned 0 total points
ID: 24798650
Well for whatever reason it didn't like the code, I don't know, but I used what Access uses for its "Begins With" filter and did

criteria =  "([Table].CompanyName Like " & Chr(34) & txtCname2 & "*" & Chr(34) & ")"

and that seems to work no problem so now I'll just have to go and make all those changes.
0
 
LVL 75
ID: 24798667
strange for sure ...

mx
0
 

Author Comment

by:jackrabbitman
ID: 24798720
And it works in 2003 as well so guess thats definatly what i'll be going with.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 24799128
Well, not wanting to halt any automatic procedure thus giving the mods more to do - but this is the first chance I've had to come back and look at this - so I might as well risk it and comment anyway (things sure do happen in a rush at EE)...
The code section offered of itself is more informative.
For the solution, you're saying you used:
criteria =  "([Table].CompanyName Like " & Chr(34) & txtCname2 & "*" & Chr(34) & ")"
instead of:
criteria = "(((Left(CompanyName, " & length & ")) = [Forms]![Search].txtCname)) "
(Using "txtCname2" just for some testing reason perhaps? And the difference being in the criteria syntax - not that you've included the TableName prefix?)
Ultimately, the replacement syntax is definitely preferable.
Wrapping fields in function calls always adds overhead - even if there isn't an index that could have been made use of.
(If there is then it's even worse).
The latter requires that the form reference be correctly resolved as part of the expression.
As opposed to concatenating in the value as you're now doing:
criteria = "Left(CompanyName, " & length & ") = " & Chr(34) & [Forms]![Search].txtCname " & Chr(34) & ")"
or forcing evaluation:
criteria = "(((Left(CompanyName, " & length & ")) = Eval(""[Forms]![Search].txtCname""))) "
as such parameters can struggle with evaluation even in earlier versions of Access.
However, neither's a great option.
The absolutely appropriate syntax is to use the Like operator as you now are.
Equally applicable should be:
criteria =  "(CompanyName Like '" & txtCname2 & "*')"
Anyway.
Onwards.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now