Efficient way of searching through records?

[AUGuy]

This is searching through a table of ~10,000 records to find whether the user-entered project# already exists, and populate the form if it does. I don't want to use a bound form here, with that in mind is this the most efficient way of searching through the ProjID field? thanks!

Private Sub Project_Number_LostFocus()
Dim intProj As Integer
Dim rs As Recordset
Dim db As Database
Dim match As String
Set db = CurrentDb
intProj = Me.Project_Number
Set rs = db.OpenRecordset("dbo_tblProj")
Me.lbSearching.Visible = True
DoEvents
rs.MoveFirst
Do Until match = "True" Or match = "Limit"
Select Case rs("ProjID").Value = intProj
        Case Is = True
            With Forms!frm_Create_Project
                .Project_Description = rs("ProjComments")
                .Address = rs("ProjAddrLine1")
                .City = rs("ProjCity")
                .State = rs("ProjState")
                .Zip = rs("ProjZip")
                .County = rs("ProjCounty")
            End With
            match = True
            rs.Close
        Case Is = False
            With Forms!frm_Create_Project
                .Project_Description = ""
                .Address = ""
                .City = ""
                .State = ""
                .Zip = ""
                .County = ""
            End With
            If rs("ProjID") > intProj Then match = "Limit"
            rs.MoveNext
    End Select
Loop
Me.lbSearching.Visible = False
End Sub

 

[spikepl]

Use .FindFirst with the search criteria, no loops . .. and ProjID should be indexed .. and the recordset should be a query, becasue a table has no inherent order, and so your current setup might yield unexpected results.

 

[AUGuy]

Thanks! I didn't think about FindFirst. Is there a way to account for FindFirst not finding a matching record?

 

[spikepl]

Yup. Look it up in the docs

 

[AUGuy]

Fair enough! NoMatch ftw.