Use Multi-Select List boxes as query parameters

[crtopher]

I may be trying to be too ambitious with my limited knowledge (because we
know that a little knowledge is a dangerous thing!).

I mastered Martin Greene's 'cascading list' idea, found here:
http://www.fontstuff.com/access/acctut10.htm

I had a combo that i selected a procedure category from...which changed the
rowsource of the next combo such that i could select procedures only from the
category selected in the previous combo. Now I want to up the ante!! I want
to use a list box on the main form to select procedure categories (one or
more), and transfer that selection to a subform control that selects
procedures, but the only procedures selectable are from the categories
selected in the main form list box. I need the list box and the subform
control to stay in synch between records!!

So i have the parent form called frmMain which writes to tblMain; the
subform control is called child 27; the subform is called frmCaseProcedure
which writes to tblCaseProcedure; tblCaseProcedure has a many to one
relationship with tblMain (ie each case can have more than one procedure)

the listbox on frmMain is lstCategory and it takes its rowsource as

SELECT lkupProceedureCategory.Category FROM lkupProceedureCategory ORDER BY
lkupProceedureCategory.Category;

the control on the subform is called cboProcedureID and at the moment it's
rowsource is:

SELECT qryProcedure.ProceedureID, qryProcedure.Proceedure,
qryProcedure.Category FROM qryProcedure WHERE
(((qryProcedure.Category)=Forms!frmMain!lstCategory)) ORDER BY
qryProcedure.Category, qryProcedure.Proceedure;

which works well when lstCategory multi select is off, but obviously doesn't
when its set to simple or extended. What i guess i need is the rowsource
query of the subform control to have a WHERE statement that has concatenated
the selections from the main form list box, as in Dev Ashish's site:

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

but i don't

1. know how or where to put the where clause generated by his code
2. know how to make the selections synchronise between records

So i know this is an impossibly big question but any saint who tries to help
would be a saint indeed

Thanks
CHris R

btw...i apologise for the different spellings of procedure/proceedure...i
changed the spelling half way through!!

 

[SteveS]

Hi Chris,

I can think of several ways to do this - all untested, BTW.

OK, here goes. First, the code can be in the after update event of the list
box or in the click event of a button. If the code is in the list box, any
changes are automatically updated, but if the requery takes a while, it can
get slow; if you want to click on 6 items - the code would run *each* time
you selectd an item. If you use the button, you will have to click the button
to update the subform, but could be faster...
Either one (button code or list box code) can be applied to each of the
following methods.


1) The code loops thru the list box selected items and builds the SQL for
the subform on-the-fly and changes the recordsource of the subform.

or

2) Add a *hidden* text box in the main form header (I asume that is where
the list box is). Change the SQL WHERE clause of the subform to point to the
text box instead of the list box. The code would create the where clause
conditions and put it in the hidden text box.

or

3) Remove the WHERE clause from the subform record source and use the filter
property, again, using a hidden text box or setting the filter property
directly to a string. (Don't forget to set the FilterOn property to TRUE)


I would probably try the hidden text box first. It is easier (for me) than
trying to get the syntax right to write to the recordsource of a subform.

If you use the text box method, don't hide it until after you have the code
creating the Where/ filter string correctly. You will be able to see if the
conditions are correct.


Here is a link on how to "Refer to Form and Subform properties and controls"

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

If you have problems writting the code, post back with what you have and
what the problem is.

HTH

 

[crtopher]

Hi Steve...thanks for replying.

I too like the idea throwing to a hidden text box (btw the list box isn't in
the form header but in the main form, but why would you put it in the
header?) and i have moified Dev Ashish' code to get this to generate the SQL
WHere clause:

Dim frm As Form, ctl as Control
Dim varItem as Variant
Dim strSQL As String
Set frm = Form!frmMain
set ctl = frm!lstCategory
strSQL = "SELECT * from lkupProcedureCategory
WHERE [Category]='"

For each varItem In ctl.ItemsSelected
strSQL = strSQL & ctlItemData(varItem) & "' " & " OR [Category]='"
Next varItem

strSQL=left$(strSQL, len(strSQL)-12))

....but i don't know how to get the SQL into the text box (i am a beginner
I'm sorry!)

Thanks in advance for your ideas
Chris

 

[SteveS]

Hi Chris,

I put the list box is the form header because if you decide to cahnge the
main form from single for to continous forms, and the list box is in the
detail section, you will have a list box in each record (row).

You have a couple of errors in your code.

Set frm = Forms!frmMain : as in formS with an "S"

and

you need a "dot" between ctl and ItemData

strSQL = strSQL & ctl.ItemData(varItem) & "' " & " OR [Category]='"

The way your code is written, you don't need the hidded text box. Since you
are creating the whole SQL, not just the "WHERE" clause, you can set the
subform record source directly.

It would look something like this:

'---------------------------------------
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!frmMain
Set ctl = frm!lstCategory

strSQL = "SELECT * from lkupProcedureCategory WHERE"

' loop thru the list box
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & " [Category]= '" & ctl.ItemData(varItem) & "' OR "
Next varItem

'trim the last " OR " 4 characters
strSQL = Left(strSQL, Len(strSQL) - 4)

' set the subform record source

Me!Child9.Form.RecordSource = strSQL
'--------------------------------------------------------------

Change "CHILD9" to the name of the subform control (not the actual name of
the subform)

And,, yes, I did kinda rewrote the code......

*****

If you want to use the hidden textbox method, you would need to make a few
changes.

Change the subform recordsource by deleting the WHERE clause (including the
word "Where".

My hidden text box name is Text11. The code would look like this:

'--------------------------------------
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!Form1
Set ctl = frm!lstCategory

Me.Text11 = ""

For Each varItem In ctl.ItemsSelected
Me.Text11 = Me.Text11 & "[txtColor] = '" & ctl.ItemData(varItem) & "'
OR "
Next varItem

If Len(Me.Text11) > 0 Then
Me.Text11 = Left(Me.Text11, Len(Me.Text11) - 4)
Me!Child9.Form.Filter = Me.Text11
Me.Child9.Form.FilterOn = True
Else
Me.Text11 = ""
Me!Child9.Form.Filter = ""
Me.Child9.Form.FilterOn = False
End If
'--------------------------------------



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Steve...thanks for replying.

I too like the idea throwing to a hidden text box (btw the list box isn't in
the form header but in the main form, but why would you put it in the
header?) and i have moified Dev Ashish' code to get this to generate the SQL
WHere clause:

Dim frm As Form, ctl as Control
Dim varItem as Variant
Dim strSQL As String
Set frm = Form!frmMain
set ctl = frm!lstCategory
strSQL = "SELECT * from lkupProcedureCategory
WHERE [Category]='"

For each varItem In ctl.ItemsSelected
strSQL = strSQL & ctlItemData(varItem) & "' " & " OR [Category]='"
Next varItem

strSQL=left$(strSQL, len(strSQL)-12))

...but i don't know how to get the SQL into the text box (i am a beginner
I'm sorry!)

Thanks in advance for your ideas
Chris

 

[crtopher]

Wow, i didn't know that about form headers (this is how much of a novice i am).

Thanks so much for the code...it's exactly what i needed...i will try it out
and let you know!

Chris

Hi Chris,

I put the list box is the form header because if you decide to cahnge the
main form from single for to continous forms, and the list box is in the
detail section, you will have a list box in each record (row).

You have a couple of errors in your code.

Set frm = Forms!frmMain : as in formS with an "S"

and

you need a "dot" between ctl and ItemData

strSQL = strSQL & ctl.ItemData(varItem) & "' " & " OR [Category]='"

The way your code is written, you don't need the hidded text box. Since you
are creating the whole SQL, not just the "WHERE" clause, you can set the
subform record source directly.

It would look something like this:

'---------------------------------------
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!frmMain
Set ctl = frm!lstCategory

strSQL = "SELECT * from lkupProcedureCategory WHERE"

' loop thru the list box
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & " [Category]= '" & ctl.ItemData(varItem) & "' OR "
Next varItem

'trim the last " OR " 4 characters
strSQL = Left(strSQL, Len(strSQL) - 4)

' set the subform record source

Me!Child9.Form.RecordSource = strSQL
'--------------------------------------------------------------

Change "CHILD9" to the name of the subform control (not the actual name of
the subform)

And,, yes, I did kinda rewrote the code......

*****

If you want to use the hidden textbox method, you would need to make a few
changes.

Change the subform recordsource by deleting the WHERE clause (including the
word "Where".

My hidden text box name is Text11. The code would look like this:

'--------------------------------------
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!Form1
Set ctl = frm!lstCategory

Me.Text11 = ""

For Each varItem In ctl.ItemsSelected
Me.Text11 = Me.Text11 & "[txtColor] = '" & ctl.ItemData(varItem) & "'
OR "
Next varItem

If Len(Me.Text11) > 0 Then
Me.Text11 = Left(Me.Text11, Len(Me.Text11) - 4)
Me!Child9.Form.Filter = Me.Text11
Me.Child9.Form.FilterOn = True
Else
Me.Text11 = ""
Me!Child9.Form.Filter = ""
Me.Child9.Form.FilterOn = False
End If
'--------------------------------------



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Steve...thanks for replying.

I too like the idea throwing to a hidden text box (btw the list box isn't in
the form header but in the main form, but why would you put it in the
header?) and i have moified Dev Ashish' code to get this to generate the SQL
WHere clause:

Dim frm As Form, ctl as Control
Dim varItem as Variant
Dim strSQL As String
Set frm = Form!frmMain
set ctl = frm!lstCategory
strSQL = "SELECT * from lkupProcedureCategory
WHERE [Category]='"

For each varItem In ctl.ItemsSelected
strSQL = strSQL & ctlItemData(varItem) & "' " & " OR [Category]='"
Next varItem

strSQL=left$(strSQL, len(strSQL)-12))

...but i don't know how to get the SQL into the text box (i am a beginner
I'm sorry!)

Thanks in advance for your ideas
Chris

 

[crtopher]

Hi Steve

Ok I promised I'd get back to you. You're probably groaning!!

Anyway, i used your code with slight modification...

Private Sub LstCategory_AfterUpdate()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Forms!frmMain
Set ctl = frm!LstCategory

strSQL = "SELECT * from qryProcedure WHERE"

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & " [ProceedureCategory] = '" &
ctl.ItemData(varItem) & "' OR "
Next varItem

strSQL = Left(strSQL, Len(strSQL) - 4)

Me!Child27.Form.cboProcedureID.RowSource = strSQL

End Sub

and it works brilliantly...

what happens though is that the choices i make in the list box change the
rowsource for the subform control permanently until i make different
selections in the list box...how do i get the list box choices to stay with
each record;

i thought of two broad stategies, one would be to make the list box somehow
enter its results into an underlying table ie..set a control source but i
don't know if you can do that with a list box with greater than one selection

the other way is to somehow get the subform control to refresh the listbox
each time a new record is entered but again i don't know how you make the
process work in reverse, ie take the strSQL from the rowsource and put it
back as choices in the list box...hope i'm making sense...any thoughts would
be appreciated!!

Chris

Hi Chris,

I put the list box is the form header because if you decide to cahnge the
main form from single for to continous forms, and the list box is in the
detail section, you will have a list box in each record (row).

You have a couple of errors in your code.

Set frm = Forms!frmMain : as in formS with an "S"

and

you need a "dot" between ctl and ItemData

strSQL = strSQL & ctl.ItemData(varItem) & "' " & " OR [Category]='"

The way your code is written, you don't need the hidded text box. Since you
are creating the whole SQL, not just the "WHERE" clause, you can set the
subform record source directly.

It would look something like this:

'---------------------------------------
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!frmMain
Set ctl = frm!lstCategory

strSQL = "SELECT * from lkupProcedureCategory WHERE"

' loop thru the list box
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & " [Category]= '" & ctl.ItemData(varItem) & "' OR "
Next varItem

'trim the last " OR " 4 characters
strSQL = Left(strSQL, Len(strSQL) - 4)

' set the subform record source

Me!Child9.Form.RecordSource = strSQL
'--------------------------------------------------------------

Change "CHILD9" to the name of the subform control (not the actual name of
the subform)

And,, yes, I did kinda rewrote the code......

*****

If you want to use the hidden textbox method, you would need to make a few
changes.

Change the subform recordsource by deleting the WHERE clause (including the
word "Where".

My hidden text box name is Text11. The code would look like this:

'--------------------------------------
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!Form1
Set ctl = frm!lstCategory

Me.Text11 = ""

For Each varItem In ctl.ItemsSelected
Me.Text11 = Me.Text11 & "[txtColor] = '" & ctl.ItemData(varItem) & "'
OR "
Next varItem

If Len(Me.Text11) > 0 Then
Me.Text11 = Left(Me.Text11, Len(Me.Text11) - 4)
Me!Child9.Form.Filter = Me.Text11
Me.Child9.Form.FilterOn = True
Else
Me.Text11 = ""
Me!Child9.Form.Filter = ""
Me.Child9.Form.FilterOn = False
End If
'--------------------------------------



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Steve...thanks for replying.

I too like the idea throwing to a hidden text box (btw the list box isn't in
the form header but in the main form, but why would you put it in the
header?) and i have moified Dev Ashish' code to get this to generate the SQL
WHere clause:

Dim frm As Form, ctl as Control
Dim varItem as Variant
Dim strSQL As String
Set frm = Form!frmMain
set ctl = frm!lstCategory
strSQL = "SELECT * from lkupProcedureCategory
WHERE [Category]='"

For each varItem In ctl.ItemsSelected
strSQL = strSQL & ctlItemData(varItem) & "' " & " OR [Category]='"
Next varItem

strSQL=left$(strSQL, len(strSQL)-12))

...but i don't know how to get the SQL into the text box (i am a beginner
I'm sorry!)

Thanks in advance for your ideas
Chris

 

[bpcdavidson]

Hi,

I used your instructions for the hidden text box method (almost
exactly) and it poplulates the text box beautifully with each
selection and an "OR" between. here is the code:

Private Sub List3_AfterUpdate()

Command6.Enabled = True
Command36.Enabled = True
Command33.Enabled = True

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Me
Set ctl = frm!List3
strSQL = ""
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR "
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 4)

Me.Text43 = strSQL

End Sub

The trouble is, when i use the text box as the criteria for a query it
a only returns values if I have only chosen one item in the list box?

Am I right in thinking that the query is only returning values when
the which match
"Selection1 OR Selection2 OR Selection3" rather than "Selection1" OR
"Selection2" OR "Selection3" ?

I'm very confused, and probably way out of my depth.....Please help!!!