Search Button code

Create search button on form to search entire database upon request

Page 1 of 1

9 Replies - 37426 Views - Last Post: 22 February 2012 - 12:02 PM Rate Topic: -----

#1 knockkneed81  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 52
  • Joined: 05-November 08

Search Button code

Post icon  Posted 13 February 2009 - 09:08 AM

Hello,

I am at work and have been tasked with creating an Access Database to track inventory, which I have sucessfully completed along with the form. the person who has tasked me with this project now wants me to create a search textbox and button that will search the entire database when a request is entered into the textbox and submitted using the search button. I believe that it will require some code behind the search button to properly find and populate the requested record(s) into the form. I will be the first to tell you that I don't know much about code. Am I approaching this the best way? Is there a site I could reference .

Any help will be appreciated.

Thanks in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: Search Button code

#2 jjsaw5  Icon User is offline

  • I must break you
  • member icon

Reputation: 90
  • View blog
  • Posts: 3,060
  • Joined: 04-January 08

Re: Search Button code

Posted 13 February 2009 - 09:24 AM

I actually wrote a tutorial for something like this.

Check this out it might be able to help you.


http://www.dreaminco...wtopic45849.htm
Was This Post Helpful? 0
  • +
  • -

#3 knockkneed81  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 52
  • Joined: 05-November 08

Re: Search Button code

Posted 13 February 2009 - 09:56 AM

View Postjjsaw5, on 13 Feb, 2009 - 08:24 AM, said:

I actually wrote a tutorial for something like this.

Check this out it might be able to help you.


http://www.dreaminco...wtopic45849.htm


I actually went this website prior to my original post and I got the following message in the attachment. I need to be able to search all fields in the database table

Attached File(s)


Was This Post Helpful? 0
  • +
  • -

#4 knockkneed81  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 52
  • Joined: 05-November 08

Re: Search Button code

Posted 13 February 2009 - 11:25 AM

Could I use macros instead?
Was This Post Helpful? 0
  • +
  • -

#5 smcherniss  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 7
  • View blog
  • Posts: 8
  • Joined: 25-June 08

Re: Search Button code

Posted 13 February 2009 - 11:28 AM

[DataBase Field] is the name of the field in the Table you are trying to retrieve records for. Example if I had a Table named "Employee" with the following Fields:

"First Name"
"Last Name"
"Phone Number"

and I wanted to search on the Last Name field I would do this

Private Sub cmdSearch_Click()
   If IsNull(txtLastName.value) = False Then
	  Me.Recordset.FindFirst "[Last Name]=" & txtLastName.value
	  
	  If Me.Recordset.NoMatch Then
		 MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
	   Else
		  What ever you need to do with the recordset
	   End If
End Sub



Hope this helps

Skip
Was This Post Helpful? 0
  • +
  • -

#6 knockkneed81  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 52
  • Joined: 05-November 08

Re: Search Button code

Posted 13 February 2009 - 12:29 PM

View Postsmcherniss, on 13 Feb, 2009 - 10:28 AM, said:

[DataBase Field] is the name of the field in the Table you are trying to retrieve records for. Example if I had a Table named "Employee" with the following Fields:

"First Name"
"Last Name"
"Phone Number"

and I wanted to search on the Last Name field I would do this

Private Sub cmdSearch_Click()
   If IsNull(txtLastName.value) = False Then
	  Me.Recordset.FindFirst "[Last Name]=" & txtLastName.value
	  
	  If Me.Recordset.NoMatch Then
		 MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
	   Else
		  What ever you need to do with the recordset
	   End If
End Sub



Hope this helps

Skip



What if you want to search all of the fields? Is there a way to say search all.
Was This Post Helpful? 0
  • +
  • -

#7 smcherniss  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 7
  • View blog
  • Posts: 8
  • Joined: 25-June 08

Re: Search Button code

Posted 17 February 2009 - 10:22 AM

If you desire to use more than 1 criteria field in your search, I would recommend constructing a SQL Statement Query and executing it against the desired Table.

Here is an example.


	Dim dbMyDb As Database
	Dim rsMyTable As Recordset
	Dim stSQLStatement As String

	' Establish a connection to the 'Choose Your Table' Table and retrieve
	' the recordset

	Set dbMyDb = CurrentDb()
  ' Construct a SQL Statement to retrieve the desired records
  stSQLStatement = _
		"SELECT [Field 1]," & _
			   "[Field 2]," & _
			   "[Field 3] " & _
		"FROM [Choose Your Table] " & _
		"WHERE [Field 1] = " & stField1Criteria & _
		"OR [Field 1] = " & stField2Criteria & ";"

	' Execute the SQL Statement and retrieve the records  
	Set rsMyTable = dbMyDb.OpenRecordset(stSQLStatement, , dbSeeChanges)

' Loop through all records in the recordset
While Not rsBalancing.EOF

	' Do What you gotta do	
	
	' How to access a field for the current record
	' rsMyTable![Field 1]

	rsBalancing.MoveNext
	
Wend




What if you want to search all of the fields? Is there a way to say search all.
[/quote]
Was This Post Helpful? 1
  • +
  • -

#8 Mykomushu  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 14-April 09

Re: Search Button code

Posted 18 May 2009 - 02:05 PM

jjsaw5,

First of all, just want to say thanks a ton for this code.

View Postjjsaw5, on 13 Feb, 2009 - 11:24 AM, said:

I actually wrote a tutorial for something like this.

Check this out it might be able to help you.


http://www.dreaminco...wtopic45849.htm


I went through your code and tried to replicate it however am running into a data mismatch error.

Below is my code:

Private Sub cmdSearch_Click()
	
	If IsNull(txtAcct) Then
	MsgBox "Please Enter An Account Number"
	End If
	
	If IsNull(txtAcct) = False Then
		Me.Recordset.FindFirst "[ACCT_NO]=" & txtAcct
		Me!txtAcct = Null
		If Me.Recordset.NoMatch Then
			MsgBox "Account Number Not Found", vbOKOnly + vbInformation, "Sorry"
			Me!txtAcct = Null
		End If
	End If
	   
End Sub


When I type any known value (that I know should retrieve a record) in to the search txt box, it's returning a "Data type mismatch in criteria expression" runtime error 3464.

I have checked the table and the field is a text field (although it used to be number and I changed it to text). I've also checked the form to make sure that there is no formatting in the properties.

I've also tried changing the code to:

[ACCT_NO] = txtAcct

However this always returns a msg of "Account number not found" which I'm assuming is also due to some mismatch.

I'm not sure where else the data type mismatch is coming from.

Thanks.

This post has been edited by Mykomushu: 18 May 2009 - 03:28 PM

Was This Post Helpful? 0
  • +
  • -

#9 Mykomushu  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 14-April 09

Re: Search Button code

Posted 22 May 2009 - 11:00 AM

I did some more research and figured it out. My final code is:

Private Sub cmdSearch_Click()
	
	If IsNull(txtAcct) Then
	MsgBox "Please Enter An Account Number"
	End If
	
	If IsNull(Me.txtAcct) = False Then
		Me.txtAcct.SetFocus
		Me.Recordset.FindFirst "ACCT_NO= '" & Me.txtAcct.Text & "'"
		Me!txtAcct = Null
		If Me.Recordset.NoMatch Then
			MsgBox "Account Number Not Found", vbOKOnly + vbInformation, "Sorry"
			Me!txtAcct = Null
		End If
	End If
	   
End Sub



My error was:

1. Not setting focus
2. Putting the right quotes around my text field

Hope this helps someone else~!
Was This Post Helpful? 0
  • +
  • -

#10 KatMan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 22-February 12

Re: Search Button code

Posted 22 February 2012 - 12:02 PM

I'm sorry if this is a dumb question, but how do you specify a file you want to search? And can it be a spreadsheet or a text file? And if so, how do you set up the spreadsheet/text file to be a "data tabe" or whatever?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1