Search button with text field (1 Viewer)

jp80

Registered User.
Local time
Today, 11:51
Joined
Jun 19, 2014
Messages
11
Hi,

I'm fairly new to Access / VBA and have been trying relentlessly to get a text box / search button on my Access form to pull up a specific record. Although the null command produces the correct error msgBox, it does not show any record if I enter a correct primary key term. Probably something really simple, but I am completely stuck and cannot find the solution anywhere.

Search button = SearchButton1
Search criteria = SearchText1
Primary key field = UniqueAEVRef

Code:

Code:
Private Sub SearchButton1_Click()
If IsNull(SearchField1) = False Then
Me.Recordset.FindFirst "[UniqueAEVRef]='" & SearchField1 & "'"
Me!SearchField1 = Null
If Me.Recordset.NoMatch Then
MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
Me!SearchField1 = Null
End If
End If
End Sub

Please help!
 

ino_mart

Registered User.
Local time
Today, 04:51
Joined
Oct 7, 2009
Messages
78
Another solution on condition the records are already in the form's recordset (does not matter whether it is single view, continuous view, datasheet view) and you want to jump to a particular one.

Private Sub SearchButton1_Click()
dim rsTemp as Recordset

If IsNull(Me.SearchField1)=False Then
Set rsTemp = Me.RecordsetClone
rsTemp.FindFirst "[UniqueAEVRef] = '" & Me.SearchField1 & "'"
If Not rsTemp.NoMatch Then
Me.Bookmark = rsTemp.Bookmark
Else
MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
End If
Me.SearchField1=Null
End If
End Sub

One remark: code above assumes field UniqueAEVREF is defined in its table as a textfield. In case the field type is number, you must use next line
rsTemp.FindFirst "[UniqueAEVRef] = " & Me.SearchField1

If it is a datefield, it becomes
rsTemp.FindFirst "[UniqueAEVRef] = #" & Me.SearchField1 & "#"
 
Last edited:

jp80

Registered User.
Local time
Today, 11:51
Joined
Jun 19, 2014
Messages
11
I really appreciate your replies. The field UniqueAEVRef is indeed a textfield. I have tried both code suggestions and the search result still displays the No Record Found msgBox, even though the value entered exists in the table. Is there anything else that I could be missing? :banghead:
 

ino_mart

Registered User.
Local time
Today, 04:51
Joined
Oct 7, 2009
Messages
78
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
 

jp80

Registered User.
Local time
Today, 11:51
Joined
Jun 19, 2014
Messages
11
Thanks. Tried that and it worked. Happy days. :)
 

ino_mart

Registered User.
Local time
Today, 04:51
Joined
Oct 7, 2009
Messages
78
My pleasure. If it is indeed solved, please mark the thread as solved. You'll find this option in menu "Thread Tools" (see second menu bar which can be found above your question).
 

Users who are viewing this thread

Top Bottom