By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 1,967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

How to create Search form for database.

P: 50
Hello I would like to create a search form for my database that searches by:

First
Last
Contact Number
Street
City
State
Zip

What is the easiest way possible to accomplish this? I guess I should mention I have a table called tblcustomerinformation. I would like the search to list possible matches and from that list I would like to be able to click and open the record.

Thank you
Aug 22 '07 #1
Share this Question
Share on Google+
25 Replies


Stang02GT
Expert 100+
P: 1,208
Hello I would like to create a search form for my database that searches by:

First
Last
Contact Number
Street
City
State
Zip

What is the easiest way possible to accomplish this? I guess I should mention I have a table called tblcustomerinformation. Once the search is completed I will like it to open the matching record.

There are a couple different ways you can do this...

1.You can have text boxes where the users enter search criteria and then press a "search" button
2.You can have a combo box preform your search. The user can either type in what they want to search for or select from a list

I personally like the "search button" method. I am sure others memebers of TSDN will have other ideas but if you would like to do this let me know and I can help you out.


Have a good one!
Aug 22 '07 #2

P: 50
There are a couple different ways you can do this...

1.You can have text boxes where the users enter search criteria and then press a "search" button
2.You can have a combo box preform your search. The user can either type in what they want to search for or select from a list

I personally like the "search button" method. I am sure others memebers of TSDN will have other ideas but if you would like to do this let me know and I can help you out.


Have a good one!

Search Button is exactly what im looking for.
Aug 22 '07 #3

Stang02GT
Expert 100+
P: 1,208
Search Button is exactly what im looking for.

Ok well here is what you are going to need to do

Once you are have your form created with all the feilds you would like displayed, you are going to create a BOUND text box. (Bound means that it is connected with a feild in a table) in this example i will use LastName.

1.Once you have created your bound text box ( i can tell you how to do this if you don't know). You will wnat to create a blank button next to it.
2. Once you have created the button place this code in the "OnClick" event of the button. You are going to have to put the appropreate names in the corrisponding places

Expand|Select|Wrap|Line Numbers
  1. Private Sub ButtonsName_Click()
  2.    If IsNull(TextboxesName) = False Then
  3.       Me.Recordset.FindFirst '[FeildYourSearchingFor]=' & TextboxesName
  4.       Me!TextboxesName = Null
  5.       If Me.Recordset.NoMatch Then
  6.          MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
  7.             Me!TextboxesName = Null
  8.       End If
  9.    End If
  10. End Sub
This code will search, and if there is no result it will pop up a message telling the user there was not record found, and it will clear the txt box after the search is completed.
Aug 22 '07 #4

P: 50
Ok well here is what you are going to need to do

Once you are have your form created with all the feilds you would like displayed, you are going to create a BOUND text box. (Bound means that it is connected with a feild in a table) in this example i will use LastName.

1.Once you have created your bound text box ( i can tell you how to do this if you don't know). You will wnat to create a blank button next to it.
2. Once you have created the button place this code in the "OnClick" event of the button. You are going to have to put the appropreate names in the corrisponding places

Expand|Select|Wrap|Line Numbers
  1. Private Sub ButtonsName_Click()
  2.    If IsNull(TextboxesName) = False Then
  3.       Me.Recordset.FindFirst '[FeildYourSearchingFor]=' & TextboxesName
  4.       Me!TextboxesName = Null
  5.       If Me.Recordset.NoMatch Then
  6.          MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
  7.             Me!TextboxesName = Null
  8.       End If
  9.    End If
  10. End Sub
This code will search, and if there is no result it will pop up a message telling the user there was not record found, and it will clear the txt box after the searc is completed.
Do I need to create a new table or can I just create a new form and label the text boxes on that?
Aug 22 '07 #5

P: 50
How do I create a bound text box. I created a new text box on a form and it says unbound.
Aug 22 '07 #6

Stang02GT
Expert 100+
P: 1,208
To create a bound text box you are going to need to....

1. Right Click on the text box
2. Select Properties
3.Click on the Data tab
4. There should be a drop down with selections of the feilds in your table(if your form is bound to a table)

To bind a form to a table
1. In the properties menu for the Form do to the data tab
2.Click the drop down box and select the table you would like the form bound to
Aug 22 '07 #7

Stang02GT
Expert 100+
P: 1,208
If im not explaing things good enough here are a couple sites you can take a look at there are very good and provide a lot of detailed information about how to do things in Access

Search Form

How To Articles

More How To's
Aug 22 '07 #8

P: 50
I created a form and dragged the fields I wanted to the form. I create the command button and also pasted what you gave me on clickevent. I get errors when I click the button.

MS Access can't find the macro 'private Sub ButtonsName_Click() if IsNull(Contact Number)= False Then Me.
Aug 22 '07 #9

Stang02GT
Expert 100+
P: 1,208
I created a form and dragged the fields I wanted to the form. I create the command button and also pasted what you gave me on clickevent. I get errors when I click the button.

MS Access can't find the macro 'private Sub ButtonsName_Click() if IsNull(Contact Number)= False Then Me.
Did you name the button "ButtonsName"?

Access names it by default something like" Command_15"
Aug 22 '07 #10

P: 50
To name the button, In design view I right click on the button and then select the all tabe. There is a field there called name in the field I inputed "ButtonsName". Is that correct?
Aug 22 '07 #11

Stang02GT
Expert 100+
P: 1,208
Yes, but you can name the button whatever you'd like. Just make sure that whatever you name it to you change it in the code, because if you don't then Access doens't know where to look when you press the button
Aug 22 '07 #12

P: 50
Can you highlight for me what areas in this code i need to edit I may have edited it incorrectly. Here is what I did below:
Expand|Select|Wrap|Line Numbers
  1. Private Sub ButtonsName_Click()
  2.    If IsNull(Contact Number) = False Then
  3.       Me.Recordset.FindFirst '[Contact Number]=' & Contact Number
  4.       Me!Contact Number = Null
  5.       If Me.Recordset.NoMatch Then
  6.          MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
  7.             Me!Contact Number = Null
  8.       End If
  9.    End If
  10. End Sub
Aug 22 '07 #13

Stang02GT
Expert 100+
P: 1,208
Expand|Select|Wrap|Line Numbers
  1. Private Sub ButtonsName_Click()
  2.    If IsNull(Contact Number) = False Then
  3.       Me.Recordset.FindFirst '[Contact Number]=' & Contact Number
  4.       Me!Contact Number = Null
  5.       If Me.Recordset.NoMatch Then
  6.          MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
  7.             Me!Contact Number  = Null
  8.       End If
  9.    End If
Change the bolded names to something different, and make sure you don't put spaces.

Try something like ContactNum
Aug 22 '07 #14

P: 50
Expand|Select|Wrap|Line Numbers
  1. Private Sub ButtonsName_Click()
  2.    If IsNull(Contact Number) = False Then
  3.       Me.Recordset.FindFirst '[Contact Number]=' & Contact Number
  4.       Me!Contact Number = Null
  5.       If Me.Recordset.NoMatch Then
  6.          MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
  7.             Me!Contact Number  = Null
  8.       End If
  9.    End If
Change the bolded names to something different, and make sure you don't put spaces.

Try something like ContactNum

They bolding did not appear on your post.
Aug 22 '07 #15

Stang02GT
Expert 100+
P: 1,208
Can you highlight for me what areas in this code i need to edit I may have edited it incorrectly. Here is what I did below:
Expand|Select|Wrap|Line Numbers
  1. Private Sub ButtonsName_Click()
  2.    If IsNull(Contact Number) = False Then
  3.       Me.Recordset.FindFirst '[Contact Number]=' & Contact Number
  4.       Me!Contact Number = Null
  5.       If Me.Recordset.NoMatch Then
  6.          MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
  7.             Me!Contact Number  = Null
  8.       End If
  9.    End If
  10. End Sub

Is this anybetter?...........................
Aug 22 '07 #16

P: 50
So here is what my code looks like now:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Contactbut_Click()
  2.    If IsNull(Contactnum) = False Then
  3.       Me.Recordset.FindFirst '[Contact Number]=' & Contactnum
  4.       Me!Contactnum = Null
  5.       If Me.Recordset.NoMatch Then
  6.          MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
  7.             Me!Contactnum = Null
  8.       End If
  9.    End If
  10. End Sub
Aug 22 '07 #17

Stang02GT
Expert 100+
P: 1,208
Ok...did it work??????????
Aug 22 '07 #18

P: 50
No it didnt unfourtunately would you mind if I emailed my db to you? Or if there is a way for me to post it here or tranfer to you via IM.
Aug 22 '07 #19

Jim Doherty
Expert 100+
P: 897
No it didnt unfourtunately would you mind if I emailed my db to you? Or if there is a way for me to post it here or tranfer to you via IM.
Ali,

I have this afternoon created an AC2000 database for you that deals specifically with your introduced question. It is a flexible method of searching across any number of fields in combination with each other. If you use your imagination with the demo sample you will quickly realise that it can be adapted for many areas. The sample assumes you are familar with VBA code (and you eventually will be)
I have only just joined this forum this afternoon and cannot see an upload button? and to post the code in itself might be to intensiveif you PM me I,ll send you the database in zipped format ( and anyone else for that matter who might benefit as a developer)
Aug 22 '07 #20

Stang02GT
Expert 100+
P: 1,208
No it didnt unfourtunately would you mind if I emailed my db to you? Or if there is a way for me to post it here or tranfer to you via IM.

Sure PM me and i'll give you my e-mail address
Aug 22 '07 #21

Jim Doherty
Expert 100+
P: 897
Anyone who might benefit in reading this:

As requested attached is the Access 2000 Search demo database I refer to earlier in this thread. (a zip file so extract to anywhere on your hard drive) There a a couple of other goodies thrown in like exporting the resultset to excel and so on.

Take a look at the code behind the forms It works for version 2000 and 2003 (note however if version 2007 it will fail due to an apparent lack of response in that a subform will only show the first record albeit the recordsource of the subform is being set at runtime)

Regards

Jim
Attached Files
File Type: zip searchdemo.zip (196.3 KB, 7404 views)
Feb 11 '11 #22

sandyinfinite
P: 24
Very Nice creation and works in 2007 also
Thank you all who involving

Sandip
Feb 12 '11 #23

Jim Doherty
Expert 100+
P: 897
In response to a request I received recently I am reposting the file I contributed 3 years ago in 2011 it is in now in ACCDB format for those of you so wishing to learn from the techniques therein.

Basically ignore the previous MDB format and go with this ACCDB format rejigged for Access 2007 through to Access 2013

Regards

Jim Doherty
Attached Files
File Type: zip SearchDemo.zip (116.9 KB, 2983 views)
Mar 3 '15 #24

NeoPa
Expert Mod 15k+
P: 31,299
For anyone else wishing to ask questions related to anything included in this thread, but not directly related to the original question of the thread and by the original poster, these are hijacks if posted in this thread, and will be deleted, but are perfectly reasonable questions when posted in their own thread separately.

You can even post links to this thread from your new one AND, if you think it will help to be noticed by members already involved in this thread, you can also post in here with a link to your new question thread. All that you mustn't do is to ask your new question in here.
Mar 12 '15 #25

P: 4
I know this is an old thread, but THANK YOU for this. It is extremely helpful.
Jan 10 '19 #26

Post your reply

Sign in to post your reply or Sign up for a free account.