Multi Multi Select List boxes

S

Simon Glencross

Help or ideas needed please....

I have a multi select listbox where the user selects different catalogue
types and they click a command button which send the subscribers details to
labels which they then print off. This all works great BUT now they want to
be able to continue selecting the individual catalogue type but also print
them by catcost i.e. paid, trail or comp.

Here is the code which I am currently using, I was thinking about maybe
adding another list box with the cat costs in but I am unsure of how to
incorporate this in to the current setup. Any help would be much appreciated

Si

Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename, "
& _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE (((tblsubscriptions.Cattypes) In (" & strCriteria & ")) AND
((tblsubscriptions.Catcost)='Paid' Or (tblsubscriptions.Catcost)='Free' Or
(tblsubscriptions.Catcost)='Trial')) " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
 
D

Douglas J Steele

If you were to add a new listbox lstCatCost, you'd add code to check whether
items were selected in that listbox, the same as what you're already doing:

Dim strCatCost As String

For Each varItem In Me!lstCatCost.ItemsSelected
strCatCost = strCatCost & ",'" & Me!lstCatCost.ItemData(varItem) &
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If

Then, check whether or not you have to add that criteria to your SQL
statement:

strSQL = "SELECT DISTINCT tblSubscribers.Title, " & _
"tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE tblsubscriptions.Cattypes In (" & strCriteria & ") "
If Len(strCatCost) > 0 Then
strSQL = strSQL & "AND tblsubscriptions.Catcost In (" & strCatCost & ") "
End If
strSQL = strSQL & "ORDER BY tblsubscribers.surname"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
Help or ideas needed please....

I have a multi select listbox where the user selects different catalogue
types and they click a command button which send the subscribers details to
labels which they then print off. This all works great BUT now they want to
be able to continue selecting the individual catalogue type but also print
them by catcost i.e. paid, trail or comp.

Here is the code which I am currently using, I was thinking about maybe
adding another list box with the cat costs in but I am unsure of how to
incorporate this in to the current setup. Any help would be much appreciated

Si

Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename, "
& _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE (((tblsubscriptions.Cattypes) In (" & strCriteria & ")) AND
((tblsubscriptions.Catcost)='Paid' Or (tblsubscriptions.Catcost)='Free' Or
(tblsubscriptions.Catcost)='Trial')) " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
 
S

Simon Glencross

Douglas,

Thank you for your help I shall attempt this and see what happens, although
I did create the database I am not familar with VB at all would you be able
to advise where in more detail I can insert the addtional code?
Many thanks for your help

Simon

Douglas J Steele said:
If you were to add a new listbox lstCatCost, you'd add code to check
whether
items were selected in that listbox, the same as what you're already
doing:

Dim strCatCost As String

For Each varItem In Me!lstCatCost.ItemsSelected
strCatCost = strCatCost & ",'" & Me!lstCatCost.ItemData(varItem) &
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If

Then, check whether or not you have to add that criteria to your SQL
statement:

strSQL = "SELECT DISTINCT tblSubscribers.Title, " & _
"tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE tblsubscriptions.Cattypes In (" & strCriteria & ") "
If Len(strCatCost) > 0 Then
strSQL = strSQL & "AND tblsubscriptions.Catcost In (" & strCatCost & ")
"
End If
strSQL = strSQL & "ORDER BY tblsubscribers.surname"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
Help or ideas needed please....

I have a multi select listbox where the user selects different catalogue
types and they click a command button which send the subscribers details to
labels which they then print off. This all works great BUT now they want to
be able to continue selecting the individual catalogue type but also
print
them by catcost i.e. paid, trail or comp.

Here is the code which I am currently using, I was thinking about maybe
adding another list box with the cat costs in but I am unsure of how to
incorporate this in to the current setup. Any help would be much appreciated

Si

Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title,
tblSubscribers.Forename, "
& _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE (((tblsubscriptions.Cattypes) In (" & strCriteria & ")) AND
((tblsubscriptions.Catcost)='Paid' Or (tblsubscriptions.Catcost)='Free'
Or
(tblsubscriptions.Catcost)='Trial')) " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
 
D

Douglas J Steele

The code to assign strSQL replaces what you've currently got.

The code that checks the new list box precedes that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
Douglas,

Thank you for your help I shall attempt this and see what happens, although
I did create the database I am not familar with VB at all would you be able
to advise where in more detail I can insert the addtional code?
Many thanks for your help

Simon

Douglas J Steele said:
If you were to add a new listbox lstCatCost, you'd add code to check
whether
items were selected in that listbox, the same as what you're already
doing:

Dim strCatCost As String

For Each varItem In Me!lstCatCost.ItemsSelected
strCatCost = strCatCost & ",'" & Me!lstCatCost.ItemData(varItem) &
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If

Then, check whether or not you have to add that criteria to your SQL
statement:

strSQL = "SELECT DISTINCT tblSubscribers.Title, " & _
"tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE tblsubscriptions.Cattypes In (" & strCriteria & ") "
If Len(strCatCost) > 0 Then
strSQL = strSQL & "AND tblsubscriptions.Catcost In (" & strCatCost & ")
"
End If
strSQL = strSQL & "ORDER BY tblsubscribers.surname"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Help or ideas needed please....

I have a multi select listbox where the user selects different catalogue
types and they click a command button which send the subscribers
details
to
labels which they then print off. This all works great BUT now they
want
to
be able to continue selecting the individual catalogue type but also
print
them by catcost i.e. paid, trail or comp.

Here is the code which I am currently using, I was thinking about maybe
adding another list box with the cat costs in but I am unsure of how to
incorporate this in to the current setup. Any help would be much appreciated

Si

Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title,
tblSubscribers.Forename, "
& _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE (((tblsubscriptions.Cattypes) In (" & strCriteria & ")) AND
((tblsubscriptions.Catcost)='Paid' Or (tblsubscriptions.Catcost)='Free'
Or
(tblsubscriptions.Catcost)='Trial')) " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
 
S

Simon Glencross

Not sure if I explained myself clearly enough

Currently on the database you can select from a multi select list box
catalogue types then click a button whihc then prints off labels.

What they now want to be able to do is to select the catalogue type from the
first list box and have another list box with the Subscriber type eg Paid,
Free or Comp if they then select the subscriber type this should then print
off labels for example everyone who is subscribed to Fine Antiques who is
showing as paid...

Hope this makes better sense.

Simon


Douglas J Steele said:
The code to assign strSQL replaces what you've currently got.

The code that checks the new list box precedes that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
Douglas,

Thank you for your help I shall attempt this and see what happens, although
I did create the database I am not familar with VB at all would you be able
to advise where in more detail I can insert the addtional code?
Many thanks for your help

Simon

Douglas J Steele said:
If you were to add a new listbox lstCatCost, you'd add code to check
whether
items were selected in that listbox, the same as what you're already
doing:

Dim strCatCost As String

For Each varItem In Me!lstCatCost.ItemsSelected
strCatCost = strCatCost & ",'" &
Me!lstCatCost.ItemData(varItem) &
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If

Then, check whether or not you have to add that criteria to your SQL
statement:

strSQL = "SELECT DISTINCT tblSubscribers.Title, " & _
"tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE tblsubscriptions.Cattypes In (" & strCriteria & ") "
If Len(strCatCost) > 0 Then
strSQL = strSQL & "AND tblsubscriptions.Catcost In (" & strCatCost & ")
"
End If
strSQL = strSQL & "ORDER BY tblsubscribers.surname"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Help or ideas needed please....

I have a multi select listbox where the user selects different catalogue
types and they click a command button which send the subscribers details
to
labels which they then print off. This all works great BUT now they want
to
be able to continue selecting the individual catalogue type but also
print
them by catcost i.e. paid, trail or comp.

Here is the code which I am currently using, I was thinking about
maybe
adding another list box with the cat costs in but I am unsure of how
to
incorporate this in to the current setup. Any help would be much
appreciated

Si

Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem)
&
"'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title,
tblSubscribers.Forename,
"
& _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE (((tblsubscriptions.Cattypes) In (" & strCriteria & ")) AND
((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free'
Or
(tblsubscriptions.Catcost)='Trial')) " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
 
D

Douglas J Steele

I believe that's what I demonstrated how to do...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
Not sure if I explained myself clearly enough

Currently on the database you can select from a multi select list box
catalogue types then click a button whihc then prints off labels.

What they now want to be able to do is to select the catalogue type from the
first list box and have another list box with the Subscriber type eg Paid,
Free or Comp if they then select the subscriber type this should then print
off labels for example everyone who is subscribed to Fine Antiques who is
showing as paid...

Hope this makes better sense.

Simon


Douglas J Steele said:
The code to assign strSQL replaces what you've currently got.

The code that checks the new list box precedes that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thank you for your help I shall attempt this and see what happens, although
I did create the database I am not familar with VB at all would you be able
to advise where in more detail I can insert the addtional code?
Many thanks for your help

Simon

If you were to add a new listbox lstCatCost, you'd add code to check
whether
items were selected in that listbox, the same as what you're already
doing:

Dim strCatCost As String

For Each varItem In Me!lstCatCost.ItemsSelected
strCatCost = strCatCost & ",'" &
Me!lstCatCost.ItemData(varItem) &
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If

Then, check whether or not you have to add that criteria to your SQL
statement:

strSQL = "SELECT DISTINCT tblSubscribers.Title, " & _
"tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE tblsubscriptions.Cattypes In (" & strCriteria & ") "
If Len(strCatCost) > 0 Then
strSQL = strSQL & "AND tblsubscriptions.Catcost In (" & strCatCost
&
")
"
End If
strSQL = strSQL & "ORDER BY tblsubscribers.surname"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Help or ideas needed please....

I have a multi select listbox where the user selects different catalogue
types and they click a command button which send the subscribers details
to
labels which they then print off. This all works great BUT now they want
to
be able to continue selecting the individual catalogue type but also
print
them by catcost i.e. paid, trail or comp.

Here is the code which I am currently using, I was thinking about
maybe
adding another list box with the cat costs in but I am unsure of how
to
incorporate this in to the current setup. Any help would be much
appreciated

Si

Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem)
&
"'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title,
tblSubscribers.Forename,
"
& _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE (((tblsubscriptions.Cattypes) In (" & strCriteria & ")) AND
((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free'
Or
(tblsubscriptions.Catcost)='Trial')) " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
 
S

Simon Glencross

Hi Doug,

WOuld you mind checking if this looks ok?


Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim strCatCost As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem) & "'"
Next varItem
For Each varItem In Me!lstcatcost.ItemsSelected
strCatCost = strCatCost & ",'" & Me!lstcatcost.ItemData(varItem) &
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If



' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename, "
& _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub



Kind Regards

Simon

Douglas J Steele said:
I believe that's what I demonstrated how to do...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
Not sure if I explained myself clearly enough

Currently on the database you can select from a multi select list box
catalogue types then click a button whihc then prints off labels.

What they now want to be able to do is to select the catalogue type from the
first list box and have another list box with the Subscriber type eg
Paid,
Free or Comp if they then select the subscriber type this should then print
off labels for example everyone who is subscribed to Fine Antiques who is
showing as paid...

Hope this makes better sense.

Simon


Douglas J Steele said:
The code to assign strSQL replaces what you've currently got.

The code that checks the new list box precedes that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thank you for your help I shall attempt this and see what happens,
although
I did create the database I am not familar with VB at all would you be
able
to advise where in more detail I can insert the addtional code?
Many thanks for your help

Simon

message
If you were to add a new listbox lstCatCost, you'd add code to check
whether
items were selected in that listbox, the same as what you're already
doing:

Dim strCatCost As String

For Each varItem In Me!lstCatCost.ItemsSelected
strCatCost = strCatCost & ",'" &
Me!lstCatCost.ItemData(varItem)
&
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If

Then, check whether or not you have to add that criteria to your SQL
statement:

strSQL = "SELECT DISTINCT tblSubscribers.Title, " & _
"tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE tblsubscriptions.Cattypes In (" & strCriteria & ") "
If Len(strCatCost) > 0 Then
strSQL = strSQL & "AND tblsubscriptions.Catcost In (" & strCatCost &
")
"
End If
strSQL = strSQL & "ORDER BY tblsubscribers.surname"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Help or ideas needed please....

I have a multi select listbox where the user selects different
catalogue
types and they click a command button which send the subscribers
details
to
labels which they then print off. This all works great BUT now they
want
to
be able to continue selecting the individual catalogue type but
also
print
them by catcost i.e. paid, trail or comp.

Here is the code which I am currently using, I was thinking about
maybe
adding another list box with the cat costs in but I am unsure of
how
to
incorporate this in to the current setup. Any help would be much
appreciated

Si

Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text
string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem)
&
"'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list"
_
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title,
tblSubscribers.Forename,
"
& _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " &
_
"WHERE (((tblsubscriptions.Cattypes) In (" & strCriteria & ")) AND
((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free'
Or
(tblsubscriptions.Catcost)='Trial')) " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
 
D

Douglas J. Steele

No, it doesn't look correct. You forgot the code to add the WHERE clause
into your query, thereby negating all the work you did reading the selected
values from the list boxes!

Reread my original suggestion for what strSQL should be.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Simon Glencross said:
Hi Doug,

WOuld you mind checking if this looks ok?


Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim strCatCost As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem) & "'"
Next varItem
For Each varItem In Me!lstcatcost.ItemsSelected
strCatCost = strCatCost & ",'" & Me!lstcatcost.ItemData(varItem) &
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If



' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
" & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub



Kind Regards

Simon

Douglas J Steele said:
I believe that's what I demonstrated how to do...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
Not sure if I explained myself clearly enough

Currently on the database you can select from a multi select list box
catalogue types then click a button whihc then prints off labels.

What they now want to be able to do is to select the catalogue type from the
first list box and have another list box with the Subscriber type eg
Paid,
Free or Comp if they then select the subscriber type this should then print
off labels for example everyone who is subscribed to Fine Antiques who
is
showing as paid...

Hope this makes better sense.

Simon


The code to assign strSQL replaces what you've currently got.

The code that checks the new list box precedes that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thank you for your help I shall attempt this and see what happens,
although
I did create the database I am not familar with VB at all would you
be
able
to advise where in more detail I can insert the addtional code?
Many thanks for your help

Simon

message
If you were to add a new listbox lstCatCost, you'd add code to
check
whether
items were selected in that listbox, the same as what you're
already
doing:

Dim strCatCost As String

For Each varItem In Me!lstCatCost.ItemsSelected
strCatCost = strCatCost & ",'" &
Me!lstCatCost.ItemData(varItem)
&
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If

Then, check whether or not you have to add that criteria to your
SQL
statement:

strSQL = "SELECT DISTINCT tblSubscribers.Title, " & _
"tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " &
_
"WHERE tblsubscriptions.Cattypes In (" & strCriteria & ") "
If Len(strCatCost) > 0 Then
strSQL = strSQL & "AND tblsubscriptions.Catcost In (" &
strCatCost &
")
"
End If
strSQL = strSQL & "ORDER BY tblsubscribers.surname"
 
S

Simon Glencross

Thank you very much for all of your help!!

Much appreciated

Simon :)


Douglas J. Steele said:
No, it doesn't look correct. You forgot the code to add the WHERE clause
into your query, thereby negating all the work you did reading the
selected values from the list boxes!

Reread my original suggestion for what strSQL should be.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Simon Glencross said:
Hi Doug,

WOuld you mind checking if this looks ok?


Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim strCatCost As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem) &
"'"
Next varItem
For Each varItem In Me!lstcatcost.ItemsSelected
strCatCost = strCatCost & ",'" & Me!lstcatcost.ItemData(varItem)
& "'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If



' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
" & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub



Kind Regards

Simon

Douglas J Steele said:
I believe that's what I demonstrated how to do...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Not sure if I explained myself clearly enough

Currently on the database you can select from a multi select list box
catalogue types then click a button whihc then prints off labels.

What they now want to be able to do is to select the catalogue type
from
the
first list box and have another list box with the Subscriber type eg
Paid,
Free or Comp if they then select the subscriber type this should then
print
off labels for example everyone who is subscribed to Fine Antiques who
is
showing as paid...

Hope this makes better sense.

Simon


The code to assign strSQL replaces what you've currently got.

The code that checks the new list box precedes that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Douglas,

Thank you for your help I shall attempt this and see what happens,
although
I did create the database I am not familar with VB at all would you
be
able
to advise where in more detail I can insert the addtional code?
Many thanks for your help

Simon

message
If you were to add a new listbox lstCatCost, you'd add code to
check
whether
items were selected in that listbox, the same as what you're
already
doing:

Dim strCatCost As String

For Each varItem In Me!lstCatCost.ItemsSelected
strCatCost = strCatCost & ",'" &
Me!lstCatCost.ItemData(varItem)
&
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If

Then, check whether or not you have to add that criteria to your
SQL
statement:

strSQL = "SELECT DISTINCT tblSubscribers.Title, " & _
"tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " &
_
"WHERE tblsubscriptions.Cattypes In (" & strCriteria & ") "
If Len(strCatCost) > 0 Then
strSQL = strSQL & "AND tblsubscriptions.Catcost In (" &
strCatCost
&
")
"
End If
strSQL = strSQL & "ORDER BY tblsubscribers.surname"
 
S

Simon Glencross

I have another problem which may be linked to this which is if I select an
item in both list boxes and then click the command button to produce the
report/labels it works fine but if I come out of that form and then back in
again and repeat the above MS Access generates an error report and closes??

Any ideas?

Many Thanks


Douglas J. Steele said:
No, it doesn't look correct. You forgot the code to add the WHERE clause
into your query, thereby negating all the work you did reading the
selected values from the list boxes!

Reread my original suggestion for what strSQL should be.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Simon Glencross said:
Hi Doug,

WOuld you mind checking if this looks ok?


Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim strCatCost As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem) &
"'"
Next varItem
For Each varItem In Me!lstcatcost.ItemsSelected
strCatCost = strCatCost & ",'" & Me!lstcatcost.ItemData(varItem)
& "'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If



' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
" & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub



Kind Regards

Simon

Douglas J Steele said:
I believe that's what I demonstrated how to do...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Not sure if I explained myself clearly enough

Currently on the database you can select from a multi select list box
catalogue types then click a button whihc then prints off labels.

What they now want to be able to do is to select the catalogue type
from
the
first list box and have another list box with the Subscriber type eg
Paid,
Free or Comp if they then select the subscriber type this should then
print
off labels for example everyone who is subscribed to Fine Antiques who
is
showing as paid...

Hope this makes better sense.

Simon


The code to assign strSQL replaces what you've currently got.

The code that checks the new list box precedes that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Douglas,

Thank you for your help I shall attempt this and see what happens,
although
I did create the database I am not familar with VB at all would you
be
able
to advise where in more detail I can insert the addtional code?
Many thanks for your help

Simon

message
If you were to add a new listbox lstCatCost, you'd add code to
check
whether
items were selected in that listbox, the same as what you're
already
doing:

Dim strCatCost As String

For Each varItem In Me!lstCatCost.ItemsSelected
strCatCost = strCatCost & ",'" &
Me!lstCatCost.ItemData(varItem)
&
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If

Then, check whether or not you have to add that criteria to your
SQL
statement:

strSQL = "SELECT DISTINCT tblSubscribers.Title, " & _
"tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " &
_
"WHERE tblsubscriptions.Cattypes In (" & strCriteria & ") "
If Len(strCatCost) > 0 Then
strSQL = strSQL & "AND tblsubscriptions.Catcost In (" &
strCatCost
&
")
"
End If
strSQL = strSQL & "ORDER BY tblsubscribers.surname"
 
D

Douglas J Steele

You may need to decompile your application.

MichKa describes what this does at
http://www.trigeminal.com/usenet/usenet004.asp

I'd advise making a copy first, just in case...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Simon Glencross said:
I have another problem which may be linked to this which is if I select an
item in both list boxes and then click the command button to produce the
report/labels it works fine but if I come out of that form and then back in
again and repeat the above MS Access generates an error report and closes??

Any ideas?

Many Thanks


Douglas J. Steele said:
No, it doesn't look correct. You forgot the code to add the WHERE clause
into your query, thereby negating all the work you did reading the
selected values from the list boxes!

Reread my original suggestion for what strSQL should be.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi Doug,

WOuld you mind checking if this looks ok?


Private Sub Command14_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim strCatCost As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery16")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" & Me!List8.ItemData(varItem) &
"'"
Next varItem
For Each varItem In Me!lstcatcost.ItemsSelected
strCatCost = strCatCost & ",'" & Me!lstcatcost.ItemData(varItem)
& "'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If



' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
" & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"ORDER BY tblsubscribers.surname; "

Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Labels", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub



Kind Regards

Simon

I believe that's what I demonstrated how to do...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Not sure if I explained myself clearly enough

Currently on the database you can select from a multi select list box
catalogue types then click a button whihc then prints off labels.

What they now want to be able to do is to select the catalogue type
from
the
first list box and have another list box with the Subscriber type eg
Paid,
Free or Comp if they then select the subscriber type this should then
print
off labels for example everyone who is subscribed to Fine Antiques who
is
showing as paid...

Hope this makes better sense.

Simon


The code to assign strSQL replaces what you've currently got.

The code that checks the new list box precedes that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Douglas,

Thank you for your help I shall attempt this and see what happens,
although
I did create the database I am not familar with VB at all would you
be
able
to advise where in more detail I can insert the addtional code?
Many thanks for your help

Simon

message
If you were to add a new listbox lstCatCost, you'd add code to
check
whether
items were selected in that listbox, the same as what you're
already
doing:

Dim strCatCost As String

For Each varItem In Me!lstCatCost.ItemsSelected
strCatCost = strCatCost & ",'" &
Me!lstCatCost.ItemData(varItem)
&
"'"
Next varItem
If Len(strCatCost) > 0 Then
strCatCost = Right(strCatCost, Len(strCatCost) - 1)
End If

Then, check whether or not you have to add that criteria to your
SQL
statement:

strSQL = "SELECT DISTINCT tblSubscribers.Title, " & _
"tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " &
_
"WHERE tblsubscriptions.Cattypes In (" & strCriteria & ") "
If Len(strCatCost) > 0 Then
strSQL = strSQL & "AND tblsubscriptions.Catcost In (" &
strCatCost
&
")
"
End If
strSQL = strSQL & "ORDER BY tblsubscribers.surname"
 

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

Top