Use Query search in Navigation form

[Kev_1988]

Hello i'm using an query based search form in a navigation form.
But i'm getting stuck with searching with multiple keywords.

like i have 3 different search bars, where you can search very specific.

Product name:........

category:.......

compound:........

Code i'm using for only 1 keyword is:

Private Sub Command10_Click()
DoCmd.OpenForm "Product_DS", acNormal, , "[productname] Like '" & Me.txtproductname & "*'"
End Sub

And this works fine for just 1 search bar.


What kind of code do i need in order to use all 3 search bars and let it work?
I've tried like this for 2 search bars but I get error code 13....:

Private Sub Command10_Click()
DoCmd.OpenForm "Product_DS", acNormal, , (("[productname] Like '" & Me.txtproductname & "*'") Or ("[category] Like '" & Me.txtcategory & "*'"))
End Sub

I'm stuck:banghead:
Please help me out thanks

Note: i'm a beginner access user, and don't have knowledge about vba or codes

 

[pbaldy]

The quotes where the two get joined are wrong. Try

DoCmd.OpenForm "Product_DS", acNormal, , "[productname] Like '" & Me.txtproductname & "*' Or [category] Like '" & Me.txtcategory & "*'"

 

[Kev_1988]

Thanks a lot!! I can continue

The quotes where the two get joined are wrong. Try

DoCmd.OpenForm "Product_DS", acNormal, , "[productname] Like '" & Me.txtproductname & "*' Or [category] Like '" & Me.txtcategory & "*'"

 

[Kev_1988]

For two search bars it works great!!
But what code do i need t use if i want use like six search bars...?

code like this??:
DoCmd.OpenForm "Product_DS", acNormal, , "[productname] Like '" & Me.txtproductname & "*' Or [category] Like '" & Me.txtcategory & "*' or [test3] Like '" & Me.txttest3 & "*' or [test4] Like '" & Me.txttest4 & "*' or [test5] Like '" & Me.txttest5 & "*' or [test6] Like '" & Me.txttest6 & "*'"

Thanks for helping

The quotes where the two get joined are wrong. Try

DoCmd.OpenForm "Product_DS", acNormal, , "[productname] Like '" & Me.txtproductname & "*' Or [category] Like '" & Me.txtcategory & "*'"

 

[pbaldy]

At a glance that looks okay. Does it error, and if so what's the error? The numbered fields imply a normalization issue.

 

[The_Doc_Man]

When you start adding up search bars like that, you reach a point of no return called "I'm SO confused." You might wish to rethink the approach to this design. It may be daunting because you are not comfortable with VBA, but there has to be a starting point somewhere. This might be yours.

When you open your form, do you have a separate "Open the Form Already" button?

If so, then there will be a button-click event. If you used a wizard to build the form-launcher button, there will be code in it to include your filter.

Create a string that will be the source of your filter. Your button-click code might RESEMBLE this snippet (but you will have to use your control names in place of what I am showing, of course).

Private Sub OpentheForm_Click()

Dim sFilter as String

... (whatever else might be needed first)

sFilter = ""

IF Trim( [SrchBox1] ) <> "" THEN sFilter = "( [ProductName] Like '" & [SrchBox1] & "*' )"

IF Trim( [SrchBox2] ) <> "" THEN
    IF sFilter <> "" THEN sFilter = sFilter & " OR "
    sFilter = sFilter & "( [Category] Like '" & [SrchBox2] & "'* ) "

(lather, rinse, repeat...)

DoCmd.OpenForm "Product_DS", acNormal, , sFilter

This way, your users can select whatever they want to see based on the preset boxes.

Now... do you want to spiffy this up a bit so that users can only select for things that actually appear in the tables? Or do you want it to be totally free-form with the chance that nothing will be selected because they can't type?

If you want to actually show them only things that potentially exist to be selected, make your text boxes become COMBO boxes for which the .RowSource is a type-in query, "SELECT DISTINCT [fieldname] FROM MyTable ;" where fieldname is (from your example) Category or ProductName or whatever.

 

[Kev_1988]

Doesn't give any error. But it doesn't work either

At a glance that looks okay. Does it error, and if so what's the error? The numbered fields imply a normalization issue.

 

[Kev_1988]

No I don't have a separate "open the form" button
But I've create one and named "opentheform in property sheet
Then I've copied your code like this:

) <> "" Then sFilter = "( [productname] Like '" & [txtproductname] & "*' )"

If Trim([txtCategory]) <> "" Then
If sFilter <> "" Then sFilter = sFilter & " OR "
sFilter = sFilter & "( [category] Like '" & [txtCategory] & "'* ) "

If Trim([txtstate]) <> "" Then
If sFilter <> "" Then sFilter = sFilter & " OR "
sFilter = sFilter & "( [state] Like '" & [txtstate] & "'* ) "

If Trim([txtCountry]) <> "" Then
If sFilter <> "" Then sFilter = sFilter & " OR "
sFilter = sFilter & "( [country] Like '" & [txtCountry] & "'* ) "

If Trim([txtcolor]) <> "" Then
If sFilter <> "" Then sFilter = sFilter & " OR "
sFilter = sFilter & "( [color] Like '" & [txtcolor] & "'* ) "
End If
End

DoCmd.OpenForm "Product_DS", acNormal, , sFilter
End Sub

And I get a error:
block if without end if

I only want users to search in this case...
I made a form with 5 search bars to search specific item, so you type a few keywords in the different search bars (e.g. product name: adaptor556, Country: USA, Color: black) or (e.g. Product name: adaptor721 Category: elektronic, Country: USA, state: california, Color: blue) and so on

don't know what to do:banghead:

When you start adding up search bars like that, you reach a point of no return called "I'm SO confused." You might wish to rethink the approach to this design. It may be daunting because you are not comfortable with VBA, but there has to be a starting point somewhere. This might be yours.

When you open your form, do you have a separate "Open the Form Already" button?

If so, then there will be a button-click event. If you used a wizard to build the form-launcher button, there will be code in it to include your filter.

Create a string that will be the source of your filter. Your button-click code might RESEMBLE this snippet (but you will have to use your control names in place of what I am showing, of course).

Private Sub OpentheForm_Click()

Dim sFilter as String

... (whatever else might be needed first)

sFilter = ""

IF Trim( [SrchBox1] ) <> "" THEN sFilter = "( [ProductName] Like '" & [SrchBox1] & "*' )"

IF Trim( [SrchBox2] ) <> "" THEN
    IF sFilter <> "" THEN sFilter = sFilter & " OR "
    sFilter = sFilter & "( [Category] Like '" & [SrchBox2] & "'* ) "

(lather, rinse, repeat...)

DoCmd.OpenForm "Product_DS", acNormal, , sFilter

This way, your users can select whatever they want to see based on the preset boxes.

Now... do you want to spiffy this up a bit so that users can only select for things that actually appear in the tables? Or do you want it to be totally free-form with the chance that nothing will be selected because they can't type?

If you want to actually show them only things that potentially exist to be selected, make your text boxes become COMBO boxes for which the .RowSource is a type-in query, "SELECT DISTINCT [fieldname] FROM MyTable ;" where fieldname is (from your example) Category or ProductName or whatever.

 

[The_Doc_Man]

block if without end if

All that means is that you have an If...Then...Else...End If multi-line block (a "Block If") that is missing an "End If" statement. No need to panic. I see that what I told you was missing an End If for the [SrchBox2] case, which could be an error of omission on my part. I made an incorrect assumption that you might understand the End If was required, but maybe not.

Look for an IF ... THEN sequence that has multiple lines following it but doesn't have an END IF behind the last line of the conditional sequence. Remember that End If DOES have a space in it, unlike GoTo, which is also two words but DOESN'T have a space for the VBA verb.

That in-line IF that adds an " OR " to the accumulating string shouldn't give you a problem, but you could change it to

If sFilter <> "" THEN
    sFilter = sFilter & " OR "
End If

That would be unequivocal.

One other common error with multiple If-blocks - don't let them partially overlap. That would also do it.

 

[Kev_1988]

All that means is that you have an If...Then...Else...End If multi-line block (a "Block If") that is missing an "End If" statement. No need to panic. I see that what I told you was missing an End If for the [SrchBox2] case, which could be an error of omission on my part. I made an incorrect assumption that you might understand the End If was required, but maybe not.

Look for an IF ... THEN sequence that has multiple lines following it but doesn't have an END IF behind the last line of the conditional sequence. Remember that End If DOES have a space in it, unlike GoTo, which is also two words but DOESN'T have a space for the VBA verb.

That in-line IF that adds an " OR " to the accumulating string shouldn't give you a problem, but you could change it to

If sFilter <> "" THEN
    sFilter = sFilter & " OR "
End If

That would be unequivocal.

One other common error with multiple If-blocks - don't let them partially overlap. That would also do it.

Oke now i think i've correct them like this

Code:

Private Sub Opentheform_Click()
Dim sFilter As String

sFilter = ""

If Trim([txtproductname]) <> "" Then
If sFilter <> "" Then sFilter = sFilter & " or "
sFilter = sFilter & "( [productname] Like '" & [txtproductname] & "*' )"
End If

If Trim([txtCategory]) <> "" Then
If sFilter <> "" Then sFilter = sFilter & " OR "
sFilter = sFilter & "( [category] Like '" & [txtCategory] & "*' ) "
End If

If Trim([txtstate]) <> "" Then
If sFilter <> "" Then sFilter = sFilter & " OR "
sFilter = sFilter & "( [state] Like '" & [txtstate] & "*' ) "
End If

If Trim([txtCountry]) <> "" Then
If sFilter <> "" Then sFilter = sFilter & " OR "
sFilter = sFilter & "( [country] Like '" & [txtCountry] & "*' ) "
End If


If Trim([txtcolor]) <> "" Then
If sFilter <> "" Then sFilter = sFilter & " OR "
sFilter = sFilter & "( [color] Like '" & [txtcolor] & "*' ) "
End If


DoCmd.OpenForm "Product_DS", acNormal, , sFilter
End Sub


But if I use the search bars then I get the next problem, namely I can use only 1 search bar at the time "e.g. search under Country: USA" gives everything related with USA, that works fine!
But if i want to use 2 search bars "e.g. search under Country: USA and Color: Black" it doesn't gives the result what I expected it only shows result of 1 search bar for example USA only and not the result USA and black ...:banghead:

It is possible to use all 5 search bars?

 

[Kev_1988]

Anyone who can help me with the problem what we've talked about??
Please help me
I'm stuk:banghead:

 

[pbaldy]

Can you attach your db here?

 

[Cronk]

Kev, your last post cited "Country: USA and Color: Black" whereas you have been using OR conditions previously from your initial post.

Sometimes to explain the difference between AND/OR to confused users, I'll say that the answer to the question "How many people live in USA and Canada?" is not 400 million but zero.

 

[The_Doc_Man]

The code you posted should work for returning everything that has USA and everything that has Black including cases that overlapped.

Did you want the implication to be "USA and Black" or did you want "Either USA or Black (or both)"? Or did you want a choice? If you wanted to choose between the AND or OR options,...

In this button-click event, you could add a radio button group on the form that has two choices - "AND" and "OR" with a default of one or the other, doesn't matter which one. Call it SelAndOr, if you like. Then add this to the top of that code you posted, just before the line sFilter = ""

Dim sRelation as String

SELECT CASE SelAndOr
    CASE 1  (or whatever value you associated with AND)
        sRelation = " AND "
    CASE 2  (or whatever value you associated with OR)
        sRelation = " OR "
END SELECT

... rest of the code

Now, for each of the IF-THEN-ELSE-END IF chunks, change the line that concatenates the " OR " string into the filter to instead use sFilter = sFilter & sRelation - which would then allow you to do either the AND or the OR of the individual filter options.

As to the problem of why the filter is only filtering based on USA but not on Black, put a breakpoint on the line that opens the form and see what is in sFilter at the time. See if some combination of events has partly erased the filter. Watch for extraneous punctuation that abnormally ends the line.

If it looks OK in the sFilter string, then I would check for stray punctuation and non-printing characters in the filter. For instance, if I were to code this with extreme OCD in mind (and adding the part about AND vs OR in the filter), application of one filter criterion would be similar to

If Trim([txtcolor]) <> "" Then
    If sFilter <> "" Then 
        sFilter = sFilter & sRelation
    End If
    sFilter = sFilter & "( [color] Like '" & [B]Trim([/B] [txtcolor] [B])[/B] & "*' ) "
End If

The second trim would take the case of extraneous spaces in the input, which would easily negate the effectiveness of the search.

 

[Kev_1988]

Can you attach your db here?

Oke I will do it.
So if you form Test Result then you get to see 5 different search bars.
What I want is for example:
--Product Name: Dev
--Category : Elektronic
--Color : White
Should give result: 1 only (DevBg2, elektronic, White) that's good....

But I can't get it done like this.
If I type "Dev" gives me 8 counts.
If I type "Dev" and "Elektronic" gives me 12 counts..... It supposed to give me only 3 counts (if you look it at the table)

How can we fix the code that works like the example??:banghead:

Thanks for helping

 

[pbaldy]

You have "OR" between tests, it sounds like you want "AND". Making that change returns 3 records given your example.

 

[Kev_1988]

You have "OR" between tests, it sounds like you want "AND". Making that change returns 3 records given your example.

Thanks it works!!

One more question, if I search under category and type only the word "Holder" gives me 8 hits
But it should be 9 hits because one of the cell contains "Electronic / Holder"
How can i fix this?

Thanks a lot
I've attached the file again with a small update in the first row you see under Category "Electronic / Holder"

 

[The_Doc_Man]

If I type "Dev" and "Elektronic" gives me 12 counts..... It supposed to give me only 3 counts (if you look it at the table)

If you give it only "Elektronic" how many counts does it give you? 4, perhaps?

Had you changed from "OR" to "AND" when you did those runs?

 

[pbaldy]

You only have a wildcard after, so hits have to start with the word. To find it anywhere:

sFilter = sFilter & "( [category] Like '*" & [txtCategory] & "*' ) "

 

[mjdemaris]

Try something like this:

If Not IsNull(Me.txtproductname) Then
      sFilter= sFilter& "([productname] = " & Me.txtproductname) & ") AND "
End If

Then just copy and edit the code for the remaining controls. Note, that I used the control names for the IF stmt to check and to pass the look up value.

You can add Trim if you want to remove spaces:

If Not IsNull(Trim(Me.txtproductname))

Hope this helps. Also, it is helpful to use camel casing like: txt.ProductName