How to make a search engine in a form

[Christopher Koh]

I will make a form which will search the database (just like google
interface) that will look/match for the exact name in the records of a
given fieldname. Any suggestions on how to make the code?

[Salad]

Christopher Koh wrote:

I will make a form which will search the database (just like google
interface) that will look/match for the exact name in the records of a
given fieldname. Any suggestions on how to make the code?

Create a form with the fields for input. Have a button to start the
search and another to exit. With the search button, open up the
property sheet and create a proc in Events for the OnClick event. Then
create a filter string. Ex:

Dim strF As String
If not isnull(Me.Text0) Then strF = _
"[TableFld0] = '" & Me.Text0 & "' And "
If not isnull(Me.Text1) Then strF = strF & _
"[TableFld1] = '" & Me.Text1 & "' And "
If not isnull(Me.Text2) Then strF = strF & _
"[TableFld2] = '" & Me.Text2 & "' And "
.....and etc. then remove the word And at end of filter
If strF > "" THen strF = Left(strF,len(strF)-5)

'now filter or open a form/report
Forms!FormToFilter.Form.Filter = strF
Forms!FormToFilter.Form.Filter = True

or
DOcmd.OpenReport "YourReport",,,strF

[Christopher Koh]

thanks salad, can you just input some explanations or input the comments
again thanks
Salad <oi*@vinegar.com> wrote in message news:<9R*******************@newsread1.news.pas.ear thlink.net>...

Christopher Koh wrote:

I will make a form which will search the database (just like google
interface) that will look/match for the exact name in the records of a
given fieldname. Any suggestions on how to make the code?

Create a form with the fields for input. Have a button to start the
search and another to exit. With the search button, open up the
property sheet and create a proc in Events for the OnClick event. Then
create a filter string. Ex:

Dim strF As String
If not isnull(Me.Text0) Then strF = _
"[TableFld0] = '" & Me.Text0 & "' And "
If not isnull(Me.Text1) Then strF = strF & _
"[TableFld1] = '" & Me.Text1 & "' And "
If not isnull(Me.Text2) Then strF = strF & _
"[TableFld2] = '" & Me.Text2 & "' And "
....and etc. then remove the word And at end of filter
If strF > "" THen strF = Left(strF,len(strF)-5)

'now filter or open a form/report
Forms!FormToFilter.Form.Filter = strF
Forms!FormToFilter.Form.Filter = True

or
DOcmd.OpenReport "YourReport",,,strF

[Christopher Koh]

I would just like to followup, my output is in tabular form so it has
many field rows.Can you suggest what code i should write so that it
would just fill in the tab form thanks
Salad <oi*@vinegar.com> wrote in message news:<9R*******************@newsread1.news.pas.ear thlink.net>...

Christopher Koh wrote:

I will make a form which will search the database (just like google
interface) that will look/match for the exact name in the records of a
given fieldname. Any suggestions on how to make the code?

Create a form with the fields for input. Have a button to start the
search and another to exit. With the search button, open up the
property sheet and create a proc in Events for the OnClick event. Then
create a filter string. Ex:

Dim strF As String
If not isnull(Me.Text0) Then strF = _
"[TableFld0] = '" & Me.Text0 & "' And "
If not isnull(Me.Text1) Then strF = strF & _
"[TableFld1] = '" & Me.Text1 & "' And "
If not isnull(Me.Text2) Then strF = strF & _
"[TableFld2] = '" & Me.Text2 & "' And "
....and etc. then remove the word And at end of filter
If strF > "" THen strF = Left(strF,len(strF)-5)

'now filter or open a form/report
Forms!FormToFilter.Form.Filter = strF
Forms!FormToFilter.Form.Filter = True

or
DOcmd.OpenReport "YourReport",,,strF

[Christopher Koh]

I was just wondering how come there are a lot of textfields like
me.text0,me.text1 when i only have one textfield search engine

Salad <oi*@vinegar.com> wrote in message news:<9R*******************@newsread1.news.pas.ear thlink.net>...

Christopher Koh wrote:

I will make a form which will search the database (just like google
interface) that will look/match for the exact name in the records of a
given fieldname. Any suggestions on how to make the code?

Create a form with the fields for input. Have a button to start the
search and another to exit. With the search button, open up the
property sheet and create a proc in Events for the OnClick event. Then
create a filter string. Ex:

Dim strF As String
If not isnull(Me.Text0) Then strF = _
"[TableFld0] = '" & Me.Text0 & "' And "
If not isnull(Me.Text1) Then strF = strF & _
"[TableFld1] = '" & Me.Text1 & "' And "
If not isnull(Me.Text2) Then strF = strF & _
"[TableFld2] = '" & Me.Text2 & "' And "
....and etc. then remove the word And at end of filter
If strF > "" THen strF = Left(strF,len(strF)-5)

'now filter or open a form/report
Forms!FormToFilter.Form.Filter = strF
Forms!FormToFilter.Form.Filter = True

or
DOcmd.OpenReport "YourReport",,,strF

[Salad]

Christopher Koh wrote:

I was just wondering how come there are a lot of textfields like
me.text0,me.text1 when i only have one textfield search engine
I was descibing a method I use.

Are you refering to my use of me.Text0...1 etc? I suppose you are.

Take a look at advanced search in google.

Are you searching/filtering only on 1 field?

This is turning into a complex problem. You should know how to parse
out values in a string and you know what field you want to search.
Database tables consist of rows and columns. DO you know what you are
searching on or is it any table and any field?

Regarding a tab form. What does that mean? Does that mean your tabs
have individual subforms? Does your form have a recordsource?

If the search finds a record in some table for some field what do you
want it to do? Do you want to open a form, close a form, tell the user
a record was found, display a list of records that match?

Maybe you can tell us what you want to do besides create a google interface.

Salad <oi*@vinegar.com> wrote in message news:<9R*******************@newsread1.news.pas.ear thlink.net>...

Christopher Koh wrote:

I will make a form which will search the database (just like google
interface) that will look/match for the exact name in the records of a
given fieldname. Any suggestions on how to make the code?

Create a form with the fields for input. Have a button to start the
search and another to exit. With the search button, open up the
property sheet and create a proc in Events for the OnClick event. Then
create a filter string. Ex:

Dim strF As String
If not isnull(Me.Text0) Then strF = _
"[TableFld0] = '" & Me.Text0 & "' And "
If not isnull(Me.Text1) Then strF = strF & _
"[TableFld1] = '" & Me.Text1 & "' And "
If not isnull(Me.Text2) Then strF = strF & _
"[TableFld2] = '" & Me.Text2 & "' And "
....and etc. then remove the word And at end of filter
If strF > "" THen strF = Left(strF,len(strF)-5)

'now filter or open a form/report
Forms!FormToFilter.Form.Filter = strF
Forms!FormToFilter.Form.Filter = True

or
DOcmd.OpenReport "YourReport",,,strF

[Christopher Koh]

No, i am not using a subform. My form uses the return results of a
query in a tabform in the detail section. I just found out that you
can use the binoculars on the menu to find the searched word in the
tabular form then highlights it, it also has a find next.
This was my intention. But instead of using this , I want another way
thru VB editor in which the user inputs what he wants to search on the
textbox, then he clicks on the cmdgo button to look for the word in
the tabularform then highlights it, it also has a findnext

Salad <oi*@vinegar.com> wrote in message news:<i7*****************@newsread2.news.pas.earth link.net>...

Christopher Koh wrote:

I was just wondering how come there are a lot of textfields like
me.text0,me.text1 when i only have one textfield search engine

I was descibing a method I use.

Are you refering to my use of me.Text0...1 etc? I suppose you are.

Take a look at advanced search in google.

Are you searching/filtering only on 1 field?

This is turning into a complex problem. You should know how to parse
out values in a string and you know what field you want to search.
Database tables consist of rows and columns. DO you know what you are
searching on or is it any table and any field?

Regarding a tab form. What does that mean? Does that mean your tabs
have individual subforms? Does your form have a recordsource?

If the search finds a record in some table for some field what do you
want it to do? Do you want to open a form, close a form, tell the user
a record was found, display a list of records that match?

Maybe you can tell us what you want to do besides create a google interface.

Salad <oi*@vinegar.com> wrote in message news:<9R*******************@newsread1.news.pas.ear thlink.net>...

Christopher Koh wrote:
I will make a form which will search the database (just like google
interface) that will look/match for the exact name in the records of a
given fieldname. Any suggestions on how to make the code?

Create a form with the fields for input. Have a button to start the
search and another to exit. With the search button, open up the
property sheet and create a proc in Events for the OnClick event. Then
create a filter string. Ex:

Dim strF As String
If not isnull(Me.Text0) Then strF = _
"[TableFld0] = '" & Me.Text0 & "' And "
If not isnull(Me.Text1) Then strF = strF & _
"[TableFld1] = '" & Me.Text1 & "' And "
If not isnull(Me.Text2) Then strF = strF & _
"[TableFld2] = '" & Me.Text2 & "' And "
....and etc. then remove the word And at end of filter
If strF > "" THen strF = Left(strF,len(strF)-5)

'now filter or open a form/report
Forms!FormToFilter.Form.Filter = strF
Forms!FormToFilter.Form.Filter = True

or
DOcmd.OpenReport "YourReport",,,strF

[Salad]

Christopher Koh wrote:

No, i am not using a subform. My form uses the return results of a
query in a tabform in the detail section. I just found out that you
can use the binoculars on the menu to find the searched word in the
tabular form then highlights it, it also has a find next.
This was my intention. But instead of using this , I want another way
thru VB editor in which the user inputs what he wants to search on the
textbox, then he clicks on the cmdgo button to look for the word in
the tabularform then highlights it, it also has a findnext

When you said "Tabular form" I thought you meant a form with tabs on them.

I have two methods I'll talk about.

I call your type of form a log form and my log forms are "continuous"
forms, not datasheet. I'm basically displaying a list of records in a
table filtered and sorted the way I want. Above each column I put a
text field in the form header. When a person enters a value in that
field above that column, I clear out any other values in the other
search fields (I use the Tag property to determine which fields to
clear). I associate each column with a field. I then create a search
string from it and store it to a public variable. When the person
presses Enter or Tab, it searches for that field. If it finds it, it
moves to that record and sets focus. If the F3 key is pressed, it
searches for the next record. It's a pretty generic form.

The second method uses a form/subform. The Main form has 2 combo
fields; a combo box and a search field. The subform is a datasheet.
When I call the form, I pass the name of the calling form. Based on the
calling form I change the recordsource of the subform. The combo box is
updated and display a list of all columns in the datasheet. The person
then selects the field to search for in the combo and enters the value
to search for. When enter or tab is pressed it searches for the record
for a matching value for the column field selected in the combo. If
found, it moves to that record.

I set the dbl-Click on the records to present single record forms for
the record found.

That's what I do/use.

[Christopher Koh]

Salad, i think your first method suits my intention, can you give me
the vb edit code for it so that i can study it, thanks Salad
Salad <oi*@vinegar.com> wrote in message news:<93*****************@newsread2.news.pas.earth link.net>...

Christopher Koh wrote:

No, i am not using a subform. My form uses the return results of a
query in a tabform in the detail section. I just found out that you
can use the binoculars on the menu to find the searched word in the
tabular form then highlights it, it also has a find next.
This was my intention. But instead of using this , I want another way
thru VB editor in which the user inputs what he wants to search on the
textbox, then he clicks on the cmdgo button to look for the word in
the tabularform then highlights it, it also has a findnext

When you said "Tabular form" I thought you meant a form with tabs on them.

I have two methods I'll talk about.

I call your type of form a log form and my log forms are "continuous"
forms, not datasheet. I'm basically displaying a list of records in a
table filtered and sorted the way I want. Above each column I put a
text field in the form header. When a person enters a value in that
field above that column, I clear out any other values in the other
search fields (I use the Tag property to determine which fields to
clear). I associate each column with a field. I then create a search
string from it and store it to a public variable. When the person
presses Enter or Tab, it searches for that field. If it finds it, it
moves to that record and sets focus. If the F3 key is pressed, it
searches for the next record. It's a pretty generic form.

The second method uses a form/subform. The Main form has 2 combo
fields; a combo box and a search field. The subform is a datasheet.
When I call the form, I pass the name of the calling form. Based on the
calling form I change the recordsource of the subform. The combo box is
updated and display a list of all columns in the datasheet. The person
then selects the field to search for in the combo and enters the value
to search for. When enter or tab is pressed it searches for the record
for a matching value for the column field selected in the combo. If
found, it moves to that record.

I set the dbl-Click on the records to present single record forms for
the record found.

That's what I do/use.

[PC User]

I wonder if the page on this link will help you. Take a look at:
http://www.utteraccess.com/forums/sh...b=5&o=&fpart=1

PC