Very Complex Problem (Search Engines). Plz Help!!

[Only1Abhi]

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

 

[Darrylles]

Hi,

When user has entered their 'surname' and clicked 'Search',
you need to change the Record Source of the form to a sql query such as:

select * from tblCustomer where fldSurname = contents of surname_textbox

So, in your form Search_click button:
-------------------------------------------------------
dim SQLString as text

SQLString = "select * from tblCustomer where fldSurname = "& txtSurname

me.recordsource = SQLString 'Change form data query.
me.requery 'Refresh the form data.
-------------------------------------------------------

Does this help?

Regards,

Darrylle


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Only1Abhi]

Thanks Darrylles,

I really appreciate it.
But it didn't work
Do u know another simple solution to this problem (anything without/little VB coding)?

If u do, plz post it. Thanks.
Any1else with solution also plz post it.
Thanks in advance to others and thanks again to Darrylles.

(Plz forgive me tone of speaking... right now my hands have frozen because they are soooo cold so I can't type that much )

regards,
Abhi

 

[Only1Abhi]

Problem solved...... nearly!

Just had a little problem on this.

I have a form called frmSearch with an option group with options:

CustomerID
Forename
Surname
Gender
HomeTelNo
WorkTelNo
MobileNo

I also have in the form:
A search box - to enter the search criteria
2 buttons - (Search button & the clear button)
ListBox - to display the search results
Label - To say how many results there are.
CheckBox - to allow exact matches.

When I select an option, then I can enter something in the SearchBox and this should show results in the listbox (called SearchResults).

I also have a check box called ChkExactMatch.
However, I am not getting exact match results. Here is the code I entered in the Search Button's On_Click Command.

what could be the problem?
Many thanks in advance for the help.
Regards,
Abhi



Private Sub SearchButton_Click()
If SearchBox = vbNullString Then
MsgBox "You have not entered any filter criteria.", vbExclamation, "Invalid Search"
Exit Sub
End If
With SearchResults
Select Case Options
Case Is = 1
.RowSource = "SELECT * FROM tblCustomer WHERE [CustomerID] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';&quot
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = -1, 0, SearchResults.ListCount - 1)

Case Is = 2
.RowSource = "SELECT * FROM tblCustomer WHERE [Forename] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';&quot
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = -1, 0, SearchResults.ListCount - 1)

Case Is = 3
.RowSource = "SELECT * FROM tblCustomer WHERE [Surname] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';&quot
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = -1, 0, SearchResults.ListCount - 1)

Case Is = 4
.RowSource = "SELECT * FROM tblCustomer WHERE [Gender] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';&quot
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = -1, 0, SearchResults.ListCount - 1)

Case Is = 5
.RowSource = "SELECT * FROM tblCustomer WHERE [HomeTelNo] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';&quot
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = 0, 0, SearchResults.ListCount - 1)

Case Is = 6
.RowSource = "SELECT * FROM tblCustomer WHERE [WorkTelNo] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';&quot
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = 0, 0, SearchResults.ListCount - 1)

Case Is = 7
.RowSource = "SELECT * FROM tblCustomer WHERE [MobileNo] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';&quot
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = 0, 0, SearchResults.ListCount - 1)
End Select
End With
End Sub

 

[Only1Abhi]

can any1 plz help?

 

[Only1Abhi]

PROBLEM SOLVED...

many thanks anyway ppl.

 

[at51178]

I just created as search engine myself in my form this is the way I did it

in the form you have to create an unbound textbox we will name it
"txtsearch"

then create a search button we will name it
"cmdSearch"

then we will create a second textbox we will name it
"txtresult"
this second textbox will be bound to the information you are looking for
so in tblcustomer if you have a column called name then make the second textbox bound to the name column in tblcustomer

you then go to the onClick portion of the cmdsearch button
and you will type this code in the onclick event of the cmdsearch button.

Me.RecordSource = "SELECT * " & "FROM tblcustomer " & "WHERE tblcustomer.Name LIKE '*" & txtname & "*';"




just copy and paste exactly this code and make any changes as you would like
in trying to make you form like a webpage then you would have to just format the textboxes and the whole form like a webpage.
let me know if this helps in any way

 

[at51178]

oh ya I forgot this code can only work with textboxes if you need one for list boxes or comboboxes let me know

 

[Only1Abhi]

many thanks at,
I've got a solution very similar to yours.

I have a textbox called:
SearchBox

I have a button called:
SearchButton

I have a listbox called:
SearchResults

I have an option Group called:
OptionGroup (or Options)

And using the exact method u mentioned above, I have created a website like search facility.

If I had found your solution at first, I would have defiently used it.

Many thanks anyway at.
I really appreciate it that u posted something even after I said I've solved my problem, just so that I could get an easier solution.
Thanks Again,
Best Regards,
Abhi

 

[at51178]

No problem hope you enjoy it

 

[stickers]

Hi at51178

I am pulling hair out trying to make a search function like this work. Here's the prob:

listbox lstGeneric with 4 columns, only 2 of which are visible:

ItemID (invisible) ItemName (Vis), GenCost (Vis), Category (invis).

Next to them are a combo box and a text box, both unbound. The combo box contains categories - cboCategory. Text box is txtSearch. Button: cmdSearch.

I want to be able to search either on a string typed into the text box, or on the combo box value.

I've tried three ways to do this, and whilst it works fine for whatever is in the combo, I can't get it to recognise the text box contents. Here's my SQL:

SELECT tblGeneric.ItemID, tblGeneric.ItemName, tblGeneric.GenCost AS [Generic Cost]
FROM tblGeneric
WHERE (((tblGeneric.ItemName) Like "* [Forms]![frmRoomSchedBuild]![txtSearch]*&quot) OR (((tblGeneric.Category)=[Forms]![frmRoomSchedBuild]![cboCategory]));

I've tried putting this as the row source of the list box - right into the list box, and as code behind the button i.e.

Me!lstGeneric.Rowsource = SQL above

AND IT JUST WON'T WORK!!!!!

I'm sure I'm missing something really simple. I know about using single apostrophes in VB and all that stuff...

 

[at51178]

Just to let you know I will be working on your problem later on in the day.
But from what I understand
just tell the computer that if isnull(txtsearch) then
(write your statement)
else if isnull(cbocategory) then
(write your statement)
end if


this way if your txtsearch is null it will look in the combo box and vis versa.

You can even go a step further and say onchange for txtsearch cbocategory is null and vis versa.
that way you don't accidentally have the user have something in the txtseach and some thing in the cbocategory.

 

[stickers]

Hey thanks!! I'll look forward to hearing from you.
I have already set the on enter events of both to nullify each other if you see what I mean. I'll change those to 'on change' - good idea. But even if I just open the form straight up and type something into txtSearch it doesn't find it.

The Row Source thing in VB I've played with more, and even if I take out the criteria that returns nothing (i.e. doing it all as code behind cmdSearch). However, using the property box in design view of the form, seems ok, and works if I put in "*AnythingElse*" against the ItemName, it's fine. Just when I try to enter txtSearch. So I guess it's something to do with how I'm putting that in.

You will get a really big fat purple star if you can give me a hand with this one!!!

I'm about to finish up for the weekend - have a good one.

 

[stickers]

One more post before I go (this is really doing my head in...)
What it doesn't like is the wildcards (*) next to the control name (txtSearch). I've just tried
Like Forms! etc.!txtSearch

and that works OK, but obviously only pulls up records if you type in the exact string. Because my users might be typing in, say, chair, and get a load of different chairs, I want them to be able to get that by typing in just 'cha' or something. In case they are unable to spell correctly...! OK chair isn't the best example, but you get the idea.

I was going to try and cover for spelling mistakes too, but think I'll leave that stuff to the experts....

 

[at51178]

Can you send me a copy of your database at
at51178@hotmail.com

that way I could check it out

 

[at51178]

I think I got it

Here it is

Step 1
I created a table tblGeneric with four fields
ItemID,ItemName,GotCost,Category

Step 2
I created a query with the following sql statement
SELECT DISTINCTROW [tblGeneric].[ItemID], [tblGeneric].[ItemName], [tblGeneric].[GenCost], [tblGeneric].[Category]
FROM tblGeneric
WHERE ((([tblGeneric].[ItemName]) Like [Forms]![frmRoomSchedBuild]![txtSearch] & "*&quot)
ORDER BY [tblGeneric.ItemName];


Step 3
I created a form name called frmRoomSchedulBuild
in the form create
two textboxes (txtsearch,txtresult)
one combobox (cboCategory)
one commandbox (cmdSearch)
one listbox (lstGeneric)

In the listbox in properties under format you type 4 for column count ; Yes for column Head ;This should take care of the problem you had about not being able to see the columns it was because you have to format it so that you can see the columns.

Now go to the code of the form and copy and paste the following if you named everything the way I told it should work.

Option Compare Database
Option Explicit

Private Sub cboCategory_AfterUpdate()
Me.txtResult = Me.cboCategory
End Sub

Private Sub cmdSearch_Click()
Me.RecordSource = "SELECT * " & "FROM tblGeneric " & "WHERE tblGeneric.ItemName LIKE '*" & txtSearch & "*';"
txtResult.SetFocus
txtSearch.SetFocus
End Sub

Private Sub lstGeneric_AfterUpdate()
Me.RecordSource = "SELECT DISTINCTROW tblGeneric.ItemID, tblGeneric.ItemName, tblGeneric.GenCost FROM tblGeneric WHERE ((tblGeneric.ItemName like [forms].[frmRoomSchedBuild].[txtSearch]));"
lstGeneric = Null
End Sub
Private Sub txtResult_GotFocus()
Me.txtSearch = Me.txtResult
End Sub

Private Sub txtSearch_AfterUpdate()
If txtSearch <> &quot;&quot; Then lstGeneric.RowSource = &quot;qryGeneric&quot;
End Sub

Private Sub txtSearch_GotFocus()
If txtSearch <> &quot;&quot; Then lstGeneric.RowSource = &quot;qryGeneric&quot;
End Sub






According to your email what you wanted to do was to be able to look at a value in the list box by typing in all or part of the value . You also want to give the user the option to click on the dropdown box and have it search for what you choose in the combo box.

The way I went around this problem was to create another textbox called txtresult what happens is when you make a choice in the combo box what ever the choice is in the combobox equals to what ever the choice is in the txtresult box and when the txtresult box is populated it will equal the txtsearch to the txtresult which will populate the listbox with what you were looking for. You can also then type any part of the text or all of the text in txtsearch to search for a record. basically I am taking your choice in the combobox and rerouting it so it goes to teh txtsearch textbox.

You can just format the txtresult textbox so that it is hidden behind the listbox so nobody can see it.

Please let me know if this is what you are looking for.

 

[stickers]

Hi both,

thank you for your answers - I'll be giving all of this a go later today and let you know how I got on!

Stickers

 

[stickers]

It's working! Thank you for the help!

In the end, actually I simplified it all somewhat and used the idea of a second hidden text box, and just set the before update of txtSearch to

txtResult = &quot;*&quot; & txtSearch.Value & &quot;*&quot;

Then set the SQL behind the list box to look for txtResult instead of txtSearch.

But I wouldn't have got there without you, so many many thanks!!

Stickers xxxx

 

[at51178]

No problem
always here to help