Dynamically search multiple fields in listbox question (1 Viewer)

magster06 · Jan 17, 2013

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 · Jan 17, 2013

magster06 said:
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.

magster06 said:
...

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 · Jan 17, 2013

... 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 · Jan 17, 2013

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 · Jan 17, 2013

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

magster06 · Jan 17, 2013

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 · Jan 17, 2013

Good luck with that

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

magster06 · Jan 17, 2013

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 · Jan 17, 2013

Glad you got it working

magster06 · Jan 30, 2013

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 · Jan 30, 2013

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 · Jan 30, 2013

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

Do you mean hiding your search form or closing it?

Closing the form:

Code:

 DoCmd.Close acForm, "form name to close"

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

samjh · Jan 30, 2013

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 · Jan 30, 2013

magster06 said:
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 · Jan 30, 2013

samjh said:
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 · Jan 31, 2013

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 · Jul 1, 2013

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 · Jul 1, 2013

That is a great idea!

Thanks sethgecko1977!

Minddumps · Jul 2, 2013

sethgecko1977 said:
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 · Sep 8, 2013

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?

Dynamically search multiple fields in listbox question (1 Viewer)

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

Registered User.

AWF VIP

AWF VIP

Registered User.

New member

Registered User.

Registered User.

New member

Similar threads

Users who are viewing this thread