SQL Statement created from ItemsSelected from Several Listboxes..

  • Thread starter Thread starter Denis Bisson
  • Start date Start date
D

Denis Bisson

Good day,

I am attempting to develop a resume/qualification cross-
reference database. At this stage, this is what I am
trying to do:

· A Search form has a number of listboxes (say 8)
listing qualifications (each listbox is a "Category" of
Quals);
· My goal is to create a SQL statement which will
be used as a RowSource to another listbox (lstResults),
resulting in a list of personnel matching the
Qualifications selected in various other listboxes. for
example: I could be looking with someone with "Access
2000" and "Excel 2000" experience (from the <Software>
listbox), as well as "ISO 14001" and "Lead Assessor"
experience (from the <Environmental> listbox), with
a "BSc in Electrical Engineering" (from the <Education>
listbox) - hey.. you never know...
· At this point, I am able to formulate a SQL
statement for more than one qualification within a
Category, in part using the Multiselect property of a
listbox.
· My problem is that I am unable to formulate a SQL
statement that will include ALL selected Quals from their
selected listboxes at the same time. I.e. the example
above. Instead, the resulting query will only display
the results based on the LAST listbox selected. I almost
understand why - as you will see in the code below, but
am unsure where to fit in another "Loop" type statement
to get the SQL statement go through EACH listbox and
capture all selected items.

Any assistance is greatly appreciated.

Thanks Denis


Private Sub cmdGo_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strSQLSub As String 'subquery checking for separate
quals
Dim i As Integer

For Each ctl In Me.Controls

If ctl.ControlType = acListBox Then ' if the ctl is
a listbox
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) = True Then ' if the ctl
is selected

'The following is for querying for this
qual AND this qual AND...

' 1) Get the name of pers...

strSQL = "SELECT tblCV_Pers.PersID,
tblCV_Pers.LNm & "", "" & tblCV_Pers.Fnm AS Name" _
& " FROM tblCV_Pers" _
& " WHERE "

' 2) where the quals =..
..
strSQLSub = "(SELECT
tblCV_PersQuals.Qual" _
& " FROM tblCV_PersQuals" _
& " WHERE tblCV_PersQuals.PersID =
tblCV_Pers.PersID AND tblCV_PersQuals.Qual = "

' 3) Add each Qual to SQL statement

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & strSQLSub
& "'" & ctl.ItemData(varItem) & "') AND" ' And strSQLSub
& ")"
Next varItem


End If
Next i


End If
Next ctl
strSQL = Left$(strSQL, Len(strSQL) - 3)


txtTemp.Value = strSQL ' txtTemp is a temporary text box
used for viewing the SQL statements during development
DoCmd.RunCommand acCmdSaveRecord
lstResults.RowSource = strSQL & "ORDER BY
tblCV_Pers.lnm" ' the last part Sorts the results
lstResults.Requery

End Sub
 
What's happening now is that every time you go through the loop, you're
resetting strSQL and strSQLSub.

Remember that an assignment statement completely replaces whatever was in
the variable. As a result, every time you execute the statement

strSQL = "SELECT tblCV_Pers.PersID,
tblCV_Pers.LNm & "", "" & tblCV_Pers.Fnm AS Name" _
& " FROM tblCV_Pers" _
& " WHERE "

completely replaces anything you've put in there from the previous ctl.

You don't need to add a loop. What you need to do is write the assignment
statements so that they are appending to a running string. Do this by
initializing your two strings BEFORE the start of the loop, and then
appending additional criteria as you go through the loop.

For example:

strSQL = "tblCV_Pers.PersID, tblCV_Pers.LNm, tblCV_Pers.Fnm AS Name FROM
tblCV_Pers where "
for each ctl in me.controls
<test to see if anything selected>
<if something was selected in listbox software>
strSQL = strSQL + "software = '" & <item selected & "'"
next ctl

This way, you're building up a SQL string rather than creating a new one on
each loop. From what I can see, I don't think you need the strSQLSub.


Good day,

I am attempting to develop a resume/qualification cross-
reference database. At this stage, this is what I am
trying to do:

· A Search form has a number of listboxes (say 8)
listing qualifications (each listbox is a "Category" of
Quals);
· My goal is to create a SQL statement which will
be used as a RowSource to another listbox (lstResults),
resulting in a list of personnel matching the
Qualifications selected in various other listboxes. for
example: I could be looking with someone with "Access
2000" and "Excel 2000" experience (from the <Software>
listbox), as well as "ISO 14001" and "Lead Assessor"
experience (from the <Environmental> listbox), with
a "BSc in Electrical Engineering" (from the <Education>
listbox) - hey.. you never know...
· At this point, I am able to formulate a SQL
statement for more than one qualification within a
Category, in part using the Multiselect property of a
listbox.
· My problem is that I am unable to formulate a SQL
statement that will include ALL selected Quals from their
selected listboxes at the same time. I.e. the example
above. Instead, the resulting query will only display
the results based on the LAST listbox selected. I almost
understand why - as you will see in the code below, but
am unsure where to fit in another "Loop" type statement
to get the SQL statement go through EACH listbox and
capture all selected items.

Any assistance is greatly appreciated.

Thanks Denis


Private Sub cmdGo_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim strSQLSub As String 'subquery checking for separate
quals
Dim i As Integer

For Each ctl In Me.Controls

If ctl.ControlType = acListBox Then ' if the ctl is
a listbox
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) = True Then ' if the ctl
is selected

'The following is for querying for this
qual AND this qual AND...

' 1) Get the name of pers...

strSQL = "SELECT tblCV_Pers.PersID,
tblCV_Pers.LNm & "", "" & tblCV_Pers.Fnm AS Name" _
& " FROM tblCV_Pers" _
& " WHERE "

' 2) where the quals =..
..
strSQLSub = "(SELECT
tblCV_PersQuals.Qual" _
& " FROM tblCV_PersQuals" _
& " WHERE tblCV_PersQuals.PersID =
tblCV_Pers.PersID AND tblCV_PersQuals.Qual = "

' 3) Add each Qual to SQL statement

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & strSQLSub
& "'" & ctl.ItemData(varItem) & "') AND" ' And strSQLSub
& ")"
Next varItem


End If
Next i


End If
Next ctl
strSQL = Left$(strSQL, Len(strSQL) - 3)


txtTemp.Value = strSQL ' txtTemp is a temporary text box
used for viewing the SQL statements during development
DoCmd.RunCommand acCmdSaveRecord
lstResults.RowSource = strSQL & "ORDER BY
tblCV_Pers.lnm" ' the last part Sorts the results
lstResults.Requery

End Sub
 
JP, thanks for your time and effort. You have given me a
new perspective on this problem.

Cheers!
Denis
 
Back
Top