Solved Search word is not available (1 Viewer)

[Alejo]

Hi,

I created a Search box and search button that is working fine when the word to search is available.
But when the word to search is not available the Form page will be displayed in blank and to return to original page I have to tick the "toggle filter"(below screenshot).

Can some please help, when the word to search is not available, the form page will stay same and a pop up like "Not Available" will display.

 

[Gasman]

Use DLookUp() or DCount() to check before setting the filter?

 

[Alejo]

Hi Gasman,

Thanks for the reply

I'm still new in access, still learning and currently using limited functions...In this case I use the Macro and put value as below:

Can you be specific where should I use DLookUp() or DCount() in this Macro, or any code that will help me to simply it.

 

[Gasman]

I do not use macros, sorry. Most people here do not either.
I would use VBA and in the click event something along the lines of (air code)

Dim lngCount as Long
dim strCriteria as String

strCriteria = "[BaseNumber] Like '*" & [Formss]![Form1![S} & *'" OR [BaseTitle] Like '*" &  "whatever you had next as it does not display" & "*'"
lngCount = DCount("*", "YourTableName", strCriteria)
If lngCount > 0 then
    Me.filter = strCriteria
    Me.FilterOn = True
Else
    MSGBOX "Not Available"
End If

HTH

 

[Alejo]

I do not use macros, sorry. Most people here do not either.
I would use VBA and in the click event something along the lines of (air code)

Dim lngCount as Long
dim strCriteria as String

strCriteria = "[BaseNumber] Like '*" & [Formss]![Form1![S} & *'" OR [BaseTitle] Like '*" &  "whatever you had next as it does not display" & "*'"
lngCount = DCount("*", "YourTableName", strCriteria)
If lngCount > 0 then
    Me.filter = strCriteria
    Me.FilterOn = True
Else
    MSGBOX "Not Available"
End If

HTH

Hi,

1. There was a pop-up error on first line that you provided like missing bracket, wrong spelling so I revised it as below:
strCriteria = "[BaseNumber] Like '*" & [Forms]![Form1]![S} & *'" OR [BaseTitle] Like '*" ]

The revised code can filter the search word but there is pop-up error as below:

Run-time error '2465'
Microsoft Access can't find the filed 'S} & *'" OR [BaseTitle] Like '*"

2. In this second line : lngCount = DCount("*", "YourTableName", strCriteria)
Which "YourTableName" you mean:

Below are the available field/texbox, are you pertaining to one of this?

BaseNumber - Data/Type - Text
BaseTitle - Data/Type - Text
S - the Search Box






Below is the latest VBA code that I tried. Note that I retain the "YourTableName",
**********************************************************************************************

Private Sub cmdS_Click()
Dim lngCount As Long
Dim strCriteria As String

strCriteria = "[BaseNumber] Like '*" & [Forms]![Form1]![S} & *'" OR [BaseTitle] Like '*" ]
lngCount = DCount("*", "YourTableName", strCriteria)
If lngCount > 0 Then
Me.Filter = strCriteria
Me.FilterOn = True
Else
MsgBox "Not Available"

End If

End Sub

Note: The MsgBox "Not Available" still doesn't pop-up (as might be due to missing information)

 

[Gasman]

Please post code within code tags and also indent your code, as I did when I posted that untested code.
It is difficult to spot single quotes with double quotes otherwise.?

If you filter to BaseTitle like *, I would have thought that would give you every record?, so try just using

strCriteria = "[BaseNumber] Like '*" & [Formss]![Form1![S] & "*'"

I had a } bracket in error.
You have to substitute YourTableName for your actual table name that holds those fields? There was a clue in what I named that parameter?

However, if YourTableName table does not exist, you would have received an error, so not sure what you are actually doing there, as I doubt you would have a table called exactly that?

 

[Alejo]

Hi Gasman,

I'm not getting it, sorry as mentioned I new in this field.

Can you pleases assist me by step. I want to do it by step.
First I want to establish a code to search a "word" that is available in the field .

I tried several combination but still end error, please help to correct the code.

This code has this error:

 

[cheekybuddha]

You really don't need to worry about brackets here since none of the field names/ control names contain spaces.

Try:

Dim lngCount as Long
dim strCriteria as String

strCriteria = "BaseNumber LIKE '*" & Forms.Form1.S & *' OR BaseTitle LIKE '*" &  "whatever you had next as it does not display" & "*'"
lngCount = DCount("*", "YourTableName", strCriteria)
If lngCount > 0 then
    Me.filter = strCriteria
    Me.FilterOn = True
Else
    Me.FilterOn = False
    MSGBOX "Not Available"
End If

If button "cmdS" and control (textbox?) "S" are on Form1 then you can use:

' ...
strCriteria = "BaseNumber LIKE '*" & Me.S & *' OR BaseTitle LIKE '*" &  "whatever you had next as it does not display" & "*'"
' ...

 

[Gasman]

Please do not try 'several combinations' if you do not know what you are doing.
Just report back as what does not work.?

I cannot see that the error would be on that line?
I tested my syntax for one field and that worked.

Please copy and post the code you have now within codetags.
I know they say a picture tells a thousand words, but not so much with computing?

 

[cheekybuddha]

strCriteria = "[BaseNumber] Like '*" & [Formss]![Form1![S] & "*'"

This line from Post#6 has errors (2 's' in Formss, and missing closing square bracket after Form1)

Looks like the OP caught the double 's', but missed the missing baracket.

 

[Gasman]

This line from Post#6 has errors (2 's' in Formss, and missing closing square bracket after Form1)

Looks like the OP caught the double 's', but missed the missing baracket.

Yes, my errors are not helping.
As only a picture was supplied I had to type it all in, and that was one of my typos.
I did say aircode though?

 

[Alejo]

Hi,

I used the last message code of [U]cheekybuddha.[/U] You are right , "cmdS" and control (textbox?) "S" are on Form1
Search button is the cmdS

Below is the code I used:

Private Sub cmdS_Click()
Dim lngCount As Long
Dim strCriteria As String

strCriteria = "BaseNumber LIKE '*" & Me.S & *' OR BaseTitle LIKE "*'"

lngCount = DCount("*", "Table1", strCriteria)
If lngCount > 0 Then
Me.Filter = strCriteria
Me.FilterOn = True
Else
Me.FilterOn = False
MsgBox "Not Available"
End If
End Sub

And this is error:

 

[cheekybuddha]

What do you want to match BaseTitle to? To Me.S as well?

Try:

' ...
strCriteria = "BaseNumber LIKE '*" & Me.S & "*' OR BaseTitle LIKE '*" & Me.S & "*'"
' ...

Or

' ...
strCriteria = "BaseNumber LIKE '*" & Me.S & "*'"
' ...

 

[Gasman]

Hi,

I used the last message code of [U]cheekybuddha.[/U] You are right , "cmdS" and control (textbox?) "S" are on Form1
Search button is the cmdS
View attachment 92606


Below is the code I used:



And this is error:
View attachment 92605

And you started trying combinations again?

 

[Alejo]

Code

Thank you very much. It worked.

When the word is not available it pop-up "Not Available" and this is enough.

Just for my curiousity, is it possible to have a pop-up of the word itself.

For example: If I search for the word "Hello" and it is not available....
The pop-up should be..."Hello is not available"

 

[Alejo]

trying combinations

Hi Gasman,

I just used the last suggestion in the forum, I stop combining codes as you suggested.

 

[Gasman]

Well you should be able to work out how you concatenate strings, from the code already supplied?

 

[cheekybuddha]

All our previous suggestions had errors of one sort or another, missing brackets here, missing quotes there.

I wonder if the forum editor software was mucking around unnoticed - I've noticed it sometimes adds matching quote marks / brackets.

 

[Alejo]

Hi Gasman and [U]cheekybuddha,[/U]

I would like to thank both of you. Being new in this forum and in access (especially in vba codes) your patience is very much appreciated.

Good day!!!

 

[cheekybuddha]

Just for my curiousity, is it possible to have a pop-up of the word itself.

For example: If I search for the word "Hello" and it is not available....
The pop-up should be..."Hello is not available"

Yes,

Adjust to:

' ...
If lngCount > 0 then
    Me.filter = strCriteria
    Me.FilterOn = True
Else
    Me.FilterOn = False
    MSGBOX "'" & Me.S & "' - Not Available"
End If
' ...