Solved: Use of FindFirst

[MWE]

I have a modest sized table (3000 records and 50 fields/record) in an Access DB. I interact with it both using conventional approaches (Access interfaces and tools) and from other Office applications (mostly Excel and Outlook) using some custom VBA stuff.

re the custom VBA: Previously, I stepped through the table doing specific searches on specific fields. For example:
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Bus_card")
rst.MoveFirst
While Not (rst.EOF)
If rst.Fields("SomeField").Value) = SomeString Then


End If
rst.MoveNext
Wend I have typically used this "brute force" approach because the checking gets pretty complicated with a half dozen cascaded IFs. To speed things up for simplier searches, I have been trying to use the FindFirst/FindNext approach to find particular strings in specific fields without success. The examples provided in VBA Help are not very useful. Can someone provide a little assistance?

[OBP]

I would suggest using the Form's Filter set by a VBA generated filter string.
See the demo database called Search 2000 that I posted here, (it is not one of mine)

http://www.vbaexpress.com/forum/showthread.php?t=25027

[MWE]

Thanks for the reply. There is far too much overhead in the Search2000 approach and I want everything to be invisible. And I would prefer doing it with only VBA code. It is such an obvious thing to do that there must be some simple way to do this.

[geekgirlau]

Your sample is pretty limited in terms of the criteria you're looking for, but could you not filter the recordset before you even start? Instead of


Set rst = db.OpenRecordset("Bus_card")


Use


Set rst = db.OpenRecordset("SELECT * FROM Bus_card WHERE [SomeField] = <SomeValue>")


This way you know that all of the records match your criteria before you even start.

[MWE]

Thanks for the reply. I tried what you suggested and received execution errors. That type of syntax does not appear to be supported in my copy of Access2003. I looked in the VBA Help section under OpenRecordset and found the reference to the SQL type "source":

Set recordset = object.OpenRecordset (source, type, options, lockedits)

with source defined as:

A String (http://www.vbaexpress.com/forum/dadefstringdatatype.htm) specifying the source of the records for the new Recordset. The source can be a table name, a query name, or an SQL statement (http://www.vbaexpress.com/forum/dadefsqlstatement.htm) that returns records. For table-type Recordset objects in Microsoft Jet databases (http://www.vbaexpress.com/forum/dadefmicrosoftjetdatabase.htm), the source can only be a table name.

I think the last para tells the story. The db file suffix is .mdb

But (hopefully) I misunderstand something, so, assuming the target field name is NameID and the string I wish to match is stored in the variable strNameID, what would be the exact syntax?

Thanks

***************************
UPDATE: it appears that even though VBA Help says it should not work, I have gotten this method to work. I tried several forms of syntax before I poked around on the web some more and evolved:
strSQL = "SELECT * FROM Bus_card WHERE [NameID] = '" & strNameID & "';"
Set rst = db.OpenRecordset(strSQL) This approach solves the current problem perfectly.

Many thanks.

[geekgirlau]

When you're constructing SQL strings in code, there are a couple of things to watch for:

Personally if it's in the least bit complicated or with lots of joins, I like to build a normal select query and copy the SQL string into my code to get me started.
If you have any spaces in your field names, use the square bracket around the name as you've done in your example. I never have spaces personally, but have been known to forget about this in other databases!
If the value is a string, it needs quotation marks around it. You can sometimes get away with a single quote as in your example, but if there is the remotest possibility that there may be an apostrophe in the text (such as people or place names), use double-quotes. Of course then the syntax gets a whole lot more interesting!
If the value is a date, you need hash marks around the date and it has to be in mm/dd/yyyy format. This is regardless of your regional settings, because Microsoft want the whole world to use a US date format.
If the value is a number, it doesn't need any special treatment.I'm glad this worked for you!