Using a combo box to search form 1

[stuandwil]

I want to be able to search a field called problem for any word written in a combo box. The best I have been able to do is create the combo and run a parameter query that prompts for keyword. However I can only get it to work by adding the * i.e. *fault*, which means the user has to type in the * which I know they will not get. Any ideas how to just get the prompt for a keyword and being able to just type fault.

 

[THWatson]

staundwil
You could try using this as the parameter in your query...

Like "*" & [Enter keyword] & "*"

Tom

 

[stuandwil]

Many thanks, thats it I have messed about with every combination to try and get what you achieved. Got the "" in the wrong places.
Cheers much appreciated

 

[stuandwil]

If a user does not enter any text in the parameter query is there any way to prompt them for a value else cancel the request.

 

[THWatson]

staundwil
I'm assuming you have a command button that fires the parameter query.

You could put in something such as...
Dim stDocName As String
stDocName = "YourQueryName"
DoCmd.OpenQuery stDocName, acNormal, acEdit
If IsNull(Me.YourComboBox.Column(?)) Then
DoCmd.Close acQuery, stDocName
MsgBox "Please enter text to search"
End If

Tom

 

[stuandwil]

I have a button that opens the form which contains the combo with the parameter query. When I click the button I get the parameter query "Enter Keyword", if a word is input the form is opened, combo drops down with the list of problem fields containing the chosen word. If when the parameter query pops up i just click on OK I get all records which is OK, but if i click on cancel the form opens with nothing in the combo. I want the form not to open if cancel is chosen.

If this is to much to ask don't worry, I'm happy with what I have achieved already with your help.

 

[THWatson]

staundwil
I have to go for a couple of hours. I'll look at it later. What you ask should be doable.

Tom

 

[THWatson]

staundwil
Okay, I've been a-thinkin' a bit.

If I understand you correctly, here's the order of things...

1. From form1, you press a command button that opens form2
2. Form2 has the combo box with a parameter query as the Row Source.
3. The parameter query runs.
4. The combo box drops down with the list of problem fields containing the keyword.
5. If you click OK when the parameter query runs, you get all the records, and that's fine.
BUT if you press the Cancel button you want the form not to open.

It's this last part I don't quite understand. How can you get the "form not to open" when it's already opened before the parameter query runs?

Maybe I'm missing something.

Tom

 

[stuandwil]

The form is specifiaclly for searching for keyword if one is not entered I want the form to close. Maybe not possible.

Stuart

 

[THWatson]

Stuart
Well, maybe the difference between "closing" and "not opening" is minor.

In any event, I added this to code that opens the form and runs the query, and it works here. Play around with it if necessary, to get the form or query name right.

If OnKeyPress = Chr$(27) Then
    DoCmd.Close acQuery, stDocName
    End If

This checks to see if the Esc key was pressed.

See if that does it.

Tom

 

[stuandwil]

many thanks