Searching for a record in a recordset (1 Viewer)

[Tammy]

HELP WANTED!!! I am trying to set up a search method that uses the recordset findfirst command. After I execute the RS.FindFirst I have placed in my code an IF condition which checks the RS.NoMatch Property which if NoMatch is true the code is suppose to display a message box saying that the record does not exist and closes the current form and returns to the main form however when I execute the code it seems to loop through the NoMatch IF-statement 3 times, displaying the message box 3 times before returning to the main form! Any suggestions on how to fix this problem would be greatly appreciated or if I am off track and someone can show me the way I would forever be greatful... thanks.

 

[bobjames]

If I were to search for a record in code, I would prefer not to use the FINDFIRST method. It’s slow, cause it doesn’t use indexes. Your alternative is to use the SEEK or use an SQL statement. The SEEK is fast but if the table is not local, then it’s more difficult to code and if it’s not an MS Access table then it can’t be used at all. An SQL statement, on the other hand is easy to code and be easily used against local or linked tables. Here’s how you would do it

Dim SQLtxt as string
Dim RS as recordset

'Create your SQL statement using the query
' grid then copy to you cod and edit it

SQLtxt = “Select Table1.* FROM Table1 WHERE Field1 = “ & FormFieldTxt

Set RS = CurrentDb.OpenRecordset(SQLtxt)

'if its’ the beginning and the end of
'the recordset,that means there was no
'match forFormFieldTxt

If RS.EOF and RS.BOF then

MsgBox “There is No Match for “ & _ FormFieldtxt
Else
MsgBox “Yes, There is No Match for “ & _
FormFieldtxt
RS.movefirst

'now you can put the data from the
' Recordset in to the form

End if


[This message has been edited by bobjames (edited 05-22-2000).]

[This message has been edited by bobjames (edited 05-22-2000).]

[This message has been edited by bobjames (edited 05-22-2000).]

 

[KMAN]

why not post your code?

 

[bobjames]

the code is up there. it starts with the Dim statement