Search Facility in Forms

Only1Abhi

Only1Abhi

World Filled With Love
Local time
Today, 21:35
Joined
Jan 26, 2003
Messages
99
Very Complex Problem (Search Facility in Forms). Plz Help!!!

Hi.
I tried to get my head around this but it simply wouldn't work.

My Table: "tblCustomer"
My Form: "frmCustomer"

In Form View, I want to include a search engine facility. It needs to be designed in such a way that I type a surname of a person (in a text box) that is already in the table and all the people with that surname should appear on the form for me to flick through. Does any1 know how to do this?

Thanks a lot in advance.

But those people who think this is a piece of cake, this isn't the whole problem. Infact, I was thinking of some way where I could get the search results to appear in a box like in a website. (I.E. When I type a typical surname such as Smith in the text box, I want all the Smiths to appear in a box like a list along with their first names so that I can just click on one of them and view their details).

Does any1 have a solution to the first and/or second part of my problem? If so, plz post it here because I've been asking all programmers I know like mad and know1 seems to know the answer to this.

Thanks a lot in advance... any1 who solve the 2nd problem is a true genius!!!

Waiting urgently for replies.
Regards,
Abhi
:)
 
Personally, I'd use a listbox on the form to list all of the surnames once the filter is run, especialy if you wish to list other details too.

On the form try creating a listbox and call it lstResults.

You put your textbox that you wish to use for filtering: txtFilter

Also, put a command button on the form: cmdSearch

On cmdSearch's OnClick event, you could put this code:

Code: 
Private Sub cmdSearch_Click()
   If IsNull(Me.txtFilter) Then
      MsgBox "You have not entered any filter criteria.", vbExclamation, "Title"
      Exit Sub
   End If
   With lstResults
      .RowSource = "SELECT * FROM tbCustomer WHERE [[i]YourSearchField[/i]] Like '*" & Me.txtFilter & "*';"
      .ReQuery
   End With
End Sub

Hopefully, everyime you type something into the textbox and click the button it will fill the listbox with all search results. What you will want to do next is be able to select a certain customer from the list and edit their details.

So, on the listbox's AfterUpdate event, put this bit of code:

Code: 
Private Sub lstResults_AfterUpdate()
   DoCmd.OpenForm "[i]Your form name[/i]", acNormal, your criteria**
End Sub

** The criteria for the form will most likely be any that equal the customer's unique ID. As the SQL row source from the click statement was designed to accept all fields in the tblCustomer your ID will most likely be the first column of the listbox, therefore you'l criteria will be along the lines of "[Customer ID] = " & lstResults.Column(0)

Hope this helps,
 
Last edited:
thanks a lot Mile-O-Phile

I tried that but it didn't work

Do u know any other solution without too much VB coding?

Thanks a lot again for your help.


Any1else got a solution to my problem? Plz post it.

Thanks a lot in advance.

Regards,
Abhi
:):D
 
You said..

I tried that but it didn't work
Click to expand...

Knowing the track record of the person who posted that reply (mile-o-phile) I'm guessing that it will work, you just have mis-translated one of the directions from the post.
A better response would be to reply back and inform us where the error is in the suggested code.

Here's another tip.. it is bad policy to reply back and ask for other solutions if you have not thoroughly explored the solution previously suggested. There are some brilliant minds behind this forum and burning bridges is not something you want to do if you are not one of them. :)

Now that I'm done with that rant..
I am interested as to what part of Mile's solution is not working. Looking at the code, it looks as if it should work.

Let us know..
 
Just out of interest, have you copied the code verbatim?

If you have, you'll need to rename any control that's mentioned to the specific names of your form. i.e

cmdSearch = the name of your command button that will trigger the search;
txtFilter = the name of your textbox where users type their criteria;
lstResults = the name of your listbox where the results will be shown;

[YourSearchField] = the actual name of the field you are searching on with the criteria specified in txtFilter;

"Your Form Name" = the name of the form you will open up to show the details as obtained from the search


I'm surprised to find I've got a track record!!! Thanks, Sambo! :cool:
 
thanks a lot for the suggesstions sambo.
I'm kinda new here so I don't know the rules.
My apologies to Mile-O-Phile aswell.

I'm pretty sure I entered it correct but I'll give it another go.

I'll reply soon.

regards,
Abhi
 
Abhi, was there a specific part that was highlighted (i.e error) or did nothing happen whatsoever?

What version of Access are you using?

If you are using 2000/XP I can knock up a quick example and post it here.
 
If you're new to Access and VB I would suggest doing just one thing at a time.
First, get the controls set up on the form.
Then, get the button working to populate the list box.
Last, work on opening the form w/ filter.
 
Here's a 2000 Database that I made to show how it's done. I adapted it a little to allow for wildcard or exact match searches.

Hope this helps
 

Attachments

Has anyone that has downloaded my .zip above had any problems with it?
 
thanks a lot Mile-O-Phile.

you program works fine.

I just have to adapt it to my project.

I will reply soon telling u which part was highlighted.

thanks,

regards,

Abhi!
 
hi Mile-O-Phile

thanks for your example.
It's works fine.

Even my program works fine now.

I just need to know...

if i enter something in txtFilter and get the list in lstResults,

how can i clear all this information and start Again?
Is this done using a button?

And 1 more thing plz...

Originally in the form, I added text boxes and labels showing the [Customer ID], Surname and Forename (using the form wizard). I have still kept them and they are still in my form with the txtFilter, cmdSearch and lstResults. When I enter something into txtFilter and the updated list comes, is there any way I can click on a certain person's name and have their Customer ID, Surname and Forename appear in the textboxes?


Plz tell me if u don't understand and i'll rephrase it.

Many Thanks!!!
:)
 
To answer you first question you could add a new command button cmdClear and put a small bit of code behind it:

Code: 
txtFilter = vbNullstring
lstResults.RowSource = ""


And for your second question - if you've selected your fields into the listbox, then you should know which column each piece of information is, so

txtCustomerID = lstResults.Column(0)

etc.
 
many thanks again Mile-O-Phile

1 last favour plz.

The orginal code you made for the cmdSearch,

Can u plz tell me what each bit of code means?

I need to understand the code aswell when implementing the codes.

Thanks a lot bro.
Many thanks.

Regards,
Abhi
:) :)
 
Code: 
Private Sub cmdSearch_Click()

    ' check if txtFilter is a null value
    If IsNull(Me.txtFilter) Then
        MsgBox "You have not entered any filter criteria.", vbExclamation, "Filter Example"
        Exit Sub
    End If

    With lstResults
        ' using this listbox we change its RowSouce (where it gets its information from)
        .RowSource = "SELECT * FROM tblCustomers WHERE [Surname] Like '" & _
            ' using the Like statement we are looking for anything similar to what  is typed in the search box
            ' the IIf statement is used to check a condition, in this case (detail the criteria, what to do if the criteria is TRUE, what to do if the criteria is FALSE) - in this case the criteria is if chkExactMatch is true then txtFilter and ; becomes the end of the statement otherwise the wildcards are used on either side of txtFilter to return a result
            IIf(chkExactMatch = True, Me.txtFilter & "';", "*" & Me.txtFilter & "*';")
        .Requery ' basically update the listbox with its new information
    End With
    
    ' As the listbox is using column headings (which count) we need to eliminate 1 from the total of entries in the list. This statement checks that if the count goes below zero then the count is zero, otherwise the count is as it is
    lblResults.Caption = "Filter Results: " & IIf(lstResults.ListCount - 1 = -1, 0, lstResults.ListCount - 1)
    

End Sub

If you need more information then just say so...
 
as always Mile-O-Phile,

you're a genius!!! :cool:

a few more thing plz.

what does the ";" symbol mean and do?
you used it in the IIf statement...

what is the difference between If and IIF bro?

can u plz explain the ".RowSource = SELECT" thing again plz?
what does that SELECT "*" thing do?
and what is that thing " ' " (apostrophe) next to LIKE do?

I also tried the cmdClear command to clear the list. Works fine but the other 1 doesn't (clicking a record in the list and getting the person's forename, surname and customerID).

This is what I typed for the command:


Private Sub SearchResults_Click()
CustomerID = SearchResults.Column(0)
Forename = SearchResults.Column(1)
Surname = SearchResults.Column(2)

End Sub


Where have I gone wrong? The Forename and Surname fields seems to be working. It's the CustomerID field that's a problem!

lstResults = SearchResults
cmdSearch = SearchButton
cmdClear = SearchClear


Thanks a lot again bro. Many thanks!
 
is it because my CustomerID field is an Autonumber?

How do I get around this problem?

and I found 1 more problem

When I click on the required record from the list, although I view the surname and forename in the textboxes,

In the actual table tblCustomer, the names get mixed up, e.g. surname is forename and forename is surname for the ones I clicked on in lstResults (SearchResults).

What can I do?

Many Thanks!
:D
 
P.S. I just LUV your chosen picture !!!!
It has a bit of spokiness to it! :eek:

What made u chose that pic and where was it from?

I also like your slogan "The Hat wears the man"

what's that meant to mean?

I like the whole SPOKINESS in both pic and slogan. I guess an unique identifier for Mile-O-Phile!!!
:)
 
Okay, let's tackle these one by one:

The use of the semi-colon (;) is to denote the end of an SQL (Structured Query Language) statement which the row source of the listbox uses to provide the user with a list of data.

The difference between the IF and IIf statement is that the IIf is akin to the IF statement in Excel whereby you present a condition relating to a control/variable/property and change the value according to whether the expression you are evaluating is true or false. The IF statement allows you to evaluate whether a condition meets a specific criteria.

The .RowSource is a property of the listbox which we are applying a value to. In the code we are creating a query with which to apply to the listbox.

SELECT * FROM means select all fields from the relevant query or table.

As we are evaluating the value of a text string within the textbox the apostrophe (') is used on either side of the value to denote that it is a string. i.e Like 'Smith'

As for the other questions, I can't remember the demo I made and my laptop is at home for the moment but I'll have a look later on.

On a lighter note, with respect to my avatar.

The picture is a scaled down image of the singer of a German band called Einstürzende Neubauten.

I have no idea why I chose "The hat wears the man": maybe it's a more humane look akin to "the tail wags the dog" whereby what one wears can be an expression and identifier of who they are, or aren't.
 
Last edited:
Thanks a lot bro.

Great help.

Just waiting for the customerID problem to be fixed (along with the problem of data being stored permenantly when I click on a record in the listbox).


Thanks again!

Was that pic & text intended to be spooky?
 
You must log in or register to reply here.

Similar threads

A
Solved Form Combo Box Doesn’t Store Selected Value (Foreign Key Issue)
2
Replies
20
Views
492
GPGeorge
GPGeorge
C
Majp Search and Filter
Replies
7
Views
410
MajP
MajP
H
Problem with Search Boxes
2
Replies
24
Views
1,398
Pat Hartman
P
A
Search box to find parts to add to job instead of a combo box
2 3
Replies
41
Views
771
Gasman
Gasman
W
After reassign link to new form in the navigation subform of the navigation button, error message "The form is not exist"
Replies
8
Views
119
wendywu
W

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom