Using a search box in a form

[SKK9614]

Hello, Hopefully someone can help me. I am new to Access 2007 and I been banging my head trying to figure out how to put a search box in a form. What I want to do is retreive information from a table and display it in a form. I figured out how to do that by creating a query and entering a parameter value, but what I want to do is just be able to type my search from a search box in my form and display the results within the same form my search box is on. I figured out how to do this using a combo box, but what I really want is a text box. If I change my combo box to a text box it won't work. I would be greatful for any help.

 

[John Big Booty]

Welcome to the forum.

Perhaps the sample posted here will give you some pointers.

 

[SKK9614]

Thank You Sir. This is what I was looking for and it should help me out a lot.

 

[SShafeeq]

hey, i had a similar problem and I was able to solve it, thanks to the solution given at the link listed above by JBB.

However, I need some help troubleshoot. The listbox works just fine, until when a user inputs anything being with "i" into the "searchfor" text box. Then it kinda crashes, giving the error 'Run time Error '2110': MS Access cant move the focus to the control SearchResults".

Why is this error popping up and how can I fx it?

Many thanks in advance!

 

[John Big Booty]

hey, i had a similar problem and I was able to solve it, thanks to the solution given at the link listed above by JBB.

However, I need some help troubleshoot. The listbox works just fine, until when a user inputs anything being with "i" into the "searchfor" text box. Then it kinda crashes, giving the error 'Run time Error '2110': MS Access cant move the focus to the control SearchResults".

Why is this error popping up and how can I fx it?

Many thanks in advance!

Sorry I can't reproduce that error, can yo post a copy of your DB?

 

[SShafeeq]

sure, i can do that, but (sorry for the ignorance) but how do I attach it?

 

[SShafeeq]

i have a feeling I knw the reason behind the problem. For some reason it keeps automatically capitalising "i"'s but not any other letter of the alphabet. Any way I can switch off its autocorrecter?

 

[John Big Booty]

sure, i can do that, but (sorry for the ignorance) but how do I attach it?

First up do a compact and repair on your DB, save it back to '03 version if possible, then put it in a zip file. Now create a new post and upload your zipped file using the little paper-clip icon at the top of the create post window.

 

[John Big Booty]

i have a feeling I knw the reason behind the problem. For some reason it keeps automatically capitalising "i"'s but not any other letter of the alphabet. Any way I can switch off its autocorrecter?

See if this article helps.

 

[vbaInet]

Then it kinda crashes, giving the error 'Run time Error '2110': MS Access cant move the focus to the control SearchResults".

If the control's Enabled property is set to No, then it will throw this error.

 

[John Big Booty]

If the control's Enabled property is set to No, then it will throw this error.

It would appear that the problem only arises when the user types an "I" as the first search variable

 

[vbaInet]

It would appear that the problem only arises when the user types an "I" as the first search variable

I think he's changed something in your code that is setting focus to that control.

 

[John Big Booty]

I think he's changed something in your code that is setting focus to that control.

Very likely but why only for the letter "I" I guess we'll not know until he posts a copy of his DB.

 

[vbaInet]

Very likely but why only for the letter "I" I guess we'll not know until he posts a copy of his DB.

Who knows John

 

[Sandbox]

That's a really useful way to search, but it seems to suffer significant performance issues on a split database over a network with it requerying the database after every change of character to update the list.

Is there a way to store the results of a of the query in a temp table (locally) and to just search that/or another way to improve performance?

Thanks

 

[Sandbox]

Figured out how to populate a temp table and requery that - now network performace isn't an issue.

 

[John Big Booty]

Figured out how to populate a temp table and requery that - now network performace isn't an issue.

You don't feel the need to share that (the how) with others who may follow

 

[Sandbox]

Yeah sure:

Because of the performance issues I found re-querying on a split database setup (with the back end on a network server, front end on a local computer), I found that populating a local temporary table with the search criteria from the BE database improved performance considerably for the constant re-querying as you type characters:

1) Create a temp table (tblTempTable) with the fields you wish to ‘search’.
2) Create an append query (qryTempTable) to populate the fields of the temp table from the BE database
3) Create a query to search temp table (as per original post) – link this query to the search results
4) On your search form:
a. In the On Open property

Private Sub Form_Open(Cancel As Integer)

'Disable Warning that data in "temp table" will be deleted and
'repopulated via a query with current data

Application.SetOption "Confirm Action Queries", 0
Application.SetOption "Confirm Document Deletions", 0
Application.SetOption "Confirm Record Changes", 0

DoCmd.RunSQL "DELETE * FROM tblTempTable"
DoCmd.OpenQuery "qryTempTable"

End Sub

b. In the On Close property

Private Sub Form_Close()

'Reactivate warnings when form closes

Application.SetOption "Confirm Action Queries", 1
Application.SetOption "Confirm Document Deletions", 1
Application.SetOption "Confirm Record Changes", 1

End Sub

4(a) above in the On Open property deletes all existing data from the temp table and repopulates with a snap shot of the current data from the BE data base – obviously the limitation with this workaround is if someone changes the data in the back end, it won’t be reflected in the search results.

Note if you want to edit/view the selected data in another form, make sure you include one of the primary keys in from the BE database in the temp table – you can then use this primary key as reference/link to open that form using ‘live’ data.

 

[John Big Booty]

Good work

 

[SShafeeq]

Hey, sorry about the late reply. The solution was actually their was an autocorrector, which would only work if you put in an 'i' and it would automatically capitalise it. When it did that, i think it threw off the object focus in the code and produced that error. Once the auto corrector is switched off, this error ceases to happen.

I have another query related to this. Now this search is brilliant for displaying records, however is there anyway to incorporate a search facility onto a continuous form?

Heres what I'm trying to do:

I have a Library Catalogue form, which is linked to tblLibCat. Now the header of the form has a txtbox (name txtGoto), a combo box (name: cmbGoto) and a button (name: btnSearch). The detail part of the form is the continuous form which has book id, book title, author, date published, publication type and quantity.

Now, what i wanted to do and (Im struggling with this), is for a user to type in a search string in txtGoto, choose which column to search in from the combo box (which has book id, book title, etc, and also ALL, to enable user to search the entire table). When the user clicks on btnSearch, I would like the form to display all the records the user has searched for.

Thanks in advance for all the help,

P.S: Also, please note, I'm a "she" not a "he"