Re: Adding <All> as option in a multiselect list box

On 30 Jan, 14:35, "Danny Lesandrini" <dlesandr...@xxxxxxxxxxx> wrote:
I had a hard time reading your SQL below but I can answer the question
about adding the <All> option to a listbox.

You need to use a UNION query and assign the <All> option an ID value,
like zero or -1, so you'll know that the user selected that option.

So if I loaded a list box with users, like this ...

SELECT UserID, UserName FROM tblUsers ORDER BY UserName

I could add the <All> option by changing that SQL to this ...

   SELECT 0 AS UserID, '<All>' AS UserName from tblUsers
   UNION
   SELECT tblUsers.UserID, tblUsers.UserName
   FROM tblUsers ORDER BY UserName;

If you use UNION ALL, it will not suppress duplicates that would be
generated from the first SELECT.   The only reason I say that is
because if you do this on a table with millions of rows, I can't say
what that will do to performance.

On SQL Server, you don't need to specify a table in the SELECT statement,
but with Access there needs to be *some* reference, even if it's irrelevant.

--
Danny J Lesandrini
dlesandr...@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

"Gordon" <gors...@xxxxxxxxxxx> wrote  ...



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- Hide quoted text -

- Show quoted text -

Hi Danny,

Thanks for the quick response. Sorry that you found my code difficult
to read - hope that was because of the word wrapping and not because
it is poorly written code <grn>.

Anyway, the solution you describe is indeed the one I tried - I'll
repeat that part of the code that is listed above:

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;

The option "All" is showing OK but when I select it and run the query
from the command button, I get an error message....sorry but I am
repeating what I said at the end of my message above.

The problem is that the SQL code being generated is incorrect.

Gordon
.