asked on

Wildcard Query in VBA

I am attempting to use VBA to get a continuous form to open using a wildcard query. Specifically, I am trying to set things up so that the user inputs a search term (partial company name) into a textbox on a form and by clicking a "search" button opens a continuous form with matches to the search term (in any position). I have tried the following code which opens the form, but no search results appear:

DoCmd.OpenForm " CF_Companies", , , "CompanyName Like '*" & SearchTextBox & "*'"

I have also tried numerous variations of the above, but alas only get synthax errors. Can anyone help point me in the right direction ? Thank you !

rspahitz

Do you get results if you open a new Query window and select the following in SQL:

SELECT * FROM CF_Companies
WHERE CompanyName Like '*XXX*'

where XXX is a typical search term.
If you don't get anything here, the problem is not your Open command.

Mike Eghtebas

side note: If field [CompanyName] is indexed for faster search, then use of the first * will drastically slow down the search.

Mike

Paul McCabe

ASKER

I tried the query suggested by rspahitz and it works perfectly (CF_Companies is a continuous form, so I ran the query against the underlying table).

rspahitz

Sorry...you're right...should have been against the table.

How about this as a test. Open the form then put that query in the Record Source of the form.
If that works, it may want more...maybe you'll need to use SearchTextBox.Value (append ".Value") to the query since it may be getting confused with the reference.

Paul McCabe

ASKER

Not sure if I understood this correctly. By "form" I take it you mean CF_Companies. The successful query I ran against the table was:
SELECT * FROM T_Companies WHERE CompanyName Like '*XXX*'
but putting this into the record source of CF_Companies leads to a syntax error....

rspahitz

OK, maybe the next step is to go into the VBA.
Do you know how to set breakpoints? If you set one on that line, check the value of "CompanyName Like '*" & SearchTextBox & "*'"

Alternately, on the line before you can put this:

Debug.Print ">CompanyName Like '*" & SearchTextBox & "*'<"

After it runs, open the immediate window (Alt+F11, I think) and see what shows to make sure it's what you expected.

Paul McCabe

ASKER

I ran
Debug.Print ">CompanyName Like '*" & SearchTextBox & "*'<"
in the immediate window, which returned the result:
>CompanyName Like '**'<
Does this make sense ? I am afraid to say I am not familiar with breakpoints.

ASKER CERTIFIED SOLUTION

rspahitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paul McCabe

ASKER

To answer PatHartman's question, CompanyName is definitely the actual name of the relevant column (I have checked this at least 10 times !), and the continious form is based on a query, not a table. For reference, the text box on the main form into which the search term is input is not bound. I tried binding it but that didn't change anything. In end, I decided to try InputBox for inputting the search terms, and this is now working fine. So I will have users input search terms using InputBox. Your comments did'nt lead to a direct solution, but prompted me to try other things and find an acceptable solution. Thank you for your time and help !

PatHartman

I'm glad you got it working but there shouldn't be any difference between using a textbox on a form and an inputbox except that with the textbox on the form, you get more flexibility.