G
Gordon
This is a follow on from a recently posted query. I am building the
criteria for a query on the fly using a form which
has 3 elements:
1. 2 check boxes to determine which of 1 or 2 additional fields
are in
the query
2 2 check boxes (based on Yes/No fields) to select certain
records
records to be included or not
3. a multi select list box to filter the query on certain
categories from one
field.
I have managed to get this all working fine but want to enhance it by
including an "All" option at the top of the multi slect list box but
cannot get this to work: The code as it stands is as follows is:
Private Sub cmdExportNamesAddresses_Click()
Dim strSQL As String, strWhere As String
Dim strSelect, strFrom As String, varItem As Variant
On Error GoTo Err_Handler
'Stage 1 - Construct query with basic fields
strSelect = "SELECT tblTitles.fldTitle,
tblWSBMarketingContacts.fldInitials,
tblWSBMarketingContacts.fldSurname,
tblWSBMarketingContacts.fldAddress1, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress2,
tblWSBMarketingContacts.fldAddress3,
tblWSBMarketingContacts.fldAddress4,
tblWSBMarketingContacts.fldAddress5, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress6,
tblWSBMarketingContacts.fldPostCode"
'Additional field - tblWSBMarketingContacts.fldAffix
If Me!chkNameAffix = True And Me!chkCompanyPosition = False Then
strSelect = "SELECT tblTitles.fldTitle,
tblWSBMarketingContacts.fldInitials,
tblWSBMarketingContacts.fldSurname, tblWSBMarketingContacts.fldAffix,
tblWSBMarketingContacts.fldAddress1, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress2,
tblWSBMarketingContacts.fldAddress3,
tblWSBMarketingContacts.fldAddress4,
tblWSBMarketingContacts.fldAddress5, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress6,
tblWSBMarketingContacts.fldPostCode"
End If
'Additional fields -
tblWSBMarketingContacts.fldCompanyName,tblWSBMarketingContacts.fldPosition
If Me!chkNameAffix = False And Me!chkCompanyPosition = True Then
strSelect = "SELECT tblTitles.fldTitle,
tblWSBMarketingContacts.fldInitials,
tblWSBMarketingContacts.fldSurname,
tblWSBMarketingContacts.fldPosition,
tblWSBMarketingContacts.fldCompanyName,
tblWSBMarketingContacts.fldAddress1, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress2,
tblWSBMarketingContacts.fldAddress3,
tblWSBMarketingContacts.fldAddress4,
tblWSBMarketingContacts.fldAddress5, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress6,
tblWSBMarketingContacts.fldPostCode"
End If
'Additional fields - tblWSBMarketingContacts.fldAffix,
tblWSBMarketingContacts.fldCompanyName,tblWSBMarketingContacts.fldPosition
If Me!chkNameAffix = True And Me!chkCompanyPosition = True Then
strSelect = "SELECT tblTitles.fldTitle,
tblWSBMarketingContacts.fldInitials,
tblWSBMarketingContacts.fldSurname, tblWSBMarketingContacts.fldAffix,
tblWSBMarketingContacts.fldPosition,
tblWSBMarketingContacts.fldCompanyName,
tblWSBMarketingContacts.fldAddress1, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress2,
tblWSBMarketingContacts.fldAddress3,
tblWSBMarketingContacts.fldAddress4,
tblWSBMarketingContacts.fldAddress5, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress6,
tblWSBMarketingContacts.fldPostCode"
End If
'Stage 2 - add the source of the data fields
strFrom = " FROM tblTitles RIGHT JOIN (tblWSBMarketingContacts LEFT
JOIN tblContactCategories ON
tblWSBMarketingContacts.fldContactCategoryID =
tblContactCategories.fldContactCategoryID) ON tblTitles.fldTitleID =
tblWSBMarketingContacts.fldTitle "
'Stage 3 - construct the WHERE clause from the multi list box
strWhere = ""
For Each varItem In lstContactCategories.ItemsSelected
If strWhere = "" Then
strWhere = " tblWSBMarketingContacts.fldContactCategoryID = " &
lstContactCategories.ItemData(varItem)
Else
strWhere = strWhere & " OR
tblWSBMarketingContacts.fldContactCategoryID = " &
lstContactCategories.ItemData(varItem)
End If
Next varItem
If strWhere <> "" Then
strSQL = strSQL & strSelect & strFrom & " WHERE " & strWhere & ";"
End If
CurrentDb.QueryDefs("qryExportDataTemplate").SQL = strSQL
DoCmd.OpenQuery "qryExportDataTemplate"
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Query cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdExportNamesAddresses_Click"
End If
Resume Exit_Handler
End Sub
------------------------------------------------------------------------------------
Currently , the row source of the listbox is:
SELECT 0 as fldContactCategoryID, "[All]" as fldContactCategory from
tblContactCategories UNION select
tblContactCategories.fldContactCategoryID,
tblContactCategories.fldContactCategory from tblContactCategories
ORDER BY fldContactCategory;
All this code works fine if I pick any one or multi items from the
list box, but if I pick the "All" option, I get a syntax error message
which when debugged shows the following value for strSQL:
SELECT tblTitles.fldTitle, tblWSBMarketingContacts.fldInitials,
tblWSBMarketingContacts.fldSurname,
tblWSBMarketingContacts.fldAddress1,
tblWSBMarketingContacts.fldAddress2,
tblWSBMarketingContacts.fldAddress3,
tblWSBMarketingContacts.fldAddress4,
tblWSBMarketingContacts.fldAddress5,
tblWSBMarketingContacts.fldAddress6,
tblWSBMarketingContacts.fldPostCode FROM tblTitles RIGHT JOIN
(tblWSBMarketingContacts LEFT JOIN tblContactCategories ON
tblWSBMarketingContacts.fldContactCategoryID =
tblContactCategories.fldContactCategoryID) ON tblTitles.fldTitleID =
tblWSBMarketingContacts.fldTitle WHERE
tblWSBMarketingContacts.fldContactCategoryID = ;
The field "fldContactCategoryID is the key field in table
"tblContactCategories" and its value ranges from 1 to 23.
How can I get this to work?
Gordon
criteria for a query on the fly using a form which
has 3 elements:
1. 2 check boxes to determine which of 1 or 2 additional fields
are in
the query
2 2 check boxes (based on Yes/No fields) to select certain
records
records to be included or not
3. a multi select list box to filter the query on certain
categories from one
field.
I have managed to get this all working fine but want to enhance it by
including an "All" option at the top of the multi slect list box but
cannot get this to work: The code as it stands is as follows is:
Private Sub cmdExportNamesAddresses_Click()
Dim strSQL As String, strWhere As String
Dim strSelect, strFrom As String, varItem As Variant
On Error GoTo Err_Handler
'Stage 1 - Construct query with basic fields
strSelect = "SELECT tblTitles.fldTitle,
tblWSBMarketingContacts.fldInitials,
tblWSBMarketingContacts.fldSurname,
tblWSBMarketingContacts.fldAddress1, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress2,
tblWSBMarketingContacts.fldAddress3,
tblWSBMarketingContacts.fldAddress4,
tblWSBMarketingContacts.fldAddress5, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress6,
tblWSBMarketingContacts.fldPostCode"
'Additional field - tblWSBMarketingContacts.fldAffix
If Me!chkNameAffix = True And Me!chkCompanyPosition = False Then
strSelect = "SELECT tblTitles.fldTitle,
tblWSBMarketingContacts.fldInitials,
tblWSBMarketingContacts.fldSurname, tblWSBMarketingContacts.fldAffix,
tblWSBMarketingContacts.fldAddress1, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress2,
tblWSBMarketingContacts.fldAddress3,
tblWSBMarketingContacts.fldAddress4,
tblWSBMarketingContacts.fldAddress5, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress6,
tblWSBMarketingContacts.fldPostCode"
End If
'Additional fields -
tblWSBMarketingContacts.fldCompanyName,tblWSBMarketingContacts.fldPosition
If Me!chkNameAffix = False And Me!chkCompanyPosition = True Then
strSelect = "SELECT tblTitles.fldTitle,
tblWSBMarketingContacts.fldInitials,
tblWSBMarketingContacts.fldSurname,
tblWSBMarketingContacts.fldPosition,
tblWSBMarketingContacts.fldCompanyName,
tblWSBMarketingContacts.fldAddress1, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress2,
tblWSBMarketingContacts.fldAddress3,
tblWSBMarketingContacts.fldAddress4,
tblWSBMarketingContacts.fldAddress5, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress6,
tblWSBMarketingContacts.fldPostCode"
End If
'Additional fields - tblWSBMarketingContacts.fldAffix,
tblWSBMarketingContacts.fldCompanyName,tblWSBMarketingContacts.fldPosition
If Me!chkNameAffix = True And Me!chkCompanyPosition = True Then
strSelect = "SELECT tblTitles.fldTitle,
tblWSBMarketingContacts.fldInitials,
tblWSBMarketingContacts.fldSurname, tblWSBMarketingContacts.fldAffix,
tblWSBMarketingContacts.fldPosition,
tblWSBMarketingContacts.fldCompanyName,
tblWSBMarketingContacts.fldAddress1, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress2,
tblWSBMarketingContacts.fldAddress3,
tblWSBMarketingContacts.fldAddress4,
tblWSBMarketingContacts.fldAddress5, "
strSelect = strSelect & " tblWSBMarketingContacts.fldAddress6,
tblWSBMarketingContacts.fldPostCode"
End If
'Stage 2 - add the source of the data fields
strFrom = " FROM tblTitles RIGHT JOIN (tblWSBMarketingContacts LEFT
JOIN tblContactCategories ON
tblWSBMarketingContacts.fldContactCategoryID =
tblContactCategories.fldContactCategoryID) ON tblTitles.fldTitleID =
tblWSBMarketingContacts.fldTitle "
'Stage 3 - construct the WHERE clause from the multi list box
strWhere = ""
For Each varItem In lstContactCategories.ItemsSelected
If strWhere = "" Then
strWhere = " tblWSBMarketingContacts.fldContactCategoryID = " &
lstContactCategories.ItemData(varItem)
Else
strWhere = strWhere & " OR
tblWSBMarketingContacts.fldContactCategoryID = " &
lstContactCategories.ItemData(varItem)
End If
Next varItem
If strWhere <> "" Then
strSQL = strSQL & strSelect & strFrom & " WHERE " & strWhere & ";"
End If
CurrentDb.QueryDefs("qryExportDataTemplate").SQL = strSQL
DoCmd.OpenQuery "qryExportDataTemplate"
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Query cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdExportNamesAddresses_Click"
End If
Resume Exit_Handler
End Sub
------------------------------------------------------------------------------------
Currently , the row source of the listbox is:
SELECT 0 as fldContactCategoryID, "[All]" as fldContactCategory from
tblContactCategories UNION select
tblContactCategories.fldContactCategoryID,
tblContactCategories.fldContactCategory from tblContactCategories
ORDER BY fldContactCategory;
All this code works fine if I pick any one or multi items from the
list box, but if I pick the "All" option, I get a syntax error message
which when debugged shows the following value for strSQL:
SELECT tblTitles.fldTitle, tblWSBMarketingContacts.fldInitials,
tblWSBMarketingContacts.fldSurname,
tblWSBMarketingContacts.fldAddress1,
tblWSBMarketingContacts.fldAddress2,
tblWSBMarketingContacts.fldAddress3,
tblWSBMarketingContacts.fldAddress4,
tblWSBMarketingContacts.fldAddress5,
tblWSBMarketingContacts.fldAddress6,
tblWSBMarketingContacts.fldPostCode FROM tblTitles RIGHT JOIN
(tblWSBMarketingContacts LEFT JOIN tblContactCategories ON
tblWSBMarketingContacts.fldContactCategoryID =
tblContactCategories.fldContactCategoryID) ON tblTitles.fldTitleID =
tblWSBMarketingContacts.fldTitle WHERE
tblWSBMarketingContacts.fldContactCategoryID = ;
The field "fldContactCategoryID is the key field in table
"tblContactCategories" and its value ranges from 1 to 23.
How can I get this to work?
Gordon