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 am interested in doing the same sort of thing!

how have you got the text box to work instead of the normal search box that appears when you run a query? Tezdread
"With every solution comes a new problem"

 

[aaronjonmartin]

To search with text entry boxes in forms check out this thread i started:

thread702-309584

 

[aaronjonmartin]

Getting back to my original post, anybody have any ideas. To re iterate I need a combo box containing the field names of all fields in my table. Selecting a field name and entering a string in the text box allows you to search for that string in that field in the table. I hope that is a clear explanation.

Cheers

 

[aaronjonmartin]

I have the text box and I have the combo box containing all the field names I just need some code to go in the buttonclick event.

 

[Tezdread]

aron, I found the other information useful and have got the textbox search to work from within my form.

I tried following the combo box instructions but unsucessfully.

I used the combo box wizard to get details from one field within a table but I had duplications and I couldn't get rid of these. I tried what was suggested (using "Group By&quot in the query but this didn't work either and I also had a UID field in the query and when I removed this I lost all the information from the combo box?? Tezdread
"With every solution comes a new problem"

 

[Rolliee]

The fields in a query are referenced by their position with the first as zero. Your field name in in me.text1

Here is code to find the particular entry you want.

DIM rs as DOA.recordset, FieldNo as long

Me.OrderByOn = True
Me.orderby = rs.Fields(FieldNo).Name

set rs = me.recordsetclone
rs.movelast
rs.movefirst
do while not rs.eof
if rs![fields(FieldNO)] >= me.text1 then
me.bookmark = rs.bookmark
exit do
endif
rs.movenext
loop

rs.close
set rs = nothiing
exit sub

 

[aaronjonmartin]

Sorry Rolliee iam a complete begginer with this stuff, what do I do with this code? Thanks for your input

 

[Rolliee]

Put the text box on your form to receive the text. Select some method to pick the field number - a combo box with the field names would do it) Then a cmd button with 'FIND' on it would start the process. The code is attached to the "onclick' event on the command button.

Hope this helps.

Rollie E

 

[aaronjonmartin]

Cheers for the help again Rolliee,

I did what you said and I got this compile error "User-defined type not identified" on this line

DIM rs as DOA.recordset, FieldNo as long

The bold bit was highlighted by the compiler, any ideas? Have I missed something?

Aaron

 

[aaronjonmartin]

Also I have a form set up to display the results of this in. Will this code allow me to do this?

Cheers

Aaron

 

[Russie]

The problem is you don't have the correct entry in your referencies.

When looking at your IDE click Tools & References.

Scroll down and click:

Microsoft 3.6 DAO Object Library

Click OK

 

[Rolliee]

that reference to object not found is becasue you did not go to tools and check dao 3.6 lib. It is down the list a bit but is required. Still have problems send me an email describiing what you want and I will send a sample.

rollie@bwsys.net

 

[aaronjonmartin]

Russie,

Sorry Im stupid but, you said:

When looking at your IDE click Tools & References.

What is my IDE? I tried clicking tools on the main tool bar but couldnt see any References option. Iam running 2000 but also have access to XP.

Cheers

Aaron

 

[aaronjonmartin]

Sorry,

I found what you were an about Russie and did what you said, however Im still getting the same error in the compiler.

Any ideas

Aaron

 

[Rolliee]

When you are in the forms "design" mode, and click tools, the top itemm iin the menu is "References" Click that and look down the list for ms Doa 3.6 and click it. Otherwise ACCESS does not know the commands for the DOA formats. It is ADO default. That means it uses a different set of commands as default. You must set that in order to run the DOA commands. Otherwise the compiler says, "I do not know what you are talking a bout."

rollie e

 

[Tezdread]

"When you are in the forms "design" mode, and click tools, the top item in the menu is "References"

I opened the form that contains a text box that I is working to carry out a search, clicked Tool (top Menu)the top item is Spelling? Am I missing something obvious? I just can't find 'References' anywhere.

One question though, where I work all the systems are all the same (HD Images) all installations are the same, so if this is not part of my Access installation would this mean that this MS Doa would have to be installed on each of the computers that would use this search function?

If this is the case then for me it is not a solution because of the problem that would arise trying to get all the other computers updated with the required functionality.

This leads me to the question, Is there another way that this can be accomplished?

Being new to Access, maybe I'm being a little over ambitious but this type of functionality would be so much use I'd be crazy not to try it... Tezdread
"With every solution comes a new problem"

 

[aaronjonmartin]

Tezdread,

You need to be in the VB compiler, click tools menu and references is at the top. But Ive done this and still get the same compiler error as before.

Aaron

 

[Tezdread]

ok, this way looks like it might be complicated if I have to make changes to each computer that will use the database.

I have been playing and I've got the combo box to work but not in conjuntion with the text box, but will play more.

I used the same method used by the text box, [Forms]![Form1]![text0] and replaced [text0] with [combo01] and this works for the combo box. I then used Like "*" & [Enter word ] & "*" for the keyword search.

e.g. Form contains combo box and search button, user makes selection from combo box then hits the button which runs the query and a dialog box appears to enter the keyword. This will then search the subject field (combo01) and the keyword field.

This is the closest I've got so far, ideally I would have more than one combo box and the text field in the table but so far this works!

Tezdread
"With every solution comes a new problem"

 

[aaronjonmartin]

Tezdread,

That is pretty much exactly what I want, and you are up to the same speed as me. The only problem that I need to solve is getting the text entered in the field to be matched in the field selected in the combo box. E.g. "Aaron" is entered in the text box and FirstName is selected in the combo box and the go button clicked. This would find and display all "Aaron"s in the FirstName field of my table. If I can get that to work I will be a very happy guy!

Aaron