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
CWBody = CwBodyIn

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


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

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

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
                BodyStr = 0
                Exit Function
                '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
                '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
                                    Counter = Counter + 1
                                End If
                        Next index3
                    Next indexTest2
                    If Counter > 0 Then
                    '*************************<wbr ></wbr>**********<wbr ></wbr>****
                    With RstAssociation
                        SearchString = "[TVIMVRIS] = '" & TVIMVRis & "'"
                        .FindFirst SearchString     '*******fails here
                        If RstAssociation.NoMatch Then
                            BodyStr = -1
                            Exit Function
                            If .Fields("CW_ABI").Value = .Fields("TVI_ABI").Value Then
                                BodyStr = 0
                                Exit Function
                                BodyStr = -1
                                Exit Function
                            End If
                        End If
                    End With
                    End If
                End If
            End If

    Next index2
Next index


End Function

Open in new window

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.


pls try

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

Open in new window


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.
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
Point 2 of

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.


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