Search form help 1

[aaronjonmartin]

I want to have a search form which has a pull down menu and a text box. I want the pull down to allow the user to choose any field in a table and the text box is to allow a user to enter a string. So when it is used the user can choose what field they wish to search in (Name, Address, Job Title, etc) and the string they enter in the text box is located in the field they chose. I know how to do the text box but I dont know how to set up the pull down. Any ideas?

 

[Tezdread]

I've got it!! *Buzzin*

I now have two combo boxes, a text box and a search button on a form and with the right combinations it works a treat!

All I've done is use the function that worked in the text box

In the query that opens the form that displays the results
I have the following:

Field: Question, Table: FAQ_tbl, Total: Group By, Show: Yes, Criteria: Blank

Field: Answer, Table: FAQ_tbl, Total: Group By, Show: Yes, Criteria: Blank

Field: Department, Table: FAQ_tbl, Total: Group By, Show: Yes, Criteria: [Forms]![Form1]![Combo9]

Field: Product, Table: FAQ_tbl, Total: Group By, Show: Yes, Criteria: [Forms]![Form1]![Combo11]

Field: Question, Table: FAQ_tbl, Total: Group By, Show: Yes, Criteria: [Forms]![Form1]![Text0]

This all works, however it does need extra functionality for it to work really well, because of this reason;

If I only specify 'Department' only and hit search I get Null results, the same for 'Products' and 'Keyword' so I need to specify all fields and if when the search is carried out all of these are not matched I get Null results.

When I was using the dialog box before I had the criteria set on the keyword as: Like "*" & [Enter Keyword] & "*"

I need to add this somehow to the text box so that it will search all words in the Keyword field, at the moment if there is more than one word in this field I get null results.??

Aron hope this helps you... Tezdread
"With every solution comes a new problem"

 

[aaronjonmartin]

And this allows the user to chose which field to search in with a combo box?

 

[Tezdread]

yeah, the combox box links to a field within a table the contents of the combo box aren't dupicated and this works in the same way as typing the subject into a dialog box.

If you want me to email you the sample database that I've been working on let me know. Tezdread
"With every solution comes a new problem"

 

[aaronjonmartin]

that would be fantastic mate, my email is

aaron@bluecannon.co.uk

Thanks for the assistance

Aaron

 

[Tezdread]

emailed it to both of you, let me know if you have any problems Tezdread
"With every solution comes a new problem"

 

[Tezdread]

Ok I got further with it now.

Before the user entered values in each of the combo boxes and the text box and then the query searched the specified fields in the table and if there was a match on all fields the results would be displayed.

This wasn't very good because if a user only wanted to do a keyword search leaving the combo boxes blank the search would return null results because each record would have an item from the combo box in the field.

The criteria was this:

[Forms]![search-form]![Combo4]
[Forms]![search-form]![Combo6]
[Forms]![search-form]![Text0]

If you add a little more to the criteria you will be able to carry out a search using any or all of the controllers within the form.

Like this : Like "*" & [Forms]![search-form]![Combo4] & "*"

This is the SQL of the query:

SELECT main_tbl.[First Name], main_tbl.Surname, main_tbl.Company, main_tbl.[Job Title], main_tbl.Question, main_tbl.Answer, main_tbl.Keywords
FROM main_tbl
WHERE (((main_tbl.Surname) Like "*" & [Forms]![search-form]![Combo4] & "*&quot AND ((main_tbl.Company) Like "*" & [Forms]![search-form]![Combo6] & "*&quot AND ((main_tbl.Keywords) Like "*" & [Forms]![search-form]![Text0] & "*&quot);

Hope this helps Tezdread
"With every solution comes a new problem"

 

[CarolOB]

Tezdread,
Would you please e-mail me a copy of your sample database too?

carolob@attbi.com


Thanks CarolOB

 

[danvlas]

Sub YourButton_Click()
With RecordsetClone
.FindFirst YourComboBoxName & "='" & YourTextBoxName & "'"
If .NoMatch Then
MsgBox "The string '" & YourTextBoxName & "' could not be found in field '" & YourComboBoxName & "'"
Else
Bookmark = .Bookmark
End If
End Sub

That will do the trick...

Replace:
YourButton with the exact name of the button
YourComboBoxName with the exact name of your combo box
YourTextBoxName with the exact name of your text box.

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[InfoNow]

Tezdread,
I would like to have a copy of your sample db as well. Email is qvh69@yahoo.com

Thanks,

 

[Tezdread]

It's been sent to both of you. If you make any progress/adjustments please post the info on here. Tezdread
"With every solution comes a new problem"

 

[aaronjonmartin]

Tezdread,

Ive been on holiday for a bit so I only got your email this morning. I actually couldnt get your sample database to work but more importantly I think we have had crossed wires. I dont want the combo box to be used to add criteria into the query like in yours. I want it to allow the user to choose which field in the table to search in. For example I wish to find plumbers in my database so from the combo box I select Job_Title (the name of the column in my table) and type "Plumber" in the text box. Click go and it will return all records matching plumber in the Job_Title column of my table. I require the pull down to allow more flexible searching, to allow the user to choose what to search and where to look for it. Im still having trouble achieving this so if anybody has any ideas please post.

Cheers

Aaron

 

[danvlas]

I made a sample.
Download it from:
ftp://ftp.artrom.ro/SearchForm.zip

It contains two files (one for Access 97 and one for Access 2K/XP).

Let me know...


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[aaronjonmartin]

Dan,

That worked a treat mate, ideally I would like the form to display only the results. At the moment it only returns the first value matching the criteria. For example if I select FirstName and enter "David" it will display the first david if I continue to cycle through records other names will appear. I would like it that if you enter "David" the only records that are displayed are those with the first name of "David". Is it possible to modify what you have done to allow me to do this? This would be of great help.

Thanks for all the help so far Daniel

Aaron

 

[danvlas]

Delete alllllll With block (starting from With and ending with End With)

Paste this instead:

Filter = srcString
FilterOn = True

And that's all...

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[aaronjonmartin]

Dan you are the man! That worked an absolute treat. Thanks again for all the help bro.

Aaron

 

[danvlas]

I replaced that file with a new one, in my opinion much more portable than the previous one.
It does both search and filter the recordset and it's easier to implement in an application.

Good luck


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[agoraccess]

Hello everyone.

Since everyone seems to be asking for Tezdread's database, I was wondering if you could send me a copie too? Thanks
Michelle

michelle.agorastos@csoconline.com