Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-19-2014, 08:03 AM   #1
jp80
Newly Registered User
 
Join Date: Jun 2014
Posts: 11
Thanks: 5
Thanked 0 Times in 0 Posts
jp80 is on a distinguished road
Search button with text field

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!

jp80 is offline   Reply With Quote
Old 06-19-2014, 09:28 PM   #2
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

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.

Quote:
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 by ino_mart; 06-19-2014 at 09:34 PM.
ino_mart is offline   Reply With Quote
The Following User Says Thank You to ino_mart For This Useful Post:
jp80 (06-20-2014)
Old 06-20-2014, 12:53 AM   #3
jp80
Newly Registered User
 
Join Date: Jun 2014
Posts: 11
Thanks: 5
Thanked 0 Times in 0 Posts
jp80 is on a distinguished road
Re: Search button with text field

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?

jp80 is offline   Reply With Quote
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)
Old 06-20-2014, 02:43 AM   #5
jp80
Newly Registered User
 
Join Date: Jun 2014
Posts: 11
Thanks: 5
Thanked 0 Times in 0 Posts
jp80 is on a distinguished road
Re: Search button with text field

Thanks. Tried that and it worked. Happy days. :-)
jp80 is offline   Reply With Quote
Old 06-20-2014, 02:53 AM   #6
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

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).

ino_mart is offline   Reply With Quote
Reply

Tags
access , button , search , textbox , vba access

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Usinga single search button for mutiple text box hemolytucus Forms 4 02-24-2014 11:05 AM
Search Text Field and Button Bizzy211 Macros 12 07-28-2013 07:48 AM
Search button on a bound Form on a specific field mooredk Forms 5 03-21-2013 08:14 AM
Search field without button Misko Forms 3 02-13-2009 12:44 AM
Search button for specific field ritchiehicks General 5 03-03-2008 08:32 PM




All times are GMT -8. The time now is 10:36 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World