Dynamically search multiple fields in listbox question

[magster06]

I am using John Big Bootys code for searching fields in a listbox am having a problem. Here is Johns original thread http://www.access-programmers.co.uk/forums/showthread.php?t=188663

My issue is this:
When I try to select a field that I have not attached the search criteria to, everything in the listbox is removed. For example ... If I type a case number (which does not have the criteria) like 2013-1, then all the records disappear.
When I place the search criteria in the "Case Number" field, then no matter what I type in the search box, then all of the records disappear again.

I will post pics as soon as I get on my laptop.

 

[John Big Booty]

I am using John Big Bootys code for searching fields in a listbox am having a problem. Here is Johns original thread http://www.access-programmers.co.uk/forums/showthread.php?t=188663

My issue is this:
When I try to select a field that I have not attached the search criteria to, everything in the listbox is removed. For example ... If I type a case number (which does not have the criteria) like 2013-1, then all the records disappear.
....

Well that stands to reason you can't search a field that is not included in the underlying query, and if there is no match in any of the other fields then you will get no results.

...

When I place the search criteria in the "Case Number" field, then no matter what I type in the search box, then all of the records disappear again.

I will post pics as soon as I get on my laptop.

Post a copy of your DB if possible that will be easier to diagnose than a picture

 

[John Big Booty]

... when you do post a copy of your DB strip out the majority of your data just leaving some representative sample to work with. Then do a Compact and repair and place the DB in a Zip file and upload that.

 

[magster06]

Ok, will do when I get to work tomorrow.

Please excuse my poor attempt at explaining what I was trying to describe.

I knew that when I typed a "Case Number" I would not receive any results, but when I did place the Like "*" & [forms]![Case Search Form]![SrchText] & "*" in the criteria, the listbox would clear itself when it had been working fine for everything other than the "Case Number".

When I remove the code for the criteria, then the search would work again for every field (of course not the case number).

 

[John Big Booty]

Did you shift the Criteria one row down in the query design grid?

 

[magster06]

Did you shift the Criteria one row down in the query design grid?

John, not only am I an idiot, but I cant seem to read as well.

I dont know how I missed that bit of instruction when you clearly stated it in a couple of places.

I will try that tomorrow, but I am sure that is where the problem lies.

 

[John Big Booty]

Good luck with that Don't worry it's an easy mistake to make and we've all done it at some time

 

[magster06]

UPDATE:

Did you shift the Criteria one row down in the query design grid?

Yes, that was indeed the problem. Thanks again!

 

[John Big Booty]

Glad you got it working

 

[magster06]

Hey John,

I am having another issue with the search. The search works great, but with the exception of when I try to type an " i " into the search box.

I receive a 2110 error "Cant move the focus to the control SearchResults"

Any ideas why this only happens with the letter " i ". I have tested all other letters and the search works as it should.

 

[samjh]

Hi,

I too have used this search function in my database, and it works perfectly. My question though, isa when I then select one of the records and my new form opens, how do close the search form, without actualy physically closing it?
Thanks.

 

[magster06]

how do close the search form, without actualy physically closing it?

Do you mean hiding your search form or closing it?

Closing the form:

 DoCmd.Close acForm, "form name to close"

Place this in the on load event of the form that is opening.

 

[samjh]

I found the code nedded to hide them, so have hidden them all after opening the new forms. My problem, is I'm never sure where the bit code should go.

 

[John Big Booty]

Hey John,

I am having another issue with the search. The search works great, but with the exception of when I try to type an " i " into the search box.

I receive a 2110 error "Cant move the focus to the control SearchResults"

Any ideas why this only happens with the letter " i ". I have tested all other letters and the search works as it should.

This is an issue (or similar) that has been raised in the past. I have been unable to replicate it. I'm 99% sure that it is related to machine set up, as there is nothing in the code that would cause that behaviour.

 

[John Big Booty]

I found the code nedded to hide them, so have hidden them all after opening the new forms. My problem, is I'm never sure where the bit code should go.

I presume that you are talking about the code in the Double Click event that opens the current record in a pop up form. If this is the case, then you code to hide the search form, should be the last command in that section of code.

 

[magster06]

This is an issue (or similar) that has been raised in the past. I have been unable to replicate it. I'm 99% sure that it is related to machine set up, as there is nothing in the code that would cause that behaviour.

I had a terrible feeling that you were going to say something like that. Oh well, I guess I will just try to trap the error.

Thanks for the replay John!

 

[sethgecko1977]

Magster06
I've just had the same issue with the I key in search causing an 2110 error, by chance i discovered if i had caps locks pressed it worked fine it was only on a lowercase i that it crashed.
I used this code to resolve it by forcing the search box to use upper case:

Private Sub Text1_Keypress(KeyAscii as Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

 

[magster06]

That is a great idea!

Thanks sethgecko1977!

 

[Minddumps]

Magster06
I've just had the same issue with the I key in search causing an 2110 error, by chance i discovered if i had caps locks pressed it worked fine it was only on a lowercase i that it crashed.
I used this code to resolve it by forcing the search box to use upper case:

Private Sub Text1_Keypress(KeyAscii as Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
End Sub

This worked for me too! Thanks!!

 

[miicker]

Hello, I have a question about the search too. I'm Dutch, so sorry for my English.
I am using the same thread as well.

I have the following problem (I used exactly the steps as in the tutorial):
When I typ something in the "SearchFor" nothing happens.
I changed the visibilty of the "SrchText" to visible, but nothing is happening to that text box, when I look at you example DB, it gets filled with the same text as SearchFor.

Also the "SearchResults" isn't updating. Can someone please help me?