Cannot do .findfirst in access vba

I get an error 3251 when trying to perform a .findfirst method.

not sure why, the data is all there row 154

the table is local

Public Function BodyStr(ClientStrIn, CwBodyIn, TVIDoorsIn, CWDoorsIn, TVIMVRisIn) As Integer

Dim ClientBody As String
Dim CWBody As String
Dim TVIDoors As Integer
Dim CWDoors As Integer

Dim TVIMVRis As String

Dim Counter As Integer
Dim Body1() As String
Dim Body2() As String
Dim Body3() As String

Dim index As Integer
Dim index2 As Integer
Dim index3 As Integer

Dim db As Database
Set db = CurrentDb

Dim rstAlias As dao.Recordset
Dim RstAssociation As dao.Recordset

Dim StrCWAlias As String
Dim StrClientAlias As String

Dim StrSelect As String
Dim StrFrom As String
Dim StrHaving As String
Dim StrGroupBy As String
Dim StrQuery As String

Dim z As Integer

StrSelect = "SELECT tClientAlias.sClient, tCWAlias.sCWDescGroup, tClientAlias.sClientDescGr<wbr ></wbr>oup, tClientAlias.sTechnicalGro<wbr ></wbr>up, tCWAlias.sCWDesc, tClientAlias.sDesc "
StrFrom = "FROM tCWAlias RIGHT JOIN tClientAlias ON tCWAlias.sCWDescGroup = tClientAlias.sClientDescGr<wbr ></wbr>oup "
StrGroupBy = "GROUP BY tClientAlias.sClient, tCWAlias.sCWDescGroup, tClientAlias.sClientDescGr<wbr ></wbr>oup, tClientAlias.sTechnicalGro<wbr ></wbr>up, tCWAlias.sCWDesc, tClientAlias.sDesc "
StrHaving = "HAVING (((tClientAlias.sClient)="<wbr ></wbr>"TVI"") AND ((tClientAlias.sTechnicalG<wbr ></wbr>roup)=""bo<wbr ></wbr>dy"") AND ((tCWAlias.sCWDesc)=""" & CwBodyIn & """));"

StrQuery = StrSelect & StrFrom & StrGroupBy & StrHaving
'Debug.Print StrQuery
Set rstAlias = db.OpenRecordset(StrQuery)<wbr ></wbr>
Set RstAssociation = db.OpenRecordset("TblAssoc<wbr ></wbr>iation")

ClientBody = ClientStrIn
TVIDoors = TVIDoorsIn
CWDoors = CWDoorsIn
TVIMVRis = TVIMVRisIn
CWBody = CwBodyIn


'populate array with alias to find
If rstAlias.BOF And rstAlias.EOF = True Then
ReDim Body3(0)

Else

With rstAlias
.MoveLast
z = .RecordCount - 1
.MoveFirst
ReDim Body3(z)
index3 = 0
    While rstAlias.EOF <> True

        Body3(index3) = .Fields("sDesc")
        index3 = index3 + 1
        .MoveNext
    Wend

End With

End If





        
Body1 = Split(UCase(ClientBody))
Body2 = Split(UCase(CWBody))
        
        

            'do straight test here without alias if we pass we can exit if not do estate alias test
      
             


            

            'now take doors into consideration

           
      
            

        
        
        
        
        
        
             '*************************<wbr ></wbr>**********<wbr ></wbr>**********<wbr ></wbr>**********<wbr ></wbr>**********<wbr ></wbr>**********<wbr ></wbr>**
            For index = LBound(Body1) To UBound(Body1)
    For index2 = LBound(Body2) To UBound(Body2)

            If InStr(UCase(Body1(index)),<wbr ></wbr> UCase(Body2(index2))) <> 0 Then
            'pass
                BodyStr = 0
                Exit Function
            Else
            'failed
                'look at alias
             'test 2
                '*************************<wbr ></wbr>**********<wbr ></wbr>**********<wbr ></wbr>**********<wbr ></wbr>**********<wbr ></wbr>**********<wbr ></wbr>**********<wbr ></wbr>********
                '*   check if alias body style keywords related to cwbody are shown in client body array
                '*   ie if CW says station wagon we can resolve test to estate as client considers them the same
            
                indexTest2 = 0
                If rstAlias.BOF And rstAlias.EOF = True Then
                ' dont do alias test as no alias data
                
                
                Else
                
                'do alias test
                   For indexTest2 = LBound(Body1) To UBound(Body1)
                        For index3 = LBound(Body3) To UBound(Body3)
                
                                If InStr(UCase(Body1(indexTes<wbr ></wbr>t2)), UCase(Body3(index3))) <> 0 Then
                                    BodyStr = 0
                                    Exit Function
                                Else
                                    
                                    Counter = Counter + 1
                
                                End If
                
                        Next index3
                    Next indexTest2
                    
                    If Counter > 0 Then
                    'failed
                    
                    
                    '*************************<wbr ></wbr>**********<wbr ></wbr>****
                    
                    With RstAssociation
                        .MoveLast
                       
                        SearchString = "[TVIMVRIS] = '" & TVIMVRis & "'"
                        .FindFirst SearchString     '*******fails here
                        
                        If RstAssociation.NoMatch Then
                            BodyStr = -1
                            Exit Function
                        Else
                        
                            If .Fields("CW_ABI").Value = .Fields("TVI_ABI").Value Then
                                BodyStr = 0
                                Exit Function
                            Else
                                BodyStr = -1
                                Exit Function
                            
                            
                            End If
                            
                            
                        End If
                        
                    End With
                        
                      
                    
                    
                    
                    
                    
                    
                    
                    
                 
                    
                    Else
                    
                    End If
                    
                End If
                
             
            End If

    Next index2
Next index
            
            
 
            

    
    



End Function

Open in new window

PeterBaileyUkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,

pls try

Set RstAssociation = db.OpenRecordset("TblAssociation", dbOpenDynaset)

Open in new window


Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterBaileyUkAuthor Commented:
Thank you.

could you let me know why it failed hopefully i wont make the same mistake again, i see the syntax change but not sure of different way.
0
Rey Obrero (Capricorn1)Commented:
on line 151

                   With RstAssociation
                        .MoveLast  ' why this line???? if you  want to retain this line ADD the line below

                        .MoveFirst           '<<<< Add this line


                        SearchString = "[TVIMVRIS] = '" & TVIMVRis & "'"
                        .FindFirst SearchString     '*******fails here
0
Rgonzo1971Commented:
Point 2 of
 http://allenbrowne.com/ser-29.html


2. Recordset types

There are different types of DAO recordset, with different methods.

When you OpenRecordset() on a query or attached table, Access defaults to a Dynaset type (dbOpenDynaset). When you OpenRecordset() on a local table, it defaults to a Table type (dbOpenTable.)

The Table type has different methods (e.g. Seek instead of FindFirst), but it cannot be used with attached tables. So if you later split your database so the tables are attached, the code fails when you use a method that no longer applies.

Solution:

Always specify the type you want. Dynaset guarantees your code will work for all queries and tables, local and attached. Example:
    Set rs = db.OpenRecordset("Table1", dbOpenDynaset)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.