Messed Up QueryDef

  • Thread starter Thread starter M Taimanov
  • Start date Start date
M

M Taimanov

I have a form with 2 list boxes.

ListBox1 cboCategories (not multi select)
ListBox3 cboSubCategories (multi select
extended)

These 2 boxes are synched with this After Update code in
cboCategories :

Private Sub cboCategories_AfterUpdate()

Me!cboSubCategories.RowSource = "Select DISTINCT
[Category]," & _
"[subCatName] From [DMA Photo Library] WHERE Category" & _
" = '" & Me![cboCategories] & "'"

End Sub

A Print Preview command button then runs the following
code :

Private Sub Command4_Click()

Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

'Previews Report

' Build a list of the selections.
Set ctl = Me![cboSubCategories]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData
(Itm) Chr(34)
End If
Next Itm

If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in
the list box!", 0, "No Selection Made")

Exit Sub
End If

' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("DMA Photo Library Query")
Q.SQL = "SELECT * FROM [DMA Photo Library] " & _
" WHERE [subCatName] In (" & Criteria & _
") AND [Category] = " & Chr$(34) & _
Me!cboCategories & Chr$(34)

Q.Close

' Run the report.
DoCmd.OpenReport "DMA Photo Library", acViewPreview

End Sub


This was working thanks to the kind help of V.T.Dinh but
somewhow I screwed it up. The problem that arises now is
this :

if the value selected in cboCategories is "Pools" and the
value in cboSubCategories is "Exterior Views", when the
query is modified in the Q.SQL section above, for some
reason, the value of both the variable 'Crtieria' and
cboCategories = "Pools" and when I look at the underlying
query - the criteria for both fields, subCatName and
Category has been modified to In ("Pools")

Many thanks for pointing the error of my ways !


Mark


Mark
 
Back
Top