Extended ListBox query criteria

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

Hi All,

I have a form 'CSS Search frm' and on the form I've placed a Listbox
'AdvisorList'.

What I would like is to be able to select multiple values from this
listbox and have a query look for the multiple values selected in the
listbox, otherwise if the listbox is blank then dont search for
anything there!

I've got a query 'CSS Case qry' and want to enter such a criteria into
'LoggedBy' field.

Is there a way I can do this using a query criteria?

Help doesnt seem to help me much on this.

Many Thanks!

Adam
 
Hi Brendan,

I'm not very good with code just yet. I'll try to understand this
though..

Does this go into a module?
 
If you expect to call the code from more than one form or report, you could
put it in a public procedure in a standard module. But if you will only call
it from one form or report, you can put it into the module for that form or
report. For example, if you open the query or a report bound to the query by
clicking a command button on the same form that contains the list box, you
could put the code into the Click event of the command button.
Alternatively, if you are opening a report bound to the query, you could put
the code into the Open event of the report.

BTW: There appears to be a slight typo in that example. Where it says 'Set
frm = Form!frmMyForm' I expect the author intended to write 'Set frm =
Forms!frmMyForm' - note 'Forms', plural, not 'Form', singular.

Don't forget to replace the form, listbox, table and field names with your
own names.

I have to go off-line for a while now, but if you can't get it to work, post
a reply to this thread and I'll try to go into a little more detail when I
get back.
 
Ok, how do you put this into your query?

In the query I have other criteria however dont know how to add this
into the existing query?

Also in the code there's a section I dont understand:

strSQL = "Select * from Employees where [EmpID]="

What does Employees link too and what is EmpID referenced too?


Many Thanks

Adam
 
Usually, I would not open a query. I would use code like that in the example
to build a SQL string, and assign the SQL string to the Record Source
property of a form or report, or perhaps to the Row Source property of a
combo box or list box. Another Access MVP, Ken Snell, has an example of
using a multi-select combo box (as well as other controls) to filter a form
or report, at the following URL ...

http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

If you really want to open a query directly, though, here's a modification
that would work. Here's the VBA code, that would go in the Click event
procedure of a command button on the same form as the list box ...

Private Sub cmdTest_Click()

Dim ctl As Control
Dim varItem As Variant
Dim strMatch As String

Set ctl = Me.lstTest
If ctl.ItemsSelected.Count > 0 Then
strMatch = ";"
For Each varItem In ctl.ItemsSelected
strMatch = strMatch & ctl.ItemData(varItem) & ";"
Next varItem
Me.txtTest = strMatch
DoCmd.OpenQuery "qryTest"
Else
MsgBox "Please select at least one item from the list."
End If

End Sub

In this example, 'lstTest' is the name of the list box, 'txtTest' is the
name of a text box on the same form (you can hide this text box if you like,
change the Visible property of the check box to 'No' in the Properties
window), and 'qryTest' is the name of the query we want to open.

Here's the SQL from the query ...

SELECT tblTest.*
FROM tblTest
WHERE (((InStr(1,[Forms]![frmTest]![txtTest],";" & [TestText] & ";"))<>0));

I put together a little demo of this which you can find at the following URL
....

http://brenreyn.brinkster.net/multiselectdemo.zip

--
Brendan Reynolds
Access MVP


[email protected] said:
Ok, how do you put this into your query?

In the query I have other criteria however dont know how to add this
into the existing query?

Also in the code there's a section I dont understand:

strSQL = "Select * from Employees where [EmpID]="

What does Employees link too and what is EmpID referenced too?


Many Thanks

Adam
Click to expand...
 
That does the trick!

Thank you!


Brendan said:
Usually, I would not open a query. I would use code like that in the example
to build a SQL string, and assign the SQL string to the Record Source
property of a form or report, or perhaps to the Row Source property of a
combo box or list box. Another Access MVP, Ken Snell, has an example of
using a multi-select combo box (as well as other controls) to filter a form
or report, at the following URL ...

http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

If you really want to open a query directly, though, here's a modification
that would work. Here's the VBA code, that would go in the Click event
procedure of a command button on the same form as the list box ...

Private Sub cmdTest_Click()

Dim ctl As Control
Dim varItem As Variant
Dim strMatch As String

Set ctl = Me.lstTest
If ctl.ItemsSelected.Count > 0 Then
strMatch = ";"
For Each varItem In ctl.ItemsSelected
strMatch = strMatch & ctl.ItemData(varItem) & ";"
Next varItem
Me.txtTest = strMatch
DoCmd.OpenQuery "qryTest"
Else
MsgBox "Please select at least one item from the list."
End If

End Sub

In this example, 'lstTest' is the name of the list box, 'txtTest' is the
name of a text box on the same form (you can hide this text box if you like,
change the Visible property of the check box to 'No' in the Properties
window), and 'qryTest' is the name of the query we want to open.

Here's the SQL from the query ...

SELECT tblTest.*
FROM tblTest
WHERE (((InStr(1,[Forms]![frmTest]![txtTest],";" & [TestText] & ";"))<>0));

I put together a little demo of this which you can find at the following URL
...

http://brenreyn.brinkster.net/multiselectdemo.zip

--
Brendan Reynolds
Access MVP


[email protected] said:
Ok, how do you put this into your query?

In the query I have other criteria however dont know how to add this
into the existing query?

Also in the code there's a section I dont understand:

strSQL = "Select * from Employees where [EmpID]="

What does Employees link too and what is EmpID referenced too?


Many Thanks

Adam


Brendan said:
If you expect to call the code from more than one form or report, you
could
put it in a public procedure in a standard module. But if you will only
call
it from one form or report, you can put it into the module for that form
or
report. For example, if you open the query or a report bound to the query
by
clicking a command button on the same form that contains the list box,
you
could put the code into the Click event of the command button.
Alternatively, if you are opening a report bound to the query, you could
put
the code into the Open event of the report.

BTW: There appears to be a slight typo in that example. Where it says
'Set
frm = Form!frmMyForm' I expect the author intended to write 'Set frm =
Forms!frmMyForm' - note 'Forms', plural, not 'Form', singular.

Don't forget to replace the form, listbox, table and field names with
your
own names.

I have to go off-line for a while now, but if you can't get it to work,
post
a reply to this thread and I'll try to go into a little more detail when
I
get back.

--
Brendan Reynolds
Access MVP

Hi Brendan,

I'm not very good with code just yet. I'll try to understand this
though..

Does this go into a module?


Brendan Reynolds wrote:

There's an example of this at the following URL ...

http://www.mvps.org/access/forms/frm0007.htm

--
Brendan Reynolds
Access MVP

Hi All,

I have a form 'CSS Search frm' and on the form I've placed a Listbox
'AdvisorList'.

What I would like is to be able to select multiple values from this
listbox and have a query look for the multiple values selected in
the
listbox, otherwise if the listbox is blank then dont search for
anything there!

I've got a query 'CSS Case qry' and want to enter such a criteria
into
'LoggedBy' field.

Is there a way I can do this using a query criteria?

Help doesnt seem to help me much on this.

Many Thanks!

Adam
Click to expand...
Click to expand...
Click to expand...
 
Back
Top