Adding <All> as option in a multiselect list box

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
 
D

Danny Lesandrini

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
(e-mail address removed)
www.amazecreations.com



Gordon said:
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
Click to expand...
 
G

Gordon

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
(e-mail address removed)



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:
Click to expand...
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.
Click to expand...
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
Click to expand...
'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"
Click to expand...
'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
Click to expand...
'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 "
Click to expand...
'Stage 3 - construct the WHERE clause from the multi list box
Click to expand...
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
Click to expand...
If strWhere <> "" Then
   strSQL = strSQL & strSelect & strFrom & " WHERE " & strWhere &";"
End If
Click to expand...
CurrentDb.QueryDefs("qryExportDataTemplate").SQL = strSQL
DoCmd.OpenQuery "qryExportDataTemplate"
Exit_Handler:
   Exit Sub
Click to expand...
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;
Click to expand...
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 = ;
Click to expand...
The field "fldContactCategoryID is the key field in table
"tblContactCategories" and its value ranges from 1 to 23.
Click to expand...
How can I get this to work?
Click to expand...
Gordon- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
D

Douglas J. Steele

Danny Lesandrini said:
I had a hard time reading your SQL below.
Click to expand...

Me too!
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;
Click to expand...

<picky>

SELECT UserID, UserName FROM tblUsers
UNION
SELECT 0, '<All>' FROM tblUsers
ORDER BY UserName

means you don't have to provide aliases for the second subselect, since
UNION queries always get their field names from the first subselect.

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.
Click to expand...

There's no reason why you need to use tblUsers for the second subquery. Pick
a tiny table, or use a WHERE clause to limit what's returned:

SELECT UserID, UserName FROM tblUsers
UNION
SELECT 0, '<All>' FROM tblUsers
WHERE UserID = 1
ORDER BY UserName

(Of course, you'd need to ensure that there WAS a UserID 1 using the second
approach)
 
G

Gordon

Me too!






<picky>

SELECT UserID, UserName FROM tblUsers
UNION
SELECT 0, '<All>' FROM tblUsers
ORDER BY UserName

means you don't have to provide aliases for the second subselect, since
UNION queries always get their field names from the first subselect.



There's no reason why you need to use tblUsers for the second subquery. Pick
a tiny table, or use a WHERE clause to limit what's returned:

SELECT UserID, UserName FROM tblUsers
UNION
SELECT 0, '<All>' FROM tblUsers
WHERE UserID = 1
ORDER BY UserName

(Of course, you'd need to ensure that there WAS a UserID 1 using the second
approach)
Click to expand...

Hello Doug,

I presume your last post crossed with my second one and that you were
really responding to Danny's post. I still need help to resolve my
query.

Gordon
 
D

Danny J. Lesandrini

Doug, you have more patience than I do. :)

If I suggested using another table, then I'd have had to explain what
you did. In my apps I usually have a table, like tblVersion, with only 1 row.
That's the one I usually use for these cases, but explaining that will lead
to a multitude of other questions. :)

I agree about the SELECT order and saving the typing of column names
but since that row will show up first in the list, my brain tells me to put
it first to save the query engine the work. I suppose that's bogus reasoning,
but since you brought it up, that's why I did it.

Speaking of hard to read, I've noticed, now that I'm back reading posts
again, that there seems to be a lot less white-space than I recall. Does it
seem that way to you? More spam on these groups than I remember too.
 
D

Danny J. Lesandrini

Gordon, are you saying my suggestion didn't help? Doug was just
fine tuning it. It should solve your problem either way, with his
syntax or mine.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


On 30 Jan, 15:39, "Douglas J. Steele"

Hello Doug,

I presume your last post crossed with my second one and that you were
really responding to Danny's post. I still need help to resolve my
query.

Gordon
 
B

BruceM

The code is hard to read in part because there is so much of it, and in part
(at least for me) because of the long table name repeated over and over.
The presence of fldAddress1...fldAddress6 does suggest a somewhat
inefficient design, but I don't thing that's relevant to the problem at
hand. When posting code it is often helpful to edit out information that is
not part of the problem at hand. For instance, FirstName and LastName are
enough to get the idea without including MI, Address, etc.

A part of this I haven't seen described yet is that you need something in
the list box After Update event to allow for UserID being zero. If you post
the After Update code it will be easier to suggest something.

Me too!






<picky>

SELECT UserID, UserName FROM tblUsers
UNION
SELECT 0, '<All>' FROM tblUsers
ORDER BY UserName

means you don't have to provide aliases for the second subselect, since
UNION queries always get their field names from the first subselect.



There's no reason why you need to use tblUsers for the second subquery.
Pick
a tiny table, or use a WHERE clause to limit what's returned:

SELECT UserID, UserName FROM tblUsers
UNION
SELECT 0, '<All>' FROM tblUsers
WHERE UserID = 1
ORDER BY UserName

(Of course, you'd need to ensure that there WAS a UserID 1 using the
second
approach)
Click to expand...

Hello Doug,

I presume your last post crossed with my second one and that you were
really responding to Danny's post. I still need help to resolve my
query.

Gordon
 
D

Douglas J. Steele

First, you can simplify your SQL by using the following for your
multiselect:

strWhere = vbNullString
If lstContactCategories.ItemsSelected.Count > 0 Then
For Each varItem In lstContactCategories.ItemsSelected
strWhere = strWhere & lstContactCategories.ItemData(varItem) & ", "
Next varItem
strWhere = "tblWSBMarketingContacts.fldContactCategoryID IN (" & _
Left(strWhere, Len(strWhere) - 2) & ")"
End If

To handle the All condition, you can use:

strWhere = vbNullString
If lstContactCategories.ItemsSelected.Count > 0 Then
For Each varItem In lstContactCategories.ItemsSelected
If lstContactCategories.ItemData(varItem) = "All" Then
strWhere = vbNullString
Exit For
Else
strWhere = strWhere & lstContactCategories.ItemData(varItem) & ", "
End If
Next varItem
If Len(strWhere) > 0 Then
strWhere = "tblWSBMarketingContacts.fldContactCategoryID IN (" & _
Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello Doug,

I presume your last post crossed with my second one and that you were
really responding to Danny's post. I still need help to resolve my
query.

Gordon
 
D

David W. Fenton

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.
Click to expand...

I would likely to that thus:

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

Another option would be to use DISTINCT in the first SELECT, but I
think that's less efficient than TOP 1 would be.

I think it's just better to have your subqueries produce unique
values so as not to force them to be processed by the UNION for
distinct values, since I don't believe that UNION's de-duping
utilizes indexes, whereas returning unique values in the component
SELECT statements *will* utilize the indexes.
 
D

David W. Fenton

If I suggested using another table, then I'd have had to explain
what you did. In my apps I usually have a table, like tblVersion,
with only 1 row. That's the one I usually use for these cases, but
explaining that will lead to a multitude of other questions. :)
Click to expand...

TOP 1 obviates the need for that.
 
D

Danny J. Lesandrini

I strongly agree with that, and the idea of using TOP, but by using
a table that I know has only one record, a table that I have in all
my databases, I get performance and save myself the trouble of
thinking too deeply about things. :)

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


think that's less efficient than TOP 1 would be.
 
G

Gordon

I strongly agree with that, and the idea of using TOP, but by using
a table that I know has only one record, a table that I have in all
my databases, I get performance and save myself the trouble of
thinking too deeply about things.  :)

--
Danny J. Lesandrini
(e-mail address removed)



 think that's less efficient than TOP 1 would be.






- Show quoted text -
Click to expand...

Thanks for the improved code, Doug. It's still not working but I
think we're almost there.

Selecting any option from the list box works but not the <All> which
produces no records. Which is not surprising because the debugged
code for that option shows
a WHERE clause ".........WHERE
tblWSBMarketingContacts.fldContactCategoryID IN (0);"

From your code I would have expected there to be no WHERE clause.
Just to remind, 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;

And the final part of the code now reads:

strWhere = vbNullString
If lstContactCategories.ItemsSelected.Count > 0 Then
For Each varItem In lstContactCategories.ItemsSelected
If lstContactCategories.ItemData(varItem) = "All" Then
strWhere = vbNullString
Exit For
Else
strWhere = strWhere & lstContactCategories.ItemData(varItem) &
", "
End If
Next varItem
If Len(strWhere) > 0 Then
strWhere = "tblWSBMarketingContacts.fldContactCategoryID IN (" & _
Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If

If strWhere <> "" Then
strSQL = strSelect & strFrom & " WHERE " & strWhere & ";"
Else
strSQL = strSelect & strFrom
End If

It doesn't seem to be picking up on the <All> option when selected in
the listbox.

GordonThanks for the improved code, Doug. It's still not working but
I think we're almost there.

Selecting any option from the list box works but not the <All> which
produces no records. Which is not surprising because the debugged
code for that option shows
a WHERE clause ".........WHERE
tblWSBMarketingContacts.fldContactCategoryID IN (0);"

From your code I would have expected there to be no WHERE clause.
Just to remind, 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;

And the final part of the code now reads:

strWhere = vbNullString
If lstContactCategories.ItemsSelected.Count > 0 Then
For Each varItem In lstContactCategories.ItemsSelected
If lstContactCategories.ItemData(varItem) = "All" Then
strWhere = vbNullString
Exit For
Else
strWhere = strWhere & lstContactCategories.ItemData(varItem) &
", "
End If
Next varItem
If Len(strWhere) > 0 Then
strWhere = "tblWSBMarketingContacts.fldContactCategoryID IN (" & _
Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If

If strWhere <> "" Then
strSQL = strSelect & strFrom & " WHERE " & strWhere & ";"
Else
strSQL = strSelect & strFrom
End If

It doesn't seem to be picking up on the <All> option when selected in
the listbox.

Gordon
 
D

Douglas J. Steele

You're putting [All] in your list box, but only checking for All in the
code.

Change the line of code

If lstContactCategories.ItemData(varItem) = "All" Then

to

If lstContactCategories.ItemData(varItem) = "[All]" Then


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I strongly agree with that, and the idea of using TOP, but by using
a table that I know has only one record, a table that I have in all
my databases, I get performance and save myself the trouble of
thinking too deeply about things. :)

--
Danny J. Lesandrini
(e-mail address removed)

message"Danny


think that's less efficient than TOP 1 would be.






- Show quoted text -
Click to expand...

Thanks for the improved code, Doug. It's still not working but I
think we're almost there.

Selecting any option from the list box works but not the <All> which
produces no records. Which is not surprising because the debugged
code for that option shows
a WHERE clause ".........WHERE
tblWSBMarketingContacts.fldContactCategoryID IN (0);"

From your code I would have expected there to be no WHERE clause.
Just to remind, 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;

And the final part of the code now reads:

strWhere = vbNullString
If lstContactCategories.ItemsSelected.Count > 0 Then
For Each varItem In lstContactCategories.ItemsSelected
If lstContactCategories.ItemData(varItem) = "All" Then
strWhere = vbNullString
Exit For
Else
strWhere = strWhere & lstContactCategories.ItemData(varItem) &
", "
End If
Next varItem
If Len(strWhere) > 0 Then
strWhere = "tblWSBMarketingContacts.fldContactCategoryID IN (" & _
Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If

If strWhere <> "" Then
strSQL = strSelect & strFrom & " WHERE " & strWhere & ";"
Else
strSQL = strSelect & strFrom
End If

It doesn't seem to be picking up on the <All> option when selected in
the listbox.

GordonThanks for the improved code, Doug. It's still not working but
I think we're almost there.

Selecting any option from the list box works but not the <All> which
produces no records. Which is not surprising because the debugged
code for that option shows
a WHERE clause ".........WHERE
tblWSBMarketingContacts.fldContactCategoryID IN (0);"

From your code I would have expected there to be no WHERE clause.
Just to remind, 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;

And the final part of the code now reads:

strWhere = vbNullString
If lstContactCategories.ItemsSelected.Count > 0 Then
For Each varItem In lstContactCategories.ItemsSelected
If lstContactCategories.ItemData(varItem) = "All" Then
strWhere = vbNullString
Exit For
Else
strWhere = strWhere & lstContactCategories.ItemData(varItem) &
", "
End If
Next varItem
If Len(strWhere) > 0 Then
strWhere = "tblWSBMarketingContacts.fldContactCategoryID IN (" & _
Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If

If strWhere <> "" Then
strSQL = strSelect & strFrom & " WHERE " & strWhere & ";"
Else
strSQL = strSelect & strFrom
End If

It doesn't seem to be picking up on the <All> option when selected in
the listbox.

Gordon
 
G

Gordon

You're putting [All] in your list box, but only checking for All in the
code.

Change the line of code

If lstContactCategories.ItemData(varItem) = "All" Then

to

If lstContactCategories.ItemData(varItem) = "[All]" Then

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)


I strongly agree with that, and the idea of using TOP, but by using
a table that I know has only one record, a table that I have in all
my databases, I get performance and save myself the trouble of
thinking too deeply about things. :)
Click to expand...
message"Danny
Click to expand...
think that's less efficient than TOP 1 would be.
Click to expand...
- Show quoted text -
Click to expand...

Thanks for the improved code, Doug.  It's still not working but I
think we're almost there.

Selecting any option from the list box works but not the <All> which
produces no records.  Which is not surprising because the debugged
code for that option shows
a WHERE clause ".........WHERE
tblWSBMarketingContacts.fldContactCategoryID IN (0);"

From your code I would have expected there to be no WHERE clause.
Just to remind, 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;

And the final part of the code now reads:

strWhere = vbNullString
If lstContactCategories.ItemsSelected.Count > 0 Then
  For Each varItem In lstContactCategories.ItemsSelected
    If lstContactCategories.ItemData(varItem) = "All" Then
      strWhere = vbNullString
      Exit For
    Else
      strWhere = strWhere & lstContactCategories.ItemData(varItem) &
", "
    End If
  Next varItem
  If Len(strWhere) > 0 Then
    strWhere = "tblWSBMarketingContacts.fldContactCategoryID IN (" & _
      Left(strWhere, Len(strWhere) - 2) & ")"
  End If
End If

If strWhere <> "" Then
strSQL = strSelect & strFrom & " WHERE " & strWhere & ";"
Else
strSQL = strSelect & strFrom
End If

It doesn't seem to be picking up on the <All> option when selected in
the listbox.

GordonThanks for the improved code, Doug.  It's still not working but
I think we're almost there.

Selecting any option from the list box works but not the <All> which
produces no records.  Which is not surprising because the debugged
code for that option shows
a WHERE clause ".........WHERE
tblWSBMarketingContacts.fldContactCategoryID IN (0);"

From your code I would have expected there to be no WHERE clause.
Just to remind, 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;

And the final part of the code now reads:

strWhere = vbNullString
If lstContactCategories.ItemsSelected.Count > 0 Then
  For Each varItem In lstContactCategories.ItemsSelected
    If lstContactCategories.ItemData(varItem) = "All" Then
      strWhere = vbNullString
      Exit For
    Else
      strWhere = strWhere & lstContactCategories.ItemData(varItem) &
", "
    End If
  Next varItem
  If Len(strWhere) > 0 Then
    strWhere = "tblWSBMarketingContacts.fldContactCategoryID IN (" & _
      Left(strWhere, Len(strWhere) - 2) & ")"
  End If
End If

If strWhere <> "" Then
strSQL = strSelect & strFrom & " WHERE " & strWhere & ";"
Else
strSQL = strSelect & strFrom
End If

It doesn't seem to be picking up on the <All> option when selected in
the listbox.

Gordon- Hide quoted text -

- Show quoted text -
Click to expand...

Nope, that didn't work. It still wouldn't pick up the "{All]" option,
but then I figured it was looking for 0 as the fldContactCategory, not
"[All]",
so I changed the line of code to:

If lstContactCategories.ItemData(varItem) = 0 Then
strWhere = vbNullString

.....and that did the trick. Go figure.

Thanks for your patience.

Gordon
 
D

David W. Fenton

I strongly agree with that, and the idea of using TOP, but by
using a table that I know has only one record, a table that I have
in all my databases, I get performance and save myself the trouble
of thinking too deeply about things. :)
Click to expand...

TOP N is extremely well-optimized. Of course, I try to use a table
with a small number of records, but I also like the idea of using
the same table for the ALL choice as is used for the real data,
simply because it is less confusing than using some other table.

In general, that oughtn't be a performance issue since any table
that is being used to populate a listbox or combo box shouldn't
really have a large number of records, since having so many records
presented to the user is not a friendly UI.

Another idea might be to create a saved query that is your ALL
source. Then you could hardwire it to whatever table you wanted, but
when used in a rowsource, it would be clear what it's for.
 
D

Douglas J. Steele

Aargh. Of course. Your list box is bound to the first column, while it's the
second column that contains the description.

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Gordon said:
- Show quoted text -
Click to expand...

Nope, that didn't work. It still wouldn't pick up the "{All]" option,
but then I figured it was looking for 0 as the fldContactCategory, not
"[All]",
so I changed the line of code to:

If lstContactCategories.ItemData(varItem) = 0 Then
strWhere = vbNullString

.....and that did the trick. Go figure.

Thanks for your patience.

Gordon
 

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

multi select list box in a form as a query parameter 3

Top