[ACCESS 97] Search multiple keywords (1 Viewer)

[bodylojohn]

I have a feeling it's not "strictly" an Access error, probably more of a logic error. What I mean is it almost looks as though the programmer is sending a msgbox to himself whenever things are not as they should be, as part of his error routine. Maybe MeldingLog means "Error Log". Do you know this language? Is it German?

That's some pretty advanced code, so finding logic errors wouldn't be easy for me, especially with the language barrier.

Well, when I use the original code there is no error.
When i use the code adjusted by me the error message appears.

I am clueless

 

[jal]

BTW, you can set breakpoints by clicking in the left margin as to produce a brown dot/circle. Another technique you can use here is to comment out this line:

On Error GoTo Err_InLijst

because it is causing the code - when an error occurs - to divert to the Melding error routine instead of halting at the problem line as for you to see which line is the problem.

 

[jal]

Earlier you asked me to type something into the textbox. But you said nothing about the dropdown (the cbo just above the textbox). Aren't you supposed to select something from the dropdown first, and then type something into the textbox? And then click the forward button or backward button?

 

[jal]

Ok, yes, it seems to be a search form. When you select an item from the dropdown, the subform is populated with 600 records. When you begin typing a search keyword into the textbox, the textchanged event fires, which calls the search function which involves code such as

.FindNext

It's a wildcard search on the column Art_Nr which has mostly numbers such as
04075NR
04076NR
04603NR

so if I type in 046 then the subform pointer (in the left margin) will point to the record 04603NR. It seemed to be working okay when I tried it a couple of times.

 

[jal]

Why did you change the original code? Whoever wrote that code seems to know what he was doing.

 

[bodylojohn]

Why did you change the original code? Whoever wrote that code seems to know what he was doing.

Wel..
The guy who wrote the program doesnt support it any more and becausei know the guy who bought the program he asked me to modify the search so he could search on keywords seperated with a comma.

fe: uitlaat,nsr

I hope this clarifies a lot.

Thanks again BTW.

 

[jal]

Looks like you've got two columns being searched.
Omschr
ArtNr

In my opinion the user would expect the search to use an AND if he types a keyword into both textboxes. In other words (this is pseudocode).

.FindFirst "Omschr LIKE txtOmschr* AND ArtNr LIKE txtArtNr*"

as opposed to an OR

.FindFirst "Omschr LIKE txtOmschr* OR ArtNr LIKE txtArtNr*"

That's the first decision that needs to be made. The next question is, what the heck does it mean to run FindFirst on a comma-separated list of keywords? Similar to the question above, do we use an OR between each keyword, or do we use an AND?

 

[jal]

Ok it looks like you only make use of one textbox at a time, even if the user types a keyword into both textboxes. And it looks like you want to use AND for a comma-separated list.

 

[bodylojohn]

Well, you either search on artnr (article number) or txtomsz (describtion).
Zo as you start typing something in the "txtomsZ" txtbox automatically the focus goes to the first match.

I also posted the original working code at the following location:
http://www.sosol.nl/access/OriginalCode.zip

The reason I put the comma code in there is because of the fact that I want to search on multiple keywords seperated by a comma.

I hope the original code helps.

 

[jal]

My apologies - I found part of the problem here, I should have caught this earlier. I didn't catch it because I normally don't use the textbox_Change event. Here's the problem with this event. There are two possible ways to get the value of a textbox:

txt1 (this is the same as txt1.Value)
txt1.Text

I am guessing that in the textbox_Change event you would need to use the second of these. You have it as this (probably due to my poor advice):

words = Split(txtOmsZ, comma)
which leads to "invalid use of null". The alternative would be this:
words = Split(txtOmsZ.Text, comma)
but that probably would only work some of the time, since you fire this sub both within and without the Textbox_Changed event. Probably the following would work, as the programmer seems to have stored the value in a string called tvarArtOms:

words = Split(tvarArtOms_i, comma)

Let me know how it goes.

 

[jal]

I just tried it and got an error I've never seen before...

 

[bodylojohn]

My apologies - I found part of the problem here, I should have caught this earlier. I didn't catch it because I normally don't use the textbox_Change event. Here's the problem with this event. There are two possible ways to get the value of a textbox:

txt1 (this is the same as txt1.Value)
txt1.Text

I am guessing that in the textbox_Change event you would need to use the second of these. You have it as this (probably due to my poor advice):

words = Split(txtOmsZ, comma)
which leads to "invalid use of null". The alternative would be this:
words = Split(txtOmsZ.Text, comma)
but that probably would only work some of the time, since you fire this sub both within and without the Textbox_Changed event. Probably the following would work, as the programmer seems to have stored the value in a string called tvarArtOms:

words = Split(tvarArtOms_i, comma)

Let me know how it goes.

WOOOW....you absolutely didnt give poor advice.
I am not sucking up to you but without you I never came this far.

If I use txtOmsZ.text it works when I am typing.
However when I want to find the next record I get the error (translated from dutch):
You can only refer to a property or method of a control if the control has the focus.

 

[jal]

This seems to work:

Dim keywords As String
keywords = varArtOms_i
words = Split(keywords, comma)

 

[jal]

WOOOW....you absolutely didnt give poor advice.
I am not sucking up to you but without you I never came this far.

If I use txtOmsZ.text it works when I am typing.
However when I want to find the next record I get the error (translated from dutch):
You can only refer to a property or method of a control if the control has the focus.

Thanks for the kind words. I am pretty slow compared to the veterans on this forum. They probably would have caught the problem right away. In fact I have a bit of a learning disability. I usually make progress, though, because I put in a lot of hard work.

 

[bodylojohn]

This seems to work:

Dim keywords As String
keywords = varArtOms_i
words = Split(keywords, comma)

Bro....you are a genius!!!!!!

It works!

Thank you very very much for all of your help man.

If there is ever anything I can do for you just let me know.

 

[jal]

But as I was saying, post #53 seems to be working. Let me know how it goes.

 

[bodylojohn]

Thanks for the kind words. I am pretty slow compared to the veterans on this forum. They probably would have caught the problem right away. In fact I have a bit of a learning disability. I usually make progress, though, because I put in a lot of hard work.

That doesnt mather at all.

Not only did you solve the problem.
But I learned a lot from it.

Thanks again bro.

 

[bodylojohn]

But as I was saying, post #53 seems to be working. Let me know how it goes.

See post #55 ;-)

Thanks again!

 

[spideynok]

Hi sir bodylojohn! Can I see the whole database if possible? I just need this badly