Partial Search on a Form

[mariannesooz]

Hi!

I'm having a problem in the database I'm working in.
I've tried a lot of things like code and queries, but there's always something
missing or something wrong!
I need your help to find out the better way to do this:

I have a table "INFOTable" where I have some fields. One of those fields is "CompleteName". I also have a form "MainForm" where I do search by Combo Box some of the fields of "INFOTable", and it is working good! My problem is that I have a Text Box "txtCompleteName" in "MainForm" where I want users to insert one or more words(names) and then search by a button called "cmdSearch2". I want also this button to open another form called "INFOForm", bound to "INFOTable", and display the search
results from "CompleteName" based on the word in "txtCompleteName". In "INFOForm" the Text Box that display "CompleteName" records is "boxCompleteName".

For example, I have 3 records in "CompleteName": «Michael Ryan Smith», «Daniel Carlton» and «Michael Daniel Carter», and I want to search by «Daniel» and find 2 records: «Daniel Carlton» and «Michael Daniel Carter».

I hope someone could help me with this, after all we are always learning and I really want to learn how to do this!!

I would appreciate your help. Thank You!!

mariannesooz

 

[ecniv]

Create the new filtered form layout, use a default recordset initially to allow binding (I personally don't like binding)
On the search form, initially I'll assume that the users will be searching using one name. First you need an Sql statement. So open a new query, add the table you want, drop the * for all fields or just those fields you want to display. In the top left corner on the toolbar, change the drop down to SQL.
Copy the text.
Go back to the search form, go into the VBA code (click event) of the button.
Type the following:
[vbcode]
Dim strSql as string, strWhere as string

strsql=""
strwhere = ""

if len(strWhere)>0 then strsql = strsql & " WHERE " & strwhere

docmd.openform "frmSearchResults"
forms("frmSearchResults").recordsource = strsql
forms("frmSearchResults").requery 'or .refresh
[/vbcode]
The SQL text you copied, you need to paste into the strsql="<here>"
If you run over several lines you can use:
[vbcode]strsql=strsql & "<continued text>"[/vbcode]
but use what you feel happy with.

strwhere should be :
[vbcode]strwhere = "[tablename].[fieldname] like '*" & txtSearchBox & "*'"[/vbcode]
Note the single quotes

I will post an alternative later on sometime, if I get a chance, but this should give you some ideas.

Also you might want to think about using a listbox to display the list of people and double clicking it to pop up the form...

Good luck.. any problems please post up.

Vince