Dazed and Confused....Query Criteria with VBA

[systemx]

Hi all,

I am lost and in need of help.

I have designed a form with a basic search function. It uses the method in the simple search example in this forum.

Basically, a listbox containing the values, requeries based on a textbox string. Using -

Like "*" & Control & "*"

I want to expand this. My listbox contains multiple values - and I would like to be able to query based on any of these values.

I have built a combobox so the user can select the field that they want the search to be based upon.

Assuming I have the right approach - I now need an 'OnChange' event for the combobox to update the query criteria.

eg.

Private Sub UpdateMyQueryCriteria()
Dim MyField As String

MyField = Me.CBO.Value

SELECT MyField FROM MyQuery

SET Criteria = "Like & Chr(34) &"*"& Chr(34) & "& Control &" & Chr(34) &"*" & Chr(34)"

End Sub


Hopefully this explains what I am trying to do. Could anyone offer me any advice how to get this working - or even whether this is the correct approach to the problem?

Thank you

A rather desperate Rob

 

[KeithG]

You want your user to be able to select more than one entry from the listbox and then use a query to bring back result from the selections from the listbox?

 

[systemx]

Hi Keith,

Sorry if my original post was not clear

The listbox on my form looks up values based on a query. The listbox also displays multiple values on each row.

For instance, lets say my query is based on 'First Name' and 'Surname'. Both values are displayed in the listbox.

ie
________________
|John....|....Smith|
|Sally....|....Jones|
________________

I have a text box on the form. When a value is typed into the textbox, the OnChange event will requery the listbox.

In the query - currently the criteria for 'First Name' is -

Like "*" & MyFrmTextBox & "*"

So, as the user begins to type something into the textbox - the results in the listbox are immediately filtered based on what has been type in.

The objective I am trying to achieve - is that the 'filter' or 'search' is based on not only the first name, but also the surname.

In writing this again....I think I may have answered my own question. When I am back at work tomorrow - I will write an expression in the query (instead of a field criteria) - something along the lines of -

( FirstName Is Like *"* & MyFrmTextBox & *"* ) OR ( Surname Is Like *"* & MyFrmTextBox & *"* )

I'm thinking that will probably do the trick and am now feeling bloody stupid for posting in the first place.

But....my question....was essentially - is there a way to modify the query criteria using VBA in an OnChange event?

In my rather poor example on my first post -

If I add a CBO so the person can 'select' the field they want to filter on...

If CBO Value is FirstName then criteria = ( FirstName Is Like......)

Else

criteria = ( Surname Is Like......)

Hope this makes a little more sense. Sorry for my rather lengthy and at times abstract explanations! Hopefully this improves with experience

Cheers

Rob