Multiselect listbox parameter query followup

D

Dave Twombly

I'm trying to make a multiselect listbox provide one or more parameters
for a query. I've found useful code for concatenating the items
selected in the list box at
http://www.mvps.org/access/forms/frm0007.htm, but I don't know how to
make my query read that string as its parameter. The website says this:
"Note: You can still use a parameterized query provided you pass the
entire Where clause to it via code as a parameter. (eg. Have the query
reference a hidden control to which you manually assign the complete
WHERE clause using the following logic...[Code begins])"

What I don't know how to do here is assign the WHERE clause that the
code produces to a control on my form. I assume that once that's done I
simply refer the query condition to the control, much as if it were a
single-select listbox.

Would someone please help me complete these final steps of getting the
query to accept the string as one of its parameters?

(Incidentally, I found a helpful way to do this for reports on
http://allenbrowne.com/ser-50.html, but I can't make that method work
for a query.)

Thanks,
Dave
 
M

Michel Walsh

Hi,


The easier way would be to create the WHOLE query as a string, form the
SELECT up to the WHERE clause your code produces. With that single string,
representing a valid SQL statement, you can use it as record source (form,
sub-form, report), or as row source (list box, combo box) as if it was a
saved query. Just be sure the string is a valid SQL statement: Debug.Print
it in the immediate debug window, then, paste it in the SQL view of a new
Query. That new query should then run (to see data in data view) without
producing any error.


Hoping it may help,
Vanderghast, Access MVP
 
D

Dave Twombly

Hi there,

Thanks for your reply, Michel. Are you suggesting that I have my form
build the complete SQL statement, send that statement as the source for
a saved query, then use the saved query as the recordsource for a
secondary report or form?

Since I originally wanted to create custom made reports and
spreadsheets from user entries, I've been handling my original problem
by bypassing the query altogether. I just build a WHERE condition and
use that in the OpenForm or OpenReport method. The recordsource for the
resulting report or form is a single table I'm drawing from. I've
copied in the code I'm using below (modified from Allen Browne's). As
you can see, I also have the user enter a date range and select from an
option box.

This is the only way I know how to do this, and I'd like to know how to
use the whole SQL statement the way you're suggesting. Can you say more
specifically what you would do with the complete SQL string once you
had built it in order, say, to use it as the recordsource for a report?
I tried storing it in a hidden control (an unbound text box) and having
a second form use that as its recordsource, but I couldn't make that
work. Do I have to go through a saved query?

Thanks,
Dave


Private Sub cmdPreview_Click()

On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "rptEqptBuyers"

'Loop through the ItemsSelected in the list box.
With Me.lstEqpt
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[EQTDESC] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Eqpt Types: " & Left$(strDescrip, lngLen)
End If
End If

'Add start and end dates to SQL
strWhere = strWhere & " AND ([UCCDATE] Between
Forms!frmEqptDesc!txtStartDate And Forms!frmEqptDesc!txtEndDate)"

'Add new or used filter if a box is checked
If Me.optNU = 1 Then
strWhere = strWhere & " AND [EQTNU] = ""N"""
End If
If Me.optNU = 2 Then
strWhere = strWhere & " AND [EQTNU] = ""U"""
End If
MsgBox strWhere

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub
 
D

Dave Twombly

Michel,

In a test database I've done what I think you were suggesting (code
below). I build a complete SQL statement from the user's entries on
frmTest. I then use the string as the QueryDef of a dummy query called
qryTestSQL -- this query has no other content. I then use qryTestSQL as
the recordsource for frmTest2, and this works perfectly. Is this the
method you were describing? (I like to know what my various options
are.)

Thanks,
Dave

Private Sub Command14_Click()

On Error GoTo Err_Handler
'Purpose: Open the form filtered to the items selected.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb

Dim strWhere As String 'String to use as WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strSQL As String 'To hold SQL statement.
Dim strConjunction As String 'For and/or operator
strDelim = """" 'Delimiter appropriate to field type.
See note 1.

'Build WHERE clause from first text box.
strWhere = txtTest1
lngLen = Len(strWhere)
If lngLen > 0 Then
strWhere = "[EQTDESC] =" & strDelim & strWhere & strDelim
End If


'Create conjunction
If Me.optTest = 1 Then
strConjunction = " AND "
Else
strConjunction = " OR "
End If


strSQL = strWhere & strConjunction

strWhere = txtTest2
lngLen = Len(strWhere)
If lngLen > 0 Then
strWhere = "[EQTNU] =" & strDelim & strWhere & strDelim
End If

strSQL = "SELECT tblUCCMerge.EQTDESC, tblUCCMerge.EQTNU FROM
tblUCCMerge WHERE " & strSQL & strWhere
txtSQL = strSQL
MsgBox txtSQL, vbMsgBoxRtlReading, "YO!"


'*** delete the previous query
db.QueryDefs.Delete "qryTestSQL"
Set qdf = db.CreateQueryDef("qryTestSQL", strSQL)

DoCmd.OpenForm "frmTest2", acViewNormal

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Table cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler


End Sub
 
M

Michel Walsh

Hi,


And you can also use the Recordsource directly:

----------------------------
Private Sub Form_Open(Cancel As Integer)
Me.RecordSource = Me.OpenArgs
End Sub
-----------------------------


and, to open the form, here, supply the SQL statement in the OpenArgs
parameter:


-----------------------
DoCmd.OpenForm "Form52", OpenArgs:="SELECT iota FROM iotas WHERE iota <=10"
-----------------------


as example. Sure, that assumes the controls find the fields names they
expect to be bound to.


There is always the possibility to get an SQL injection attack when we
create an SQL statement from a user input. Since JET does not allow
comments, neither multiple statements, that is somehow less likely than if
you use MS SQL Server, but the possibility has to be known, and the security
threat considered.



Hoping it may help,
Vanderghast, Access MVP


Duane Hookom said:
You don't have to delete and recreate the query. Replace these two lines:
db.QueryDefs.Delete "qryTestSQL"
Set qdf = db.CreateQueryDef("qryTestSQL", strSQL)
with
db.QueryDefs("qryTestSQL").SQL = strSQL

There is a somewhat generic Listbox with Multislection function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP


Dave Twombly said:
Michel,

In a test database I've done what I think you were suggesting (code
below). I build a complete SQL statement from the user's entries on
frmTest. I then use the string as the QueryDef of a dummy query called
qryTestSQL -- this query has no other content. I then use qryTestSQL as
the recordsource for frmTest2, and this works perfectly. Is this the
method you were describing? (I like to know what my various options
are.)

Thanks,
Dave

Private Sub Command14_Click()

On Error GoTo Err_Handler
'Purpose: Open the form filtered to the items selected.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb

Dim strWhere As String 'String to use as WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strSQL As String 'To hold SQL statement.
Dim strConjunction As String 'For and/or operator
strDelim = """" 'Delimiter appropriate to field type.
See note 1.

'Build WHERE clause from first text box.
strWhere = txtTest1
lngLen = Len(strWhere)
If lngLen > 0 Then
strWhere = "[EQTDESC] =" & strDelim & strWhere & strDelim
End If


'Create conjunction
If Me.optTest = 1 Then
strConjunction = " AND "
Else
strConjunction = " OR "
End If


strSQL = strWhere & strConjunction

strWhere = txtTest2
lngLen = Len(strWhere)
If lngLen > 0 Then
strWhere = "[EQTNU] =" & strDelim & strWhere & strDelim
End If

strSQL = "SELECT tblUCCMerge.EQTDESC, tblUCCMerge.EQTNU FROM
tblUCCMerge WHERE " & strSQL & strWhere
txtSQL = strSQL
MsgBox txtSQL, vbMsgBoxRtlReading, "YO!"


'*** delete the previous query
db.QueryDefs.Delete "qryTestSQL"
Set qdf = db.CreateQueryDef("qryTestSQL", strSQL)

DoCmd.OpenForm "frmTest2", acViewNormal

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Table cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler


End Sub
Click to expand...
Click to expand...
 
D

Dave Twombly

Duane and Michel,

Both of your methods worked for me -- thanks very much for your help.

Dave
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiselect listbox problem in queries 2
crosstab parameterized query 1
Multiselect Listbox Query Code 2
Calling Parameterized Query Using SQL Syntax 3
Update Query with Parameter 3
Word macro that can call cells out of Excel for a find and replace 0
Parameter Query with DSum 2
Multiple Parameter Crosstab query filter by form 1

Top