Open a table for search

[chas47]

Using VBA I want to open a table. Then move to first record, then check the
data in [name field] and compare it to the data the user has typed into a
text box on a form. When it finds a match or the end of file - use a message
to inform the user of results. The crude code below will give an idea of
what I am trying to do. I have been away from Access and VBA four about
seven years.

Open table/recordset
Move first
Do until EOF
Read [name field]
If [name field] = Me.[text box] then
Message “You’ve hit pay dirtâ€
endsub
EndIf
Move Next
Loop
Message “Search failedâ€

 

[vanderghast]

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tableName WHERE
fieldName = """ & Nz(Me.TextBox, "NULL" ) & """"" )
If rst.RecordCount <> 0 Then
MsgBox "You've hit pay dirt"
Else
MsgBox "Search failed"
End If
rst.Close



which is a little bit different than what you asked, since it opens a
recordset based on the criteria you supplied, and not the whole table. It
also handles the case where the textbox holds nothing (by making the search
fails). If you don't need the other fields, of the record, you can even
simplify more


if 0 <> DCount("*", "tableName",
"fieldName=FORMS!FormNameHere!TextBoxName" ) then
MsgBox "You've hit pay dirt"
Else
MsgBox "Search failed"
End If



where you don't open (explicitly) anything, just querying the database for a
single value, here, the number of records matching the criteria, in the said
table. The word FORMS is a key word to be typed as it is. Other names
(tableName, FormNameHere, TextBoxName) have to be modified to suit your
design.






Vanderghast, Access MVP

 

[John W. Vinson]

Using VBA I want to open a table. Then move to first record, then check the
data in [name field] and compare it to the data the user has typed into a
text box on a form. When it finds a match or the end of file - use a message
to inform the user of results. The crude code below will give an idea of
what I am trying to do. I have been away from Access and VBA four about
seven years.

Open table/recordset
Move first
Do until EOF
Read [name field]
If [name field] = Me.[text box] then
Message “You’ve hit pay dirt”
endsub
EndIf
Move Next
Loop
Message “Search failed”

You're using a database: use it as a database!!!!

It is NOT necessary to open the table, or create a recordset, or loop, or ANY
of this stuff. Access contains the tools (queries) to do what you want far
more efficiently, probably with *no code at all*.

If you base a Form on a Query uing a criterion on [name field] of

=Forms![NameOfYourForm]![NameOfTheTextbox]

and open the form in the afterupdate event of the (unbound) textbox, you'll
see all the matches.