View Single Post
Old 06-20-2014, 02:40 AM   #4
ino_mart
Newly Registered User
 
Join Date: Oct 2009
Posts: 74
Thanks: 4
Thanked 7 Times in 6 Posts
ino_mart is on a distinguished road
Re: Search button with text field

JP80

Is the requested record already somewhere visible in the form? So if you use the record navigation buttons to browse manually through the records, do you actually find the record you search in the form? If not, my code will indeed not work.

Below an alternative solution. If no record is found, the main recordset is retrieved. You'll have to declare a public variable on top of the code and to add a line in the "form_open"-sub

Code:
Public strOriginalSQL as String
 
Private Sub Form_Load
strOriginalSQL = Me.RecordSource
End Sub
Private Sub cmdSearchButton_1_Click()
Dim rs As Recordset
Dim strSQL As String
'do not forget to replace table by the name of the table or view you use.
strSQL = "select * from [table] where [UniqueAEVRef]='" & Me.Searchfield1 & "'"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If Not rs.BOF And Not rs.EOF Then
 Set Me.Recordset = rs
Else
    MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
    Me.RecordSource = strOriginalSQL
End If
Me.Searchfield1 = Null
End Sub
ino_mart is offline   Reply With Quote
The Following User Says Thank You to ino_mart For This Useful Post:
jp80 (06-20-2014)