Problems with RecordsetClone.RecordCount in Access 2007

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

I would say you win :-)

mx

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.....

P.S. Looks like access is winning right now

Unfortunately, I'm not running A2007 yet.  A few experts here are however.

mx

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?

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

Select allOpen in new window

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....

strange for sure ...

mx

And it works in 2003 as well so guess thats definatly what i'll be going with.

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.