How to create Search form for database.

[ali3n8]

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

[Stang02GT]

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!

[ali3n8]

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.

[Stang02GT]

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

Private Sub ButtonsName_Click()
   If IsNull(TextboxesName) = False Then
      Me.Recordset.FindFirst '[FeildYourSearchingFor]=' & TextboxesName
      Me!TextboxesName = Null
      If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!TextboxesName = Null
      End If
   End If
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.

[ali3n8]

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

Private Sub ButtonsName_Click()
   If IsNull(TextboxesName) = False Then
      Me.Recordset.FindFirst '[FeildYourSearchingFor]=' & TextboxesName
      Me!TextboxesName = Null
      If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!TextboxesName = Null
      End If
   End If
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?

[ali3n8]

How do I create a bound text box. I created a new text box on a form and it says unbound.

[Stang02GT]

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

[Stang02GT]

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

[ali3n8]

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.

[Stang02GT]

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"

[ali3n8]

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?

[Stang02GT]

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

[ali3n8]

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:

Private Sub ButtonsName_Click()
   If IsNull(Contact Number) = False Then
      Me.Recordset.FindFirst '[Contact Number]=' & Contact Number
      Me!Contact Number = Null
      If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!Contact Number = Null
      End If
   End If
End Sub

[Stang02GT]

Private Sub ButtonsName_Click()
   If IsNull(Contact Number) = False Then
      Me.Recordset.FindFirst '[Contact Number]=' & Contact Number
      Me!Contact Number = Null
      If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!Contact Number  = Null
      End If
   End If

Change the bolded names to something different, and make sure you don't put spaces.

Try something like ContactNum

[ali3n8]

Private Sub ButtonsName_Click()
   If IsNull(Contact Number) = False Then
      Me.Recordset.FindFirst '[Contact Number]=' & Contact Number
      Me!Contact Number = Null
      If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!Contact Number  = Null
      End If
   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.

[Stang02GT]

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:

Private Sub ButtonsName_Click()
   If IsNull(Contact Number) = False Then
      Me.Recordset.FindFirst '[Contact Number]=' & Contact Number
      Me!Contact Number = Null
      If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!Contact Number  = Null
      End If
   End If
End Sub

Is this anybetter?...........................

[ali3n8]

So here is what my code looks like now:

Private Sub Contactbut_Click()
   If IsNull(Contactnum) = False Then
      Me.Recordset.FindFirst '[Contact Number]=' & Contactnum
      Me!Contactnum = Null
      If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!Contactnum = Null
      End If
   End If
End Sub

[Stang02GT]

Ok...did it work??????????

[ali3n8]

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.

[Jim Doherty]

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)

[Stang02GT]

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

[Jim Doherty]

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

[sandyinfinite]

Very Nice creation and works in 2007 also
Thank you all who involving

Sandip

[Jim Doherty]

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

[NeoPa]

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.

[Rhonda F]

I know this is an old thread, but THANK YOU for this. It is extremely helpful.