Populating lst Box

[Guest]

I have a list box that I would like to populate with selections based on 2
other multiselect list booxes. I am not sure as to the best way to do this.
Below is a summary of what I have so far:

Currently I have a list box (lst1) getting its data based on the selection
of one other multiselect list box (lst2). This is done by appending data to a
seperate table once a selection is chosen in lst2 and using this data as
lst1's record source. Unfortunately I have not been able to use this logic
to encompass adding one more multiselect list box as a criteria identifier
for lst 1. I have tried using an imbedded For...Each loop but it will only
let me use one at a time.

Is the approach I am using the best way? If not, does anyone think they can
offer advice?

This would be greatly appreciated,

Chad Lucy

 

[MacDermott]

Could you post the relevant code, please?

 

[Guest]

Here is a copy of the code I have so far:
***********************************************************
Private Sub lstProgram_Click()
Dim lst As Access.ListBox
Dim varItem As Variant
Dim strProgCode As String
Set lst = Forms!frmCriteria!lstProgram

DoCmd.RunSQL ("DELETE * FROM tblVarianceReportCriteriatmp")
'DoCmd.RunSQL ("INSERT INTO tblVarianceReportCriteriatmp SELECT DISTINCT
(tblCharge_No_MFC.Program_Code) FROM tblCharge_No_MFC " & _
' "WHERE cboProgram = tblCharge_No_MFC.Program_Code")
For Each varItem In lst.ItemsSelected
strProgCode = "'" & lst.Column(1, varItem) & "'"
DoCmd.RunSQL ("INSERT INTO tblVarianceReportCriteriatmp SELECT
DISTINCT(tblCharge_No_MFC.Program_Code), Master, WrkPkg, CAM FROM
tblCharge_No_MFC " & _
"WHERE tblCharge_No_MFC.Program_Code = " & strProgCode & " ")
Next varItem

Forms!frmCriteria!lstCAM.Requery

End Sub

************************************************************

Once a selection (or more than one ) is selected in lstProgram, this code
populates a table (tblVarianceReportCriteriatmp) which is then used to
populate lstCAM. I would like to now add an additional multiselect list box
which would obtain its data as a result of both of these list boxes
(lstProgram and lstCAM). Let me know if this helps or if you need anything
else.

 

[MacDermott]

If I understand your needs correctly, here's how I'd go about it:
WARNING: AIR CODE

'Populate lst1 with items from MyField in MyTable,
' narrowed by criteria of lst2 on Field2 and lst3 on Field3
Dim SqlStr as String
Dim strCriteria1 as String
Dim strCriteria2 as String
Dim i as Integer

'Collect a string of selected items from lst2, separated by commas
' Surround each item with double-quotes -
' this assumes they are text items
For i=0 to lst2.listcount-1
if lst2.selected(i)=true then strCriteria1= chr(34) & strCriteria1 &
chr(34) & lst2.itemdata(i) & ","
Next
'If anything was selected, strip off the final comma
If len(strCriteria1)>0 then _
strCriteria1=left(strCriteria1,len(strCriteria1)-1)

'Collect a string of selected items from lst3, separated by commas
' Surround each item with double-quotes -
' this assumes they are text items
For i=0 to lst3.listcount-1
if lst3.selected(i)=true then strCriteria2= chr(34) & strCriteria2 &
chr(34) & lst3.itemdata(i) & ","
Next
'If anything was selected, strip off the final comma
If len(strCriteria2)>0 then _
strCriteria2=left(strCriteria2,len(strCriteria2)-1)

'Create the basic SQL string
StrSQL="SELECT MyField FROM MyTable
'If criteria were selected in lst2, add a WHERE clause
If len(strCriteria1)>0 then _
StrSQL=StrSQL & " WHERE Field2 IN (" & strCriteria1 & ")"
'If criteria were selected in lst3, add a WHERE clause
If len(strCriteria2)>0 then
'Check whether there is already a WHERE present
If instr(SQLStr, " WHERE ")>0 then
SQLStr=SQLStr & " AND "
Else
SQLStr=SQLStr & " WHERE "
End If
StrSQL=StrSQL & " Field3 IN (" & strCriteria2 & ")"

'Assign this SQL to the RowSource of lst1.
'Note: no need to create a temp table
' Writing a temp table to the hard disk takes time.
lst1.RowSource=StrSQL