locked
need help with FindFirst RRS feed

  • Question

  •      I am new to VBA although I have experience as a COBOL programmer.  When I try to use The FindFirst statement, I get a message 'expected function or variable'  Can anyone clarify this situation?
    Tuesday, September 18, 2018 9:57 PM

All replies

  • Could you please post your full procedure for us to review.

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, September 18, 2018 10:34 PM
  • You haven't told us what code you tried...

    You have to specify a condition in terms of a field (or fields) in the recordset that you opened. You can use FindFirst like this:

        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("MyTable", dbOpenDynaset)
        rst.FindFirst "LastName='Jones'"
        If rst.NoMatch Then
            MsgBox "Jones not found!"
        Else
            ' Do something with the record
            MsgBox rst!FirstName
        End If
        rst.Close


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, September 18, 2018 10:39 PM
  • I agree with the other posts. You have not shared your code for us to review. But I was able to find a sample with FindFirst and FindNext methods in the following post - https://msdn.microsoft.com/en-us/library/office/ff194787.aspx

    Sub FindOrgName()
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        
        'Get the database and Recordset
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("tblCustomers")
    
        'Search for the first matching record   
        rst.FindFirst "[OrgName] LIKE '*parts*'"
        
        'Check the result
        If rst.NoMatch Then
            MsgBox "Record not found."
            GotTo Cleanup
        Else
            Do While Not rst.NoMatch
                MsgBox "Customer name: " & rst!CustName
                rst.FindNext "[OrgName] LIKE '*parts*'"
            Loop
    
            'Search for the next matching record
            rst.FindNext "[OrgName] LIKE '*parts*'"
        End If
       
        Cleanup:
            rst.Close
            Set rst = Nothing
            Set dbs = Nothing
    
    End Sub

    Tuesday, September 18, 2018 11:27 PM
  • Not really a “big” deal, but you can also often put your condition in place of the table.

    So, your code could become this:

    Sub Test5()
    
    
       Dim rst     As DAO.Recordset
    
       Set rst = CurrentDb.OpenRecordset("select * from tblCustomers where OrgName like '*parts*")
    
       Do While rst.EOF = False
    
          MsgBox "Customer name =  " & rst!CustName
          rst.MoveNext
    
       Loop
    
       rst.Close
    
    End Sub
    

    I suppose if you wanted to display a “not found”, then we get:

    Sub Test55()
       Dim rst     As DAO.Recordset
    
       Set rst = CurrentDb.OpenRecordset("select * from tblCustomers where OrgName like '*parts*")
    
       If rst.RecordCount = 0 = True Then
    
         MsgBox "not found"
    
       Else
    
          Do While rst.EOF = False
    
             MsgBox "Customer name =  " & rst!CustName
    
             rst.MoveNext
    
          Loop
    
         rst.Close
    
       End If
    
    End Sub
    

    So I will say that we really don’t have to use “find first” all that much, since the criteria can (and should) be included in the query that you can freely use in place of the table name.

    There is no use to load up say a table of 100,000 recods, and THEN execute some searching.

    And, if your search is not a wild card, then high speed indexing is used and ONLY records are pulled.

    Even in Access, say you want to find a invoice, and you have 1 million rows, the following will NOT load the whole table, and the results will appear near instant:

    Sub Test555()
    
       Dim rst     As DAO.Recordset
       Dim strSQL  As String
    
       strSQL = "select * from tblInvoices where invoiceNum = 13355"
       Set rst = CurrentDb.OpenRecordset(strSQL)
    
       If rst.RecordCount = 0 Then
    
          MsgBox "invoice not found"
    
       Else
    
          MsgBox "invoice found"
    
       End If
    
       rst.Close
    
    End Sub
    

    So above will ONLY pull and fill the record set with one record. This even occurs if you were using access over a local network.

    So as rule of thumb, try to put the criteria WHEN you create the record set, not pull a large record set and THEN execute a find First on that large data set.

    So above from a performance point of view is a great tip to keep in mind. The performance when you do this is really mind boggling.

    No real worries about the approach here, but Access is often a big challenge due to so many ways to accomplish a given task – that tends to be the hard part!

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Wednesday, September 19, 2018 3:42 AM