Search function problem...

[JvM]

Good afternoon,

I have a problem with a search function I build in to a form. This form searches a query with article information. Maybey some one can help me. I want multiple searches without closing and opening the form. I tried refreshing it on several key moments.

I don't see the answer..

Thankxxx

 

[bradcccs]

JvM

Try changing your code to:

Private Sub Bijschrift5_Click()

  
   ' declare variables.
   
     
   Dim Keywords As String
   Dim fieldToSearch As String
   Dim Criteria As String
   Dim SQL As String
   
   ' remove leading and trailing spaces, if any, from user input.
   Keywords = Trim(Me.txtKeywords)
   
   ' specify which field to search.
   fieldToSearch = "[Artikelnummer] & [Artikel_omschrijving]"
   
   ' create criteria based on input.
   Do While InStr(Keywords, " ") > 0
     Criteria = Criteria & fieldToSearch & " Like ""*" & Left(Keywords, InStr(Keywords, " ") - 1) & "*"" or "
     Keywords = Mid(Keywords, InStr(Keywords, " ") + 1)
   Loop
   Criteria = Criteria & fieldToSearch & " Like ""*" & Keywords & "*"""
    
   ' build SQL statement.
   SQL = "SELECT [Artikelnummer], [Artikel_omschrijving], [Leverancier], [Voorraad], [netto inkoop prijs], [Voorraadwaarde]" & _
   " FROM Voorraad" & _
        " WHERE " & Criteria
        
   Forms![Zoeken]![Subzoeken].Form.RecordSource = SQL
   
   Me.Subzoeken.Requery
   
End Sub

Note: I have removed your IsNull test and Subform.visible just for ease of testing.

This approach differs to yours by using the SQL as the rowsource for your subform, and not creating a TempTable/Query that your code was doing.

HTH

Brad.

 

[JvM]

Still it doesn't apply a second search. It stops with

Keywords = Trim(Me.txtKeywords)

it gives as failer that it uses the is Null on a wrong way..

How can I open the subform then if I don't set focus to it..

Making a second form?

Greetings,

Thankxx for bothering...

 

[bradcccs]

This Null error occurs because I removed you IsNull test.

Also worth noting. Your keyword does not update until you move the focus off the keyword field. If your cursor is still in the keyword field, then the SQL criteria still uses the old keyword.

This "focus" issue is caused by using a label to initiate the "On_Click" event, and not a command button. As a label does not receive the focus, the Keyword field does not update prior to the code running. If you changed the label to a Command Button, the focus issue would not occur.

Brad.

 

[JvM]

It tried to avoid using a command button.. I thought it would be easier changing a text label then to make a button in corral draw.
Because the back color can't be changed of a command button.

But if it's the best sollution I'll have to make one..

Put an ' for the null failure and it stopped..

Thankx.. I;m going to try to make the command button...

Greetings..

 

[bradcccs]

Creating your own buttons - Too much work

If you don't mind your subform receiving the focus, you could add this line to the start of your code

Me.Subzoeken.SetFocus

That way you can continue to use your label.

Brad.

 

[JvM]

I tried it but it gave me an error beacuase the focus couldn't been moved to the subform..

In this dbase there is just one button.. But last week I had to make a dbase with three forms with 15 labels each.. My works new home styl..

Great.. Somebody didn't bother to ask IT how much work it is to change all management info dbases.. But at least I keep my job..

Greetings...

 

[JvM]

I just made a command button and still the form doesn't refresh to allow a second search?

Please help...

 

[bradcccs]

Jan,

Are you using the code I posted, or your own code?

Please repost your code (or .mdb) and I will try to assist.

In regards to the focus error, I would suggest that this is occuring due to your subform being hidden (not visible).

Brad.

 

[bradcccs]

Perhaps the following may help.

I have rehashed your example with my code.

This is not to say that it is exactly correct, but . . .

Note: I have removed the IsNull testing and made the subform visible.

It is possible to test for Nulls, and hide the subform if you really wish.

I would consider using the * search if a null keyword is entered (ie: return all records)

Brad.

 

[JvM]

Great that you responded!!!

I'll post the dbase again..

Thanks for helping me out!! I really appriciate it!..

Greetings

 

[bradcccs]

Jan,

Sorry to mislead you in the previous posts.
The main problem is not due to a label being used instead of a command button.

The actual (or Main) problem with not allowing the second search, is due to writing to the query with the method you are using.

If you check out the re-work of your example above, you will see I have used a different method.

If you can not make sense of my re-work, then let me know.

Brad.

 

[JvM]

Hay Brad,

Thank you very much for your help..

The form works like a charm. I can even make it unvisible or visible when making a search..
Great piece of code. With some deep thinking I can follow it.

Again thank you very much for bothering..


Maybey t'ill a next time?

With kind greets from the Netherlands,

Jan