Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Extended ListBox query criteria

Discussion in 'Information Technology' started by Adam@nospam.com, Jul 28, 2006.

  1. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    India Remand of Punjab Chief Parliamentary Secy Extended Breaking News May 7, 2011
    1984 SC stays Sajjan Kumar's trial in 1984 anti-Sikh riot case (extended coverage) History of Sikhism Aug 15, 2010
    Sikh Coalition Sikh Coalition: First Annual Diversity Video Competition (Deadline Extended) Sikh Organisations Aug 13, 2010
    World Armed Forces Sikh Conference Fosters ’Sense of Extended Family’ Breaking News May 19, 2010
    Sikh Coalition Be a Part of History, Today! Summer Internship Deadline Extended Sikh Organisations Apr 13, 2010

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    news:1151489001.638747.210240@x69g2000cwx.googlegroups.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. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    > news:1151489001.638747.210240@x69g2000cwx.googlegroups.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. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

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

    Adam@nospam.com
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    > news:1151490097.875301.141820@p79g2000cwp.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > >> news:1151489001.638747.210240@x69g2000cwx.googlegroups.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. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    news:1151571007.484087.156170@i40g2000cwc.googlegroups.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" <adamevans81@gmail.com> wrote in message
    >> news:1151490097.875301.141820@p79g2000cwp.googlegroups.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" <adamevans81@gmail.com> wrote in message
    >> >> news:1151489001.638747.210240@x69g2000cwx.googlegroups.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
    >> >> >
    >> >

    >
     
  8. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    > news:1151571007.484087.156170@i40g2000cwc.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > >> news:1151490097.875301.141820@p79g2000cwp.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > >> >> news:1151489001.638747.210240@x69g2000cwx.googlegroups.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
    > >> >> >
    > >> >

    > >
     

Share This Page