Extended ListBox query criteria

#1 (**permalink**) 28-Jul-2006, 08:27 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12231-extended-listbox-query-criteria.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231
'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

#2 (**permalink**) 28-Jul-2006, 08:27 AM

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

http://www.mvps.org/access/forms/frm0007.htm
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231

--
Brendan Reynolds
Access MVP

"Adam@nospam.com" wrote in message
news:1151489001.638747.210240@x69g2000cwx.googlegr oups.com...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231
> 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
>

#3 (**permalink**) 28-Jul-2006, 08:27 AM

Hi Brendan,

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

Does this go into a module?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231

Brendan Reynolds wrote:

> There's an example of this at the following URL ...
>
> http://www.mvps.org/access/forms/frm0007.htm
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231
>
> --
> Brendan Reynolds
> Access MVP
>
> "Adam@nospam.com" wrote in message
> news:1151489001.638747.210240@x69g2000cwx.googlegr oups.com...
> > 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
> >

#4 (**permalink**) 28-Jul-2006, 08:27 AM

If you expect to call the code from more than one form or report, you could
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231
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 =
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231
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

"Adam@nospam.com" wrote in message
news:1151490097.875301.141820@p79g2000cwp.googlegr oups.com...
> 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
>>
>> "Adam@nospam.com" wrote in message
>> news:1151489001.638747.210240@x69g2000cwx.googlegr oups.com...
>> > 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
>> >
>

#5 (**permalink**) 28-Jul-2006, 08:28 AM

Ok, how do you put this into your query?

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231
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 Reynolds wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231
> 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
>
> "Adam@nospam.com" wrote in message
> news:1151490097.875301.141820@p79g2000cwp.googlegr oups.com...
> > 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
> >>
> >> "Adam@nospam.com" wrote in message
> >> news:1151489001.638747.210240@x69g2000cwx.googlegr oups.com...
> >> > 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
> >> >
> >

#6 (**permalink**) 28-Jul-2006, 08:28 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231

--
Brendan Reynolds
Access MVP

"Adam@nospam.com" wrote in message
news:1151571007.484087.156170@i40g2000cwc.googlegr oups.com...
> 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 Reynolds wrote:
>
>> 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
>>
>> "Adam@nospam.com" wrote in message
>> news:1151490097.875301.141820@p79g2000cwp.googlegr oups.com...
>> > 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
>> >>
>> >> "Adam@nospam.com" wrote in message
>> >> news:1151489001.638747.210240@x69g2000cwx.googlegr oups.com...
>> >> > 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
>> >> >
>> >
>

#7 (**permalink**) 28-Jul-2006, 08:28 AM

That does the trick!

Thank you!

Brendan Reynolds wrote:

> 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
>
>
> "Adam@nospam.com" wrote in message
> news:1151571007.484087.156170@i40g2000cwc.googlegr oups.com...
> > 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 Reynolds wrote:
> >
> >> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231
> >> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12231
> >> '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
> >>
> >> "Adam@nospam.com" wrote in message
> >> news:1151490097.875301.141820@p79g2000cwp.googlegr oups.com...
> >> > 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
> >> >>
> >> >> "Adam@nospam.com" wrote in message
> >> >> news:1151489001.638747.210240@x69g2000cwx.googlegr oups.com...
> >> >> > 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
> >> >> >
> >> >
> >