re posted

Nikos Yannacopoulos · Feb 15, 2005

But you are not creating the strSQL string! Change this section to:

strSQL = "SELECT 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 & ");"
Debug.Print strSQL

Do you get a proper strSQL statement in the immediate window now?
What's this * at the end of the select clause? Is the query itself
working properly (the one you copied the SQL statement form)?

Nikos

Nothing appears in the immediate window here is the code to show you where I
have inserted the debug.print strSQL.

SELECT 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 & ");"
Debug.Print strSQL

Many Thanks Simon

Do the debug trick, and post back the SQL expression that you get in the
immediate window (and the exact error message and line of code it
occurs on).

Nikos,

I have done what you suggested but still no difference and I still get

Click to expand...

the

same syntax error message, any ideas why?

Simon

You have forgotten to precede the WHERE with a space!
Tip: in your code, insert a

debug.print strSQL

line right after strSQL statement is created, so you can see exactly
what you are doing in the immediate window (Ctrl+G). It would have made
the problem obvious right away.

Nikos

Simonglencross wrote:

Nikos,

I copied and pasted the following statemant but I am geting a syntax

error,

any ideas what I am doing wrong?
strSQL = "SELECT tblsubscriptions.Catcost, 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 & ");"

Kind Regards

Simon

Simon,

Let Access do the hard work for you! Make the query that you need in
design view, i.e. add the required tables (and joins) and drag the
fields you need down to the grid, omitting the filter on cattypes;

Click to expand...

then

revert to SQL view, and the SQL expression is ready for you to copy

Click to expand...

and

paste in your code, adding just the WHERE clause exactly as you are

doing.

HTH,
Nikos

Simonglencross wrote:

Nikos,

You will pleased to hear I am making some progress I have now go the

multi

select list box to work with the following statement

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("allsubscribersnew")
' 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)

Click to expand...

&

"'"

Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
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 * FROM tblsubscriptions " & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria &

Click to expand...

");"

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "allsubscribersnew"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

This diplays to my screen mailinglistid of each subscriber and also

Click to expand...

the

cat

type, but I also need it to show me the name and address of each

subscriber

and the catcost, once it has done that I then need it to out put to

labels

automaticaly! Have you any ideas I have tried adding this statement

Click to expand...

in

but

it doesnt work.

strSQL = "SELECT tblSubscribers.Title tblSubscribers.Forename
tblSubscribers.Surname & _
tblSubscribers.Company tblSubscribers.Address
tblSubscribers.City tblSubscribers.[Country/Region] & _
tblSubscribers.PostalCode tblsubscriptions.Catcost *

FROM

tblSubscribers INNER JOIN tblsubscriptions & _
ON tblSubscribers.MailingListID =

tblsubscriptions.MailingListID

" & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria &

Click to expand...

");"

All of your help is much appreciated!

Simon

Simon,

Add a command button on the same form to open the report; the code
behind the button's click event should be something like:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "MTyp_ID = '" & Me.[Catalogue Types].ItemData(itm) &

"'

AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line between

Click to expand...

the

For and Next;
* I have assumed the PK field in table tblSubscriptions to be named
MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the

Click to expand...

actual

name;

* remember, remove the WHERE clause on mag types from the original

query.

HTH,
Nikos

Simonglencross wrote:

Sorry Nikos you loast me a bit here I have now got a form called
frmLabelsSelection on this I have an unbound list box called

Catalogue

Types

with my different types of Catalogues. The list box has the

multiselect

option enabled. Where would you suggest I go from here?

Kind Regards

Simon

"Nikos Yannacopoulos" <[email protected]> wrote in

Click to expand...

message

Simon,

I would remove the mag type filtering from the query altogether,

Click to expand...

and

use

some VB code to "read" the listbox selections and put together a

WHERE

condition string and impose it on the report upon opening it (this
assumes the report is opened through a button on the same form.

Click to expand...

The

code

behind the button would look something like:

For Each itm In Me.lstMTyp_ID
ctn = ctn & "MTyp_ID = '" & Me.lstMTyp_ID.ItemData(itm) & "' AND

"

Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Note: code above assumes MTyp_ID is type text; if numeric, use

Click to expand...

this

second line instead:

ctn = ctn & "MTyp_ID = " & Me.lstMTyp_ID.ItemData(itm) & " AND "

HTH,
Nikos

Simonglencross wrote:

Nikos,

The VBA way sounds like the better choice would you have any

resources

or

information which would help me?I'm reading up on the multi

Click to expand...

select

list

boxes at the moment.

Thanks again Nikos!

message

Simon,

The "proper" way to do it is to use a multi-select listbox on a

form,

and some VBA code behind it. The "quick and dirty" way (i.e. no

coding)

is to add an extra field in tblMagazineTypes (type yes/no), and

show

that on the form (in a subform?) in either continuous forms or

datasheet

view, so the user can click for including a type; then you will

need

to

include this field in your query as well, and filter on the True

values.

HTH,
Nikos

Simonglencross wrote:

Thanks for that Nikos,

Do you know how I can set this up so the user only has to

Click to expand...

select

the

magazine types from a form and then clicks a button to print

Click to expand...

out

the

labels,

I can't get me head round it at all.

Many Thanks

Simon

message

Simon,

Your data structure seems to be correct; if it's what I think

Click to expand...

it

is

based on your last post, then the query to print your labels

Click to expand...

off

of

should look something like:

SELECT tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

FROM (tblMagazineTypes INNER JOIN tblSubscriptions ON

tblMagazineTypes.MTyp_ID = tblSubscriptions.MTyp_ID) INNER

Click to expand...

JOIN

tblSubscribers ON tblSubscriptions.Sber_ID =

tblSubscribers.Sber_ID

WHERE (((tblMagazineTypes.MTyp_ID)="mt1" Or
(tblMagazineTypes.MTyp_ID)="mt2" Or

(tblMagazineTypes.MTyp_ID)="mt4"))

GROUP BY tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

(hope my assumptions on field names are clear!)

HTH,
Nikos

Simonglencross wrote:

I shall try and explain this a little better.

I have three tables tblSubscribers, tblSubscriptions and

tblMagazineTypes.

tblSubscribers contains all names and addresses'

tblsubscriptions

holds

all

of the subscriptions information i.e what people have

subscribed

to

and

the

last table tblMagazineTypes contains the 6 different magazime

types.

I need to be able to set up a way where the user can select

Click to expand...

any

magazine

type combination and print off the labels with the

Click to expand...

subscribers

names

and

addresses on rememebering that I only need one label per

subscriber

regardless of the magazine type. for example mr smith may

Click to expand...

have

subscribed to

magazine 1,2 and 3 and the operator has selected to print

labels

for

magazine 1 and 3 in this instance I would only want 1 address

label

and

not

2.

Hope this helps a little more!

message

Can you give us some sample data?

Simonglencross wrote:

I understand this but the database already has a large

Click to expand...

amount

of

data

in

it,

to do what you suggest would invole alot of work is there

another

way?

or

can you explain what I would need to do with the current

data?

Your help is much appreciated!

Many Thanks

Simon

"Nikos Yannacopoulos" <[email protected]> wrote

Click to expand...

in

message

Simon,

This is a classic many-to-many relationship between

subscriber

s

and

magazines. To handle efficiently, you need to modify your

data

design

so

you have one table for subscribers (like you already do),

one

for

magazines (tblMagazines with an ID field, name field plus

whatever

else

required), and a third table called tblSubscriptions (with

Click to expand...

a

subscriber

ID and a magazine ID foreign keys) to represent actual

subcriptions,

breaking the many-to-many relationship down to two

one-to-many

ones.

HTH,
Nikos

Simonglencross wrote:

Sory for reposting but desperate for some help

I have created a database and have two tables one called

tblSubscribers

and

another called tblSubscriptions. Within these tables I

Click to expand...

have

a

number

of

subscribers and 6 different magazine type's setup in the

subscriptions

table.
I had setup the database to print off labels, these where

separated

in

to

cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a subscriber to

subscribe

to

any number of catalogues and these catalogues may well be

sent

out

together

for example this week I am sending out cat 1, cat 2 and

cat3

but

next

week

it may be cat 2 and cat 6 but I also need to be careful

Click to expand...

as

1

subscriber

might subscribe to more than 1 catalogue and therefore I

would

only

want

one

label with the subscribers name and address and not one

Click to expand...

for

each

catalogue

selected.

Any advise would be much appreciated, I hope you can

understand

what

I

am

trying to achieve.

Kind Regards

Simon

Click to expand...

Click to expand...

Simonglencross · Feb 15, 2005

Nikos,

I see what I was doing wrong now Sorry Nikos!

Ok that works great the only other two things I need to do now are

If I select a for instance cat 1 and cat 2 from the list box and a
subscriber has subscribed to both cat 1 and cat 2 I only want 1 label
printed out for that Subscriber.

The other thing is I want instead of the query screen displaying the data I
want to be able to print off the labels

Curretly at the end I have

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "allsubscribersnew", _
view:=acViewPreview

This diplays the results to screen.

Many Thanks

Simon

Nikos Yannacopoulos said:
But you are not creating the strSQL string! Change this section to:

strSQL = "SELECT 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 & ");"
Debug.Print strSQL

Do you get a proper strSQL statement in the immediate window now?
What's this * at the end of the select clause? Is the query itself
working properly (the one you copied the SQL statement form)?

Nikos

Nothing appears in the immediate window here is the code to show you where I
have inserted the debug.print strSQL.

SELECT 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 & ");"
Debug.Print strSQL

Many Thanks Simon

Do the debug trick, and post back the SQL expression that you get in the
immediate window (and the exact error message and line of code it
occurs on).

Simonglencross wrote:

Nikos,

I have done what you suggested but still no difference and I still get
the

same syntax error message, any ideas why?

Simon

You have forgotten to precede the WHERE with a space!
Tip: in your code, insert a

debug.print strSQL

line right after strSQL statement is created, so you can see exactly
what you are doing in the immediate window (Ctrl+G). It would have made
the problem obvious right away.

Nikos

Simonglencross wrote:

Nikos,

I copied and pasted the following statemant but I am geting a syntax

error,

any ideas what I am doing wrong?
strSQL = "SELECT tblsubscriptions.Catcost, 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 & ");"

Kind Regards

Simon

Simon,

Let Access do the hard work for you! Make the query that you need in
design view, i.e. add the required tables (and joins) and drag the
fields you need down to the grid, omitting the filter on cattypes;
then

revert to SQL view, and the SQL expression is ready for you to copy
and

paste in your code, adding just the WHERE clause exactly as you are

doing.

HTH,
Nikos

Simonglencross wrote:

Nikos,

You will pleased to hear I am making some progress I have now go the

multi

select list box to work with the following statement

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("allsubscribersnew")
' 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 select anything from the list" _
, vbExclamation, "Nothing to find!"
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 * FROM tblsubscriptions " & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria &
");"

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "allsubscribersnew"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

This diplays to my screen mailinglistid of each subscriber and also
the

cat

type, but I also need it to show me the name and address of each

subscriber

and the catcost, once it has done that I then need it to out put to

labels

automaticaly! Have you any ideas I have tried adding this statement
in

but

it doesnt work.

strSQL = "SELECT tblSubscribers.Title tblSubscribers.Forename
tblSubscribers.Surname & _
tblSubscribers.Company tblSubscribers.Address
tblSubscribers.City tblSubscribers.[Country/Region] & _
tblSubscribers.PostalCode tblsubscriptions.Catcost *

FROM

tblSubscribers INNER JOIN tblsubscriptions & _
ON tblSubscribers.MailingListID =

tblsubscriptions.MailingListID

" & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria &
");"

All of your help is much appreciated!

Simon

Simon,

Add a command button on the same form to open the report; the code
behind the button's click event should be something like:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "MTyp_ID = '" & Me.[Catalogue Types].ItemData(itm) &

"'

AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line between
the

For and Next;
* I have assumed the PK field in table tblSubscriptions to be named
MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the
actual

name;

* remember, remove the WHERE clause on mag types from the original

query.

HTH,
Nikos

Simonglencross wrote:

Sorry Nikos you loast me a bit here I have now got a form called
frmLabelsSelection on this I have an unbound list box called

Catalogue

Types

with my different types of Catalogues. The list box has the

multiselect

option enabled. Where would you suggest I go from here?

Kind Regards

Simon

message

Simon,

I would remove the mag type filtering from the query altogether,
and

use

some VB code to "read" the listbox selections and put together a

WHERE

condition string and impose it on the report upon opening it (this
assumes the report is opened through a button on the same form.
The

code

behind the button would look something like:

For Each itm In Me.lstMTyp_ID
ctn = ctn & "MTyp_ID = '" & Me.lstMTyp_ID.ItemData(itm) & "' AND

"

Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Note: code above assumes MTyp_ID is type text; if numeric, use
this

second line instead:

ctn = ctn & "MTyp_ID = " & Me.lstMTyp_ID.ItemData(itm) & " AND "

HTH,
Nikos

Simonglencross wrote:

Nikos,

The VBA way sounds like the better choice would you have any

resources

or

information which would help me?I'm reading up on the multi
select

list

boxes at the moment.

Thanks again Nikos!

message

Simon,

The "proper" way to do it is to use a multi-select listbox on a

form,

and some VBA code behind it. The "quick and dirty" way (i.e. no

coding)

is to add an extra field in tblMagazineTypes (type yes/no), and

show

that on the form (in a subform?) in either continuous forms or

datasheet

view, so the user can click for including a type; then you will

need

to

include this field in your query as well, and filter on the True

values.

HTH,
Nikos

Simonglencross wrote:

Thanks for that Nikos,

Do you know how I can set this up so the user only has to
select

the

magazine types from a form and then clicks a button to print
out

the

labels,

I can't get me head round it at all.

Many Thanks

Simon

message

Simon,

Your data structure seems to be correct; if it's what I

Click to expand...

think

it

is

based on your last post, then the query to print your labels
off

of

should look something like:

SELECT tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

FROM (tblMagazineTypes INNER JOIN tblSubscriptions ON

tblMagazineTypes.MTyp_ID = tblSubscriptions.MTyp_ID) INNER
JOIN

tblSubscribers ON tblSubscriptions.Sber_ID =

tblSubscribers.Sber_ID

WHERE (((tblMagazineTypes.MTyp_ID)="mt1" Or
(tblMagazineTypes.MTyp_ID)="mt2" Or

(tblMagazineTypes.MTyp_ID)="mt4"))

GROUP BY tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

(hope my assumptions on field names are clear!)

HTH,
Nikos

Simonglencross wrote:

I shall try and explain this a little better.

I have three tables tblSubscribers, tblSubscriptions and

tblMagazineTypes.

tblSubscribers contains all names and addresses'

tblsubscriptions

holds

all

of the subscriptions information i.e what people have

subscribed

to

and

the

last table tblMagazineTypes contains the 6 different magazime

types.

I need to be able to set up a way where the user can select
any

magazine

type combination and print off the labels with the
subscribers

names

and

addresses on rememebering that I only need one label per

subscriber

regardless of the magazine type. for example mr smith may
have

subscribed to

magazine 1,2 and 3 and the operator has selected to print

labels

for

magazine 1 and 3 in this instance I would only want 1 address

label

and

not

2.

Hope this helps a little more!

message

Can you give us some sample data?

Simonglencross wrote:

I understand this but the database already has a large
amount

of

data

in

it,

to do what you suggest would invole alot of work is there

another

way?

or

can you explain what I would need to do with the current

data?

Your help is much appreciated!

Many Thanks

Simon

in

message

Simon,

This is a classic many-to-many relationship between

subscriber

s

and

magazines. To handle efficiently, you need to modify your

data

design

so

you have one table for subscribers (like you already do),

one

for

magazines (tblMagazines with an ID field, name field plus

whatever

else

required), and a third table called tblSubscriptions

Click to expand...

(with

a

subscriber

ID and a magazine ID foreign keys) to represent actual

subcriptions,

breaking the many-to-many relationship down to two

one-to-many

ones.

HTH,
Nikos

Simonglencross wrote:

Sory for reposting but desperate for some help

I have created a database and have two tables one called

tblSubscribers

and

another called tblSubscriptions. Within these tables I
have

a

number

of

subscribers and 6 different magazine type's setup in the

subscriptions

table.
I had setup the database to print off labels, these where

separated

in

to

cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a subscriber to

subscribe

to

any number of catalogues and these catalogues may well be

sent

out

together

for example this week I am sending out cat 1, cat 2 and

cat3

but

next

week

it may be cat 2 and cat 6 but I also need to be careful
as

1

subscriber

might subscribe to more than 1 catalogue and therefore I

would

only

want

one

label with the subscribers name and address and not one
for

each

catalogue

selected.

Any advise would be much appreciated, I hope you can

understand

what

I

am

trying to achieve.

Kind Regards

Simon

Click to expand...

Click to expand...

Nikos Yannacopoulos · Feb 15, 2005

For single records:

strSQL = "SELECT 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 & ") " & _
"GROUP BY tblSubscribers.Title, tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode, * "
Debug.Print strSQL

or something like that... To check, go back to the query where you
copied the SQL string from, and set the Totals to on (View > Totals",
keeping the default setting Group By. While you are at it, save the
query, you will need it for the report.

Coming to your second question, you don't need to modify the query
everytime! Keep the saved query as above, i.e. with no filter on
cattypes, and base your report on it, so it will include all subscribers
if opened directly by hand. Then with the use of the code, you can apply
the filter on the report at runtime, without changing the query. This is
exactly what my original code sample did. Assuming you have saved the
base query (no filtering) and based your report on it, the code bhind
the button should be:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "Typ_ID = '" & Me.[Catalogue Types].ItemData(itm) & "'
AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line between the
For and Next;
* I have assumed the PK field in table tblSubscriptions to be named
MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the actual name;
* remember, remove the WHERE clause on mag types from the original query.

This is actually my first post this morning, five postings ago! Do you
see now?

Nikos

Nikos,

I see what I was doing wrong now Sorry Nikos!

Ok that works great the only other two things I need to do now are

If I select a for instance cat 1 and cat 2 from the list box and a
subscriber has subscribed to both cat 1 and cat 2 I only want 1 label
printed out for that Subscriber.

The other thing is I want instead of the query screen displaying the data I
want to be able to print off the labels

Curretly at the end I have

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "allsubscribersnew", _
view:=acViewPreview

This diplays the results to screen.

Many Thanks

Simon

But you are not creating the strSQL string! Change this section to:

strSQL = "SELECT 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 & ");"
Debug.Print strSQL

Do you get a proper strSQL statement in the immediate window now?
What's this * at the end of the select clause? Is the query itself
working properly (the one you copied the SQL statement form)?

Nikos

Nothing appears in the immediate window here is the code to show you

Click to expand...

Click to expand...

where I

have inserted the debug.print strSQL.

SELECT 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 & ");"
Debug.Print strSQL

Many Thanks Simon

Do the debug trick, and post back the SQL expression that you get in the
immediate window (and the exact error message and line of code it
occurs on).

Simonglencross wrote:

Nikos,

I have done what you suggested but still no difference and I still get

the

same syntax error message, any ideas why?

Simon

You have forgotten to precede the WHERE with a space!
Tip: in your code, insert a

debug.print strSQL

line right after strSQL statement is created, so you can see exactly
what you are doing in the immediate window (Ctrl+G). It would have

Click to expand...

made

the problem obvious right away.

Nikos

Simonglencross wrote:

Nikos,

I copied and pasted the following statemant but I am geting a syntax

error,

any ideas what I am doing wrong?
strSQL = "SELECT tblsubscriptions.Catcost, 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 &

Click to expand...

");"

Kind Regards

Simon

Simon,

Let Access do the hard work for you! Make the query that you need in
design view, i.e. add the required tables (and joins) and drag the
fields you need down to the grid, omitting the filter on cattypes;

then

revert to SQL view, and the SQL expression is ready for you to copy

and

paste in your code, adding just the WHERE clause exactly as you are

doing.

HTH,
Nikos

Simonglencross wrote:

Nikos,

You will pleased to hear I am making some progress I have now go

Click to expand...

the

multi

select list box to work with the following statement

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("allsubscribersnew")
' 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 select anything from the list" _
, vbExclamation, "Nothing to find!"
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 * FROM tblsubscriptions " & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria &

");"

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "allsubscribersnew"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

This diplays to my screen mailinglistid of each subscriber and also

the

cat

type, but I also need it to show me the name and address of each

subscriber

and the catcost, once it has done that I then need it to out put to

labels

automaticaly! Have you any ideas I have tried adding this statement

in

but

it doesnt work.

strSQL = "SELECT tblSubscribers.Title tblSubscribers.Forename
tblSubscribers.Surname & _
tblSubscribers.Company tblSubscribers.Address
tblSubscribers.City tblSubscribers.[Country/Region] & _
tblSubscribers.PostalCode tblsubscriptions.Catcost *

FROM

tblSubscribers INNER JOIN tblsubscriptions & _
ON tblSubscribers.MailingListID =

tblsubscriptions.MailingListID

" & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria &

");"

All of your help is much appreciated!

Simon

"Nikos Yannacopoulos" <[email protected]> wrote in

Click to expand...

message

Simon,

Add a command button on the same form to open the report; the code
behind the button's click event should be something like:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "MTyp_ID = '" & Me.[Catalogue Types].ItemData(itm) &

"'

AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line between

the

For and Next;
* I have assumed the PK field in table tblSubscriptions to be

Click to expand...

named

MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the

actual

name;

* remember, remove the WHERE clause on mag types from the original

query.

HTH,
Nikos

Simonglencross wrote:

Sorry Nikos you loast me a bit here I have now got a form called
frmLabelsSelection on this I have an unbound list box called

Catalogue

Types

with my different types of Catalogues. The list box has the

multiselect

option enabled. Where would you suggest I go from here?

Kind Regards

Simon

message

Simon,

I would remove the mag type filtering from the query altogether,

and

use

some VB code to "read" the listbox selections and put together a

WHERE

condition string and impose it on the report upon opening it

Click to expand...

(this

assumes the report is opened through a button on the same form.

The

code

behind the button would look something like:

For Each itm In Me.lstMTyp_ID
ctn = ctn & "MTyp_ID = '" & Me.lstMTyp_ID.ItemData(itm) & "'

Click to expand...

AND

"

Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Note: code above assumes MTyp_ID is type text; if numeric, use

this

second line instead:

ctn = ctn & "MTyp_ID = " & Me.lstMTyp_ID.ItemData(itm) & " AND "

HTH,
Nikos

Simonglencross wrote:

Nikos,

The VBA way sounds like the better choice would you have any

resources

or

information which would help me?I'm reading up on the multi

select

list

boxes at the moment.

Thanks again Nikos!

message

Simon,

The "proper" way to do it is to use a multi-select listbox on

Click to expand...

a

form,

and some VBA code behind it. The "quick and dirty" way (i.e.

Click to expand...

no

coding)

is to add an extra field in tblMagazineTypes (type yes/no),

Click to expand...

and

show

that on the form (in a subform?) in either continuous forms or

datasheet

view, so the user can click for including a type; then you

Click to expand...

will

need

to

include this field in your query as well, and filter on the

Click to expand...

True

values.

HTH,
Nikos

Simonglencross wrote:

Thanks for that Nikos,

Do you know how I can set this up so the user only has to

select

the

magazine types from a form and then clicks a button to print

out

the

labels,

I can't get me head round it at all.

Many Thanks

Simon

message

Simon,

Your data structure seems to be correct; if it's what I

Click to expand...

think

it

is

based on your last post, then the query to print your labels

off

of

should look something like:

SELECT tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

FROM (tblMagazineTypes INNER JOIN tblSubscriptions ON

tblMagazineTypes.MTyp_ID = tblSubscriptions.MTyp_ID) INNER

JOIN

tblSubscribers ON tblSubscriptions.Sber_ID =

tblSubscribers.Sber_ID

WHERE (((tblMagazineTypes.MTyp_ID)="mt1" Or
(tblMagazineTypes.MTyp_ID)="mt2" Or

(tblMagazineTypes.MTyp_ID)="mt4"))

GROUP BY tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

(hope my assumptions on field names are clear!)

HTH,
Nikos

Simonglencross wrote:

I shall try and explain this a little better.

I have three tables tblSubscribers, tblSubscriptions and

tblMagazineTypes.

tblSubscribers contains all names and addresses'

tblsubscriptions

holds

all

of the subscriptions information i.e what people have

subscribed

to

and

the

last table tblMagazineTypes contains the 6 different

Click to expand...

magazime

types.

I need to be able to set up a way where the user can select

any

magazine

type combination and print off the labels with the

subscribers

names

and

addresses on rememebering that I only need one label per

subscriber

regardless of the magazine type. for example mr smith may

have

subscribed to

magazine 1,2 and 3 and the operator has selected to print

labels

for

magazine 1 and 3 in this instance I would only want 1

Click to expand...

address

label

and

not

2.

Hope this helps a little more!

"Nikos Yannacopoulos" <[email protected]> wrote

Click to expand...

in

message

Can you give us some sample data?

Simonglencross wrote:

I understand this but the database already has a large

amount

of

data

in

it,

to do what you suggest would invole alot of work is there

another

way?

or

can you explain what I would need to do with the current

data?

Your help is much appreciated!

Many Thanks

Simon

in

message

Simon,

This is a classic many-to-many relationship between

subscriber

s

and

magazines. To handle efficiently, you need to modify

Click to expand...

your

data

design

so

you have one table for subscribers (like you already

Click to expand...

do),

one

for

magazines (tblMagazines with an ID field, name field

Click to expand...

plus

whatever

else

required), and a third table called tblSubscriptions

Click to expand...

(with

a

subscriber

ID and a magazine ID foreign keys) to represent actual

subcriptions,

breaking the many-to-many relationship down to two

one-to-many

ones.

HTH,
Nikos

Simonglencross wrote:

Sory for reposting but desperate for some help

I have created a database and have two tables one

Click to expand...

called

tblSubscribers

and

another called tblSubscriptions. Within these tables I

have

a

number

of

subscribers and 6 different magazine type's setup in

Click to expand...

the

subscriptions

table.
I had setup the database to print off labels, these

Click to expand...

where

separated

in

to

cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a subscriber

Click to expand...

to

subscribe

to

any number of catalogues and these catalogues may well

Click to expand...

be

sent

out

together

for example this week I am sending out cat 1, cat 2 and

cat3

but

next

week

it may be cat 2 and cat 6 but I also need to be careful

as

1

subscriber

might subscribe to more than 1 catalogue and therefore

Click to expand...

I

would

only

want

one

label with the subscribers name and address and not one

for

each

catalogue

selected.

Any advise would be much appreciated, I hope you can

understand

what

I

am

trying to achieve.

Kind Regards

Simon

Click to expand...

Click to expand...

Simonglencross · Feb 15, 2005

Nikko,

Just adding in the Group BY and I am getting an error miss operator in Query
expression '*' and the following appears in the immediate window

SELECT 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('1','2','3','4','5','6','7') GROUP BY
tblSubscribers.Title, tblSubscribers.Forename, tblSubscribers.Surname,
tblSubscribers.Company, tblSubscribers.Address, tblSubscribers.City,
tblSubscribers.[Country/Region], tblSubscribers.PostalCode, *

Any Ideas?

Thanks Simon

Nikos Yannacopoulos said:
For single records:

strSQL = "SELECT 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 & ") " & _
"GROUP BY tblSubscribers.Title, tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode, * "
Debug.Print strSQL

or something like that... To check, go back to the query where you
copied the SQL string from, and set the Totals to on (View > Totals",
keeping the default setting Group By. While you are at it, save the
query, you will need it for the report.

Coming to your second question, you don't need to modify the query
everytime! Keep the saved query as above, i.e. with no filter on
cattypes, and base your report on it, so it will include all subscribers
if opened directly by hand. Then with the use of the code, you can apply
the filter on the report at runtime, without changing the query. This is
exactly what my original code sample did. Assuming you have saved the
base query (no filtering) and based your report on it, the code bhind
the button should be:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "Typ_ID = '" & Me.[Catalogue Types].ItemData(itm) & "'
AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line between the
For and Next;
* I have assumed the PK field in table tblSubscriptions to be named
MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the actual name;
* remember, remove the WHERE clause on mag types from the original query.

This is actually my first post this morning, five postings ago! Do you
see now?

Nikos

Nikos,

I see what I was doing wrong now Sorry Nikos!

Ok that works great the only other two things I need to do now are

If I select a for instance cat 1 and cat 2 from the list box and a
subscriber has subscribed to both cat 1 and cat 2 I only want 1 label
printed out for that Subscriber.

The other thing is I want instead of the query screen displaying the data I
want to be able to print off the labels

Curretly at the end I have

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "allsubscribersnew", _
view:=acViewPreview

This diplays the results to screen.

Many Thanks

Simon

But you are not creating the strSQL string! Change this section to:

strSQL = "SELECT 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 & ");"
Debug.Print strSQL

Do you get a proper strSQL statement in the immediate window now?
What's this * at the end of the select clause? Is the query itself
working properly (the one you copied the SQL statement form)?

Simonglencross wrote:

Nikos

Nothing appears in the immediate window here is the code to show you

Click to expand...

where I

have inserted the debug.print strSQL.

SELECT 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 & ");"
Debug.Print strSQL

Many Thanks Simon

Do the debug trick, and post back the SQL expression that you get in the
immediate window (and the exact error message and line of code it
occurs on).

Simonglencross wrote:

Nikos,

I have done what you suggested but still no difference and I still get

the

same syntax error message, any ideas why?

Simon

You have forgotten to precede the WHERE with a space!
Tip: in your code, insert a

debug.print strSQL

line right after strSQL statement is created, so you can see exactly
what you are doing in the immediate window (Ctrl+G). It would have
made

the problem obvious right away.

Nikos

Simonglencross wrote:

Nikos,

I copied and pasted the following statemant but I am geting a syntax

error,

any ideas what I am doing wrong?
strSQL = "SELECT tblsubscriptions.Catcost, 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 &
");"

Kind Regards

Simon

Simon,

Let Access do the hard work for you! Make the query that you need in
design view, i.e. add the required tables (and joins) and drag the
fields you need down to the grid, omitting the filter on cattypes;

then

revert to SQL view, and the SQL expression is ready for you to copy

and

paste in your code, adding just the WHERE clause exactly as you are

doing.

HTH,
Nikos

Simonglencross wrote:

Nikos,

You will pleased to hear I am making some progress I have now go
the

multi

select list box to work with the following statement

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("allsubscribersnew")
' 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 select anything from the list" _
, vbExclamation, "Nothing to find!"
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 * FROM tblsubscriptions " & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria &

");"

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "allsubscribersnew"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

This diplays to my screen mailinglistid of each subscriber and also

the

cat

type, but I also need it to show me the name and address of each

subscriber

and the catcost, once it has done that I then need it to out put to

labels

automaticaly! Have you any ideas I have tried adding this statement

in

but

it doesnt work.

strSQL = "SELECT tblSubscribers.Title tblSubscribers.Forename
tblSubscribers.Surname & _
tblSubscribers.Company tblSubscribers.Address
tblSubscribers.City tblSubscribers.[Country/Region] & _
tblSubscribers.PostalCode tblsubscriptions.Catcost *

FROM

tblSubscribers INNER JOIN tblsubscriptions & _
ON tblSubscribers.MailingListID =

tblsubscriptions.MailingListID

" & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria &

");"

All of your help is much appreciated!

Simon

message

Simon,

Add a command button on the same form to open the report; the code
behind the button's click event should be something like:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "MTyp_ID = '" & Me.[Catalogue Types].ItemData(itm) &

"'

AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line between

the

For and Next;
* I have assumed the PK field in table tblSubscriptions to be
named

MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the

actual

name;

* remember, remove the WHERE clause on mag types from the original

query.

HTH,
Nikos

Simonglencross wrote:

Sorry Nikos you loast me a bit here I have now got a form called
frmLabelsSelection on this I have an unbound list box called

Catalogue

Types

with my different types of Catalogues. The list box has the

multiselect

option enabled. Where would you suggest I go from here?

Kind Regards

Simon

message

Simon,

I would remove the mag type filtering from the query altogether,

and

use

some VB code to "read" the listbox selections and put together a

WHERE

condition string and impose it on the report upon opening it
(this

assumes the report is opened through a button on the same form.

The

code

behind the button would look something like:

For Each itm In Me.lstMTyp_ID
ctn = ctn & "MTyp_ID = '" & Me.lstMTyp_ID.ItemData(itm) & "'
AND

"

Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Note: code above assumes MTyp_ID is type text; if numeric, use

this

second line instead:

ctn = ctn & "MTyp_ID = " & Me.lstMTyp_ID.ItemData(itm) & " AND "

HTH,
Nikos

Simonglencross wrote:

Nikos,

The VBA way sounds like the better choice would you have any

resources

or

information which would help me?I'm reading up on the multi

select

list

boxes at the moment.

Thanks again Nikos!

message

Simon,

The "proper" way to do it is to use a multi-select listbox

Click to expand...

on

a

form,

and some VBA code behind it. The "quick and dirty" way (i.e.
no

coding)

is to add an extra field in tblMagazineTypes (type yes/no),
and

show

that on the form (in a subform?) in either continuous forms or

datasheet

view, so the user can click for including a type; then you
will

need

to

include this field in your query as well, and filter on the
True

values.

HTH,
Nikos

Simonglencross wrote:

Thanks for that Nikos,

Do you know how I can set this up so the user only has to

select

the

magazine types from a form and then clicks a button to print

out

the

labels,

I can't get me head round it at all.

Many Thanks

Simon

message

Simon,

Your data structure seems to be correct; if it's what I
think

it

is

based on your last post, then the query to print your labels

off

of

should look something like:

SELECT tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

FROM (tblMagazineTypes INNER JOIN tblSubscriptions ON

tblMagazineTypes.MTyp_ID = tblSubscriptions.MTyp_ID) INNER

JOIN

tblSubscribers ON tblSubscriptions.Sber_ID =

tblSubscribers.Sber_ID

WHERE (((tblMagazineTypes.MTyp_ID)="mt1" Or
(tblMagazineTypes.MTyp_ID)="mt2" Or

(tblMagazineTypes.MTyp_ID)="mt4"))

GROUP BY tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

(hope my assumptions on field names are clear!)

HTH,
Nikos

Simonglencross wrote:

I shall try and explain this a little better.

I have three tables tblSubscribers, tblSubscriptions and

tblMagazineTypes.

tblSubscribers contains all names and addresses'

tblsubscriptions

holds

all

of the subscriptions information i.e what people have

subscribed

to

and

the

last table tblMagazineTypes contains the 6 different
magazime

types.

I need to be able to set up a way where the user can select

any

magazine

type combination and print off the labels with the

subscribers

names

and

addresses on rememebering that I only need one label per

subscriber

regardless of the magazine type. for example mr smith may

have

subscribed to

magazine 1,2 and 3 and the operator has selected to print

labels

for

magazine 1 and 3 in this instance I would only want 1
address

label

and

not

2.

Hope this helps a little more!

in

message

Can you give us some sample data?

Simonglencross wrote:

I understand this but the database already has a large

amount

of

data

in

it,

to do what you suggest would invole alot of work is there

another

way?

or

can you explain what I would need to do with the current

data?

Your help is much appreciated!

Many Thanks

Simon

in

message

Simon,

This is a classic many-to-many relationship between

subscriber

s

and

magazines. To handle efficiently, you need to modify
your

data

design

so

you have one table for subscribers (like you already
do),

one

for

magazines (tblMagazines with an ID field, name field
plus

whatever

else

required), and a third table called tblSubscriptions
(with

a

subscriber

ID and a magazine ID foreign keys) to represent actual

subcriptions,

breaking the many-to-many relationship down to two

one-to-many

ones.

HTH,
Nikos

Simonglencross wrote:

Sory for reposting but desperate for some help

I have created a database and have two tables one
called

tblSubscribers

and

another called tblSubscriptions. Within these tables I

have

a

number

of

subscribers and 6 different magazine type's setup in
the

subscriptions

table.
I had setup the database to print off labels, these
where

separated

in

to

cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a

Click to expand...

subscriber

to

subscribe

to

any number of catalogues and these catalogues may

Click to expand...

well

be

sent

out

together

for example this week I am sending out cat 1, cat 2 and

cat3

but

next

week

it may be cat 2 and cat 6 but I also need to be careful

as

1

subscriber

might subscribe to more than 1 catalogue and

Click to expand...

therefore

I

would

only

want

one

label with the subscribers name and address and not one

for

each

catalogue

selected.

Any advise would be much appreciated, I hope you can

understand

what

I

am

trying to achieve.

Kind Regards

Simon

Click to expand...

Click to expand...

Nikos Yannacopoulos · Feb 15, 2005

I already said "or something like that... to check balah blah".

Moreover, if you read further down, it becomes apparent you needn't
actually do that at all - and it was the second time I posted that
today. You need to put some more effort in this, rather than expect me
or anybody else to do it A to Z for you.

Nikko,

Just adding in the Group BY and I am getting an error miss operator in Query
expression '*' and the following appears in the immediate window

SELECT 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('1','2','3','4','5','6','7') GROUP BY
tblSubscribers.Title, tblSubscribers.Forename, tblSubscribers.Surname,
tblSubscribers.Company, tblSubscribers.Address, tblSubscribers.City,
tblSubscribers.[Country/Region], tblSubscribers.PostalCode, *

Any Ideas?

Thanks Simon

For single records:

strSQL = "SELECT 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 & ") " & _
"GROUP BY tblSubscribers.Title, tblSubscribers.Forename, " & _
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode, * "
Debug.Print strSQL

or something like that... To check, go back to the query where you
copied the SQL string from, and set the Totals to on (View > Totals",
keeping the default setting Group By. While you are at it, save the
query, you will need it for the report.

Coming to your second question, you don't need to modify the query
everytime! Keep the saved query as above, i.e. with no filter on
cattypes, and base your report on it, so it will include all subscribers
if opened directly by hand. Then with the use of the code, you can apply
the filter on the report at runtime, without changing the query. This is
exactly what my original code sample did. Assuming you have saved the
base query (no filtering) and based your report on it, the code bhind
the button should be:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "Typ_ID = '" & Me.[Catalogue Types].ItemData(itm) & "'
AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line between the
For and Next;
* I have assumed the PK field in table tblSubscriptions to be named
MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the actual
name;

* remember, remove the WHERE clause on mag types from the original query.

This is actually my first post this morning, five postings ago! Do you
see now?

Nikos

Nikos,

I see what I was doing wrong now Sorry Nikos!

Ok that works great the only other two things I need to do now are

If I select a for instance cat 1 and cat 2 from the list box and a
subscriber has subscribed to both cat 1 and cat 2 I only want 1 label
printed out for that Subscriber.

The other thing is I want instead of the query screen displaying the

Click to expand...

Click to expand...

data I

want to be able to print off the labels

Curretly at the end I have

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "allsubscribersnew", _
view:=acViewPreview

This diplays the results to screen.

Many Thanks

Simon

But you are not creating the strSQL string! Change this section to:

strSQL = "SELECT 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 & ");"
Debug.Print strSQL

Do you get a proper strSQL statement in the immediate window now?
What's this * at the end of the select clause? Is the query itself
working properly (the one you copied the SQL statement form)?

Simonglencross wrote:

Nikos

Nothing appears in the immediate window here is the code to show you

where I

have inserted the debug.print strSQL.

SELECT 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 & ");"
Debug.Print strSQL

Many Thanks Simon

Do the debug trick, and post back the SQL expression that you get in

Click to expand...

the

immediate window (and the exact error message and line of code it
occurs on).

Simonglencross wrote:

Nikos,

I have done what you suggested but still no difference and I still

Click to expand...

get

the

same syntax error message, any ideas why?

Simon

You have forgotten to precede the WHERE with a space!
Tip: in your code, insert a

debug.print strSQL

line right after strSQL statement is created, so you can see exactly
what you are doing in the immediate window (Ctrl+G). It would have

made

the problem obvious right away.

Nikos

Simonglencross wrote:

Nikos,

I copied and pasted the following statemant but I am geting a

Click to expand...

syntax

error,

any ideas what I am doing wrong?
strSQL = "SELECT tblsubscriptions.Catcost, 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 &

");"

Kind Regards

Simon

"Nikos Yannacopoulos" <[email protected]> wrote in

Click to expand...

message

Simon,

Let Access do the hard work for you! Make the query that you need

Click to expand...

in

design view, i.e. add the required tables (and joins) and drag the
fields you need down to the grid, omitting the filter on cattypes;

then

revert to SQL view, and the SQL expression is ready for you to

Click to expand...

copy

and

paste in your code, adding just the WHERE clause exactly as you

Click to expand...

are

doing.

HTH,
Nikos

Simonglencross wrote:

Nikos,

You will pleased to hear I am making some progress I have now go

the

multi

select list box to work with the following statement

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("allsubscribersnew")
' Loop through the selected items in the list box and build a

Click to expand...

text

string

For Each varItem In Me!List8.ItemsSelected
strCriteria = strCriteria & ",'" &

Click to expand...

Me!List8.ItemData(varItem)

&

"'"

Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
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 * FROM tblsubscriptions " & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria &

");"

' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "allsubscribersnew"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

This diplays to my screen mailinglistid of each subscriber and

Click to expand...

also

the

cat

type, but I also need it to show me the name and address of each

subscriber

and the catcost, once it has done that I then need it to out put

Click to expand...

to

labels

automaticaly! Have you any ideas I have tried adding this

Click to expand...

statement

in

but

it doesnt work.

strSQL = "SELECT tblSubscribers.Title tblSubscribers.Forename
tblSubscribers.Surname & _
tblSubscribers.Company tblSubscribers.Address
tblSubscribers.City tblSubscribers.[Country/Region] & _
tblSubscribers.PostalCode tblsubscriptions.Catcost *

FROM

tblSubscribers INNER JOIN tblsubscriptions & _
ON tblSubscribers.MailingListID =

tblsubscriptions.MailingListID

" & _
"WHERE tblsubscriptions.cattypes IN(" & strCriteria &

");"

All of your help is much appreciated!

Simon

message

Simon,

Add a command button on the same form to open the report; the

Click to expand...

code

behind the button's click event should be something like:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "MTyp_ID = '" & Me.[Catalogue Types].ItemData(itm)

Click to expand...

&

"'

AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line

Click to expand...

between

the

For and Next;
* I have assumed the PK field in table tblSubscriptions to be

named

MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the

actual

name;

* remember, remove the WHERE clause on mag types from the

Click to expand...

original

query.

HTH,
Nikos

Simonglencross wrote:

Sorry Nikos you loast me a bit here I have now got a form

Click to expand...

called

frmLabelsSelection on this I have an unbound list box called

Catalogue

Types

with my different types of Catalogues. The list box has the

multiselect

option enabled. Where would you suggest I go from here?

Kind Regards

Simon

message

Simon,

I would remove the mag type filtering from the query

Click to expand...

altogether,

and

use

some VB code to "read" the listbox selections and put together

Click to expand...

a

WHERE

condition string and impose it on the report upon opening it

(this

assumes the report is opened through a button on the same

Click to expand...

form.

The

code

behind the button would look something like:

For Each itm In Me.lstMTyp_ID
ctn = ctn & "MTyp_ID = '" & Me.lstMTyp_ID.ItemData(itm) & "'

AND

"

Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Note: code above assumes MTyp_ID is type text; if numeric, use

this

second line instead:

ctn = ctn & "MTyp_ID = " & Me.lstMTyp_ID.ItemData(itm) & " AND

Click to expand...

"

HTH,
Nikos

Simonglencross wrote:

Nikos,

The VBA way sounds like the better choice would you have any

resources

or

information which would help me?I'm reading up on the multi

select

list

boxes at the moment.

Thanks again Nikos!

message

Simon,

The "proper" way to do it is to use a multi-select listbox

Click to expand...

on

a

form,

and some VBA code behind it. The "quick and dirty" way (i.e.

no

coding)

is to add an extra field in tblMagazineTypes (type yes/no),

and

show

that on the form (in a subform?) in either continuous forms

Click to expand...

or

datasheet

view, so the user can click for including a type; then you

will

need

to

include this field in your query as well, and filter on the

True

values.

HTH,
Nikos

Simonglencross wrote:

Thanks for that Nikos,

Do you know how I can set this up so the user only has to

select

the

magazine types from a form and then clicks a button to

Click to expand...

print

out

the

labels,

I can't get me head round it at all.

Many Thanks

Simon

"Nikos Yannacopoulos" <[email protected]> wrote

Click to expand...

in

message

Simon,

Your data structure seems to be correct; if it's what I

think

it

is

based on your last post, then the query to print your

Click to expand...

labels

off

of

should look something like:

SELECT tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

FROM (tblMagazineTypes INNER JOIN tblSubscriptions ON

tblMagazineTypes.MTyp_ID = tblSubscriptions.MTyp_ID) INNER

JOIN

tblSubscribers ON tblSubscriptions.Sber_ID =

tblSubscribers.Sber_ID

WHERE (((tblMagazineTypes.MTyp_ID)="mt1" Or
(tblMagazineTypes.MTyp_ID)="mt2" Or

(tblMagazineTypes.MTyp_ID)="mt4"))

GROUP BY tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

(hope my assumptions on field names are clear!)

HTH,
Nikos

Simonglencross wrote:

I shall try and explain this a little better.

I have three tables tblSubscribers, tblSubscriptions and

tblMagazineTypes.

tblSubscribers contains all names and addresses'

tblsubscriptions

holds

all

of the subscriptions information i.e what people have

subscribed

to

and

the

last table tblMagazineTypes contains the 6 different

magazime

types.

I need to be able to set up a way where the user can

Click to expand...

select

any

magazine

type combination and print off the labels with the

subscribers

names

and

addresses on rememebering that I only need one label per

subscriber

regardless of the magazine type. for example mr smith may

have

subscribed to

magazine 1,2 and 3 and the operator has selected to print

labels

for

magazine 1 and 3 in this instance I would only want 1

address

label

and

not

2.

Hope this helps a little more!

in

message

Can you give us some sample data?

Simonglencross wrote:

I understand this but the database already has a large

amount

of

data

in

it,

to do what you suggest would invole alot of work is

Click to expand...

there

another

way?

or

can you explain what I would need to do with the

Click to expand...

current

data?

Your help is much appreciated!

Many Thanks

Simon

"Nikos Yannacopoulos" <[email protected]>

Click to expand...

wrote

in

message

Simon,

This is a classic many-to-many relationship between

subscriber

s

and

magazines. To handle efficiently, you need to modify

your

data

design

so

you have one table for subscribers (like you already

do),

one

for

magazines (tblMagazines with an ID field, name field

plus

whatever

else

required), and a third table called tblSubscriptions

(with

a

subscriber

ID and a magazine ID foreign keys) to represent actual

subcriptions,

breaking the many-to-many relationship down to two

one-to-many

ones.

HTH,
Nikos

Simonglencross wrote:

Sory for reposting but desperate for some help

I have created a database and have two tables one

called

tblSubscribers

and

another called tblSubscriptions. Within these tables

Click to expand...

I

have

a

number

of

subscribers and 6 different magazine type's setup in

the

subscriptions

table.
I had setup the database to print off labels, these

where

separated

in

to

cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a

Click to expand...

subscriber

to

subscribe

to

any number of catalogues and these catalogues may

Click to expand...

well

be

sent

out

together

for example this week I am sending out cat 1, cat 2

Click to expand...

and

cat3

but

next

week

it may be cat 2 and cat 6 but I also need to be

Click to expand...

careful

as

1

subscriber

might subscribe to more than 1 catalogue and

Click to expand...

therefore

I

would

only

want

one

label with the subscribers name and address and not

Click to expand...

one

for

each

catalogue

selected.

Any advise would be much appreciated, I hope you can

understand

what

I

am

trying to achieve.

Kind Regards

Simon

Click to expand...

Click to expand...

Simonglencross · Feb 15, 2005

Nikos,

I am trying believe it or not maybe I have bitten off more than I can Chew
and I appologise for ansking you so many questions! I have incorperated the
below but get a compile error argument not optional highlighting the ctn =
Left(Len(ctn)) - 5 .

Once again thanks for all of your help, I m not expecting you to do
everything for me although it may seem that way.

Many Thanks

Simon

Nikos Yannacopoulos said:
Simon,

Add a command button on the same form to open the report; the code
behind the button's click event should be something like:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "MTyp_ID = '" & Me.[Catalogue Types].ItemData(itm) & "' AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line between the
For and Next;
* I have assumed the PK field in table tblSubscriptions to be named
MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the actual name;
* remember, remove the WHERE clause on mag types from the original query.

HTH,
Nikos

Sorry Nikos you loast me a bit here I have now got a form called
frmLabelsSelection on this I have an unbound list box called Catalogue Types
with my different types of Catalogues. The list box has the multiselect
option enabled. Where would you suggest I go from here?

Kind Regards

Simon

Click to expand...

Michel Walsh · Feb 15, 2005

Hi,

The function Left requires TWO arguments, you supplied just one; the missing
argument is not optional (one that can be automatically assumed by the
function) and so, YOU have to supply it.

When the error occur, position your cursor on the word Left in the
expression, then, click on F1 to get the help file information related to
the function. Guided by the help file and the error message, you should be
able to figure out by yourself what is the problem, and what is the
solution. In this specific case, the first argument should be a string, the
string you have the intention to take the len(ctn)-5 leftmost
characters:

ctn=left(ctn, len(ctn)-5 )

is, probably, the correction.

Hoping it may help,
Vanderghast, Access MVP

Simonglencross said:
Nikos,

I am trying believe it or not maybe I have bitten off more than I can Chew
and I appologise for ansking you so many questions! I have incorperated
the
below but get a compile error argument not optional highlighting the ctn =
Left(Len(ctn)) - 5 .

Once again thanks for all of your help, I m not expecting you to do
everything for me although it may seem that way.

Many Thanks

Simon

Nikos Yannacopoulos said:

Simon,

Add a command button on the same form to open the report; the code
behind the button's click event should be something like:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "MTyp_ID = '" & Me.[Catalogue Types].ItemData(itm) & "' AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line between the
For and Next;
* I have assumed the PK field in table tblSubscriptions to be named
MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the actual name;
* remember, remove the WHERE clause on mag types from the original query.

HTH,
Nikos

Sorry Nikos you loast me a bit here I have now got a form called
frmLabelsSelection on this I have an unbound list box called Catalogue Types
with my different types of Catalogues. The list box has the multiselect
option enabled. Where would you suggest I go from here?

Kind Regards

Simon

Simon,

I would remove the mag type filtering from the query altogether, and
use
some VB code to "read" the listbox selections and put together a WHERE
condition string and impose it on the report upon opening it (this
assumes the report is opened through a button on the same form. The
code
behind the button would look something like:

For Each itm In Me.lstMTyp_ID
ctn = ctn & "MTyp_ID = '" & Me.lstMTyp_ID.ItemData(itm) & "' AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Note: code above assumes MTyp_ID is type text; if numeric, use this
second line instead:

ctn = ctn & "MTyp_ID = " & Me.lstMTyp_ID.ItemData(itm) & " AND "

HTH,
Nikos

Simonglencross wrote:

Nikos,

The VBA way sounds like the better choice would you have any resources

or

information which would help me?I'm reading up on the multi select
list
boxes at the moment.

Thanks again Nikos!

Simon,

The "proper" way to do it is to use a multi-select listbox on a form,
and some VBA code behind it. The "quick and dirty" way (i.e. no coding)
is to add an extra field in tblMagazineTypes (type yes/no), and show
that on the form (in a subform?) in either continuous forms or datasheet
view, so the user can click for including a type; then you will need to
include this field in your query as well, and filter on the True values.

HTH,
Nikos

Simonglencross wrote:

Thanks for that Nikos,

Do you know how I can set this up so the user only has to select the
magazine types from a form and then clicks a button to print out the

labels,

I can't get me head round it at all.

Many Thanks

Simon

Simon,

Your data structure seems to be correct; if it's what I think it is
based on your last post, then the query to print your labels off of
should look something like:

SELECT tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

FROM (tblMagazineTypes INNER JOIN tblSubscriptions ON

tblMagazineTypes.MTyp_ID = tblSubscriptions.MTyp_ID) INNER JOIN
tblSubscribers ON tblSubscriptions.Sber_ID = tblSubscribers.Sber_ID
WHERE (((tblMagazineTypes.MTyp_ID)="mt1" Or
(tblMagazineTypes.MTyp_ID)="mt2" Or (tblMagazineTypes.MTyp_ID)="mt4"))
GROUP BY tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

(hope my assumptions on field names are clear!)

HTH,
Nikos

Simonglencross wrote:

I shall try and explain this a little better.

I have three tables tblSubscribers, tblSubscriptions and

tblMagazineTypes.

tblSubscribers contains all names and addresses' tblsubscriptions

holds

all

of the subscriptions information i.e what people have subscribed
to

and

the

last table tblMagazineTypes contains the 6 different magazime types.

I need to be able to set up a way where the user can select any

magazine

type combination and print off the labels with the subscribers names

and

addresses on rememebering that I only need one label per
subscriber
regardless of the magazine type. for example mr smith may have

subscribed to

magazine 1,2 and 3 and the operator has selected to print labels for
magazine 1 and 3 in this instance I would only want 1 address
label

and

not

2.

Hope this helps a little more!

Can you give us some sample data?

Simonglencross wrote:

I understand this but the database already has a large amount of

data

in

it,

to do what you suggest would invole alot of work is there
another

way?

or

can you explain what I would need to do with the current data?

Your help is much appreciated!

Many Thanks

Simon

message

Simon,

This is a classic many-to-many relationship between subscribers

and

magazines. To handle efficiently, you need to modify your data

design

so

you have one table for subscribers (like you already do), one for
magazines (tblMagazines with an ID field, name field plus whatever

else

required), and a third table called tblSubscriptions (with a

subscriber

ID and a magazine ID foreign keys) to represent actual

subcriptions,

breaking the many-to-many relationship down to two one-to-many

ones.

HTH,
Nikos

Simonglencross wrote:

Sory for reposting but desperate for some help

I have created a database and have two tables one called

tblSubscribers

and

another called tblSubscriptions. Within these tables I have a

number

of

subscribers and 6 different magazine type's setup in the

subscriptions

table.
I had setup the database to print off labels, these where

separated

in

to

cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a subscriber to

subscribe

to

any number of catalogues and these catalogues may well be sent

out

together

for example this week I am sending out cat 1, cat 2 and cat3 but

next

week

it may be cat 2 and cat 6 but I also need to be careful as 1

subscriber

might subscribe to more than 1 catalogue and therefore I would

only

want

one

label with the subscribers name and address and not one for each

catalogue

selected.

Any advise would be much appreciated, I hope you can
understand

what

I

am

trying to achieve.

Kind Regards

Simon

Click to expand...

Click to expand...

Nikos Yannacopoulos · Feb 16, 2005

Correct... that's what is should have been in the first place. Sorry for
the oversight (always a possibility with untested, sample code).

Thanks Michel!

Nikos

Michel said:
Hi,

The function Left requires TWO arguments, you supplied just one; the missing
argument is not optional (one that can be automatically assumed by the
function) and so, YOU have to supply it.

When the error occur, position your cursor on the word Left in the
expression, then, click on F1 to get the help file information related to
the function. Guided by the help file and the error message, you should be
able to figure out by yourself what is the problem, and what is the
solution. In this specific case, the first argument should be a string, the
string you have the intention to take the len(ctn)-5 leftmost
characters:

ctn=left(ctn, len(ctn)-5 )

is, probably, the correction.

Hoping it may help,
Vanderghast, Access MVP

Nikos,

I am trying believe it or not maybe I have bitten off more than I can Chew
and I appologise for ansking you so many questions! I have incorperated
the
below but get a compile error argument not optional highlighting the ctn =
Left(Len(ctn)) - 5 .

Once again thanks for all of your help, I m not expecting you to do
everything for me although it may seem that way.

Many Thanks

Simon

Simon,

Add a command button on the same form to open the report; the code
behind the button's click event should be something like:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "MTyp_ID = '" & Me.[Catalogue Types].ItemData(itm) & "'

Click to expand...

AND "

Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line between the
For and Next;
* I have assumed the PK field in table tblSubscriptions to be named
MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the actual
name;

* remember, remove the WHERE clause on mag types from the original query.

HTH,
Nikos

Simonglencross wrote:

Sorry Nikos you loast me a bit here I have now got a form called
frmLabelsSelection on this I have an unbound list box called Catalogue
Types

with my different types of Catalogues. The list box has the multiselect
option enabled. Where would you suggest I go from here?

Kind Regards

Simon

Simon,

I would remove the mag type filtering from the query altogether, and
use
some VB code to "read" the listbox selections and put together a WHERE
condition string and impose it on the report upon opening it (this
assumes the report is opened through a button on the same form. The
code
behind the button would look something like:

For Each itm In Me.lstMTyp_ID
ctn = ctn & "MTyp_ID = '" & Me.lstMTyp_ID.ItemData(itm) & "' AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Note: code above assumes MTyp_ID is type text; if numeric, use this
second line instead:

ctn = ctn & "MTyp_ID = " & Me.lstMTyp_ID.ItemData(itm) & " AND "

HTH,
Nikos

Simonglencross wrote:

Nikos,

The VBA way sounds like the better choice would you have any resources

or

information which would help me?I'm reading up on the multi select
list
boxes at the moment.

Thanks again Nikos!

Simon,

The "proper" way to do it is to use a multi-select listbox on a form,
and some VBA code behind it. The "quick and dirty" way (i.e. no
coding)

is to add an extra field in tblMagazineTypes (type yes/no), and show
that on the form (in a subform?) in either continuous forms or
datasheet

view, so the user can click for including a type; then you will need
to

include this field in your query as well, and filter on the True
values.

HTH,
Nikos

Simonglencross wrote:

Thanks for that Nikos,

Do you know how I can set this up so the user only has to select the
magazine types from a form and then clicks a button to print out the

labels,

I can't get me head round it at all.

Many Thanks

Simon

Simon,

Your data structure seems to be correct; if it's what I think it is
based on your last post, then the query to print your labels off of
should look something like:

SELECT tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

FROM (tblMagazineTypes INNER JOIN tblSubscriptions ON

tblMagazineTypes.MTyp_ID = tblSubscriptions.MTyp_ID) INNER JOIN
tblSubscribers ON tblSubscriptions.Sber_ID = tblSubscribers.Sber_ID
WHERE (((tblMagazineTypes.MTyp_ID)="mt1" Or
(tblMagazineTypes.MTyp_ID)="mt2" Or
(tblMagazineTypes.MTyp_ID)="mt4"))

GROUP BY tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

(hope my assumptions on field names are clear!)

HTH,
Nikos

Simonglencross wrote:

I shall try and explain this a little better.

I have three tables tblSubscribers, tblSubscriptions and

tblMagazineTypes.

tblSubscribers contains all names and addresses' tblsubscriptions

holds

all

of the subscriptions information i.e what people have subscribed
to

and

the

last table tblMagazineTypes contains the 6 different magazime
types.

I need to be able to set up a way where the user can select any

magazine

type combination and print off the labels with the subscribers
names

and

addresses on rememebering that I only need one label per
subscriber
regardless of the magazine type. for example mr smith may have

subscribed to

magazine 1,2 and 3 and the operator has selected to print labels
for

magazine 1 and 3 in this instance I would only want 1 address
label

and

not

2.

Hope this helps a little more!

message

Can you give us some sample data?

Simonglencross wrote:

I understand this but the database already has a large amount of

data

in

it,

to do what you suggest would invole alot of work is there
another

way?

or

can you explain what I would need to do with the current data?

Your help is much appreciated!

Many Thanks

Simon

message

Simon,

This is a classic many-to-many relationship between subscribers

and

magazines. To handle efficiently, you need to modify your data

design

so

you have one table for subscribers (like you already do), one
for

magazines (tblMagazines with an ID field, name field plus
whatever

else

required), and a third table called tblSubscriptions (with a

subscriber

ID and a magazine ID foreign keys) to represent actual

subcriptions,

breaking the many-to-many relationship down to two one-to-many

ones.

HTH,
Nikos

Simonglencross wrote:

Sory for reposting but desperate for some help

I have created a database and have two tables one called

tblSubscribers

and

another called tblSubscriptions. Within these tables I have a

number

of

subscribers and 6 different magazine type's setup in the

subscriptions

table.
I had setup the database to print off labels, these where

separated

in

to

cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a subscriber to

subscribe

to

any number of catalogues and these catalogues may well be sent

out

together

for example this week I am sending out cat 1, cat 2 and cat3
but

next

week

it may be cat 2 and cat 6 but I also need to be careful as 1

subscriber

might subscribe to more than 1 catalogue and therefore I would

only

want

one

label with the subscribers name and address and not one for
each

catalogue

selected.

Any advise would be much appreciated, I hope you can
understand

what

I

am

trying to achieve.

Kind Regards

Simon

Click to expand...

Click to expand...

Simonglencross · Feb 16, 2005

Nikos,
Just a little note to say thank you for all of your help yesterday!

Regards

Simon

Nikos Yannacopoulos said:
Correct... that's what is should have been in the first place. Sorry for
the oversight (always a possibility with untested, sample code).

Thanks Michel!

Nikos

Michel said:

Hi,

The function Left requires TWO arguments, you supplied just one; the missing
argument is not optional (one that can be automatically assumed by the
function) and so, YOU have to supply it.

When the error occur, position your cursor on the word Left in the
expression, then, click on F1 to get the help file information related to
the function. Guided by the help file and the error message, you should be
able to figure out by yourself what is the problem, and what is the
solution. In this specific case, the first argument should be a string, the
string you have the intention to take the len(ctn)-5 leftmost
characters:

ctn=left(ctn, len(ctn)-5 )

is, probably, the correction.

Hoping it may help,
Vanderghast, Access MVP

Nikos,

I am trying believe it or not maybe I have bitten off more than I can Chew
and I appologise for ansking you so many questions! I have incorperated
the
below but get a compile error argument not optional highlighting the ctn =
Left(Len(ctn)) - 5 .

Once again thanks for all of your help, I m not expecting you to do
everything for me although it may seem that way.

Many Thanks

Simon

Simon,

Add a command button on the same form to open the report; the code
behind the button's click event should be something like:

For Each itm In Me.[Catalogue Types].ItemsSelected
ctn = ctn & "MTyp_ID = '" & Me.[Catalogue Types].ItemData(itm) & "'

AND "

Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Notes:
* watch out for wrapping in your newsreader, just one line between the
For and Next;
* I have assumed the PK field in table tblSubscriptions to be named
MTyp_ID, change to the actual name;
* I have assumed the report to be named MyReport, change to the actual

name;

* remember, remove the WHERE clause on mag types from the original query.

HTH,
Nikos

Simonglencross wrote:

Sorry Nikos you loast me a bit here I have now got a form called
frmLabelsSelection on this I have an unbound list box called Catalogue

Types

with my different types of Catalogues. The list box has the multiselect
option enabled. Where would you suggest I go from here?

Kind Regards

Simon

Simon,

I would remove the mag type filtering from the query altogether, and
use
some VB code to "read" the listbox selections and put together a WHERE
condition string and impose it on the report upon opening it (this
assumes the report is opened through a button on the same form. The
code
behind the button would look something like:

For Each itm In Me.lstMTyp_ID
ctn = ctn & "MTyp_ID = '" & Me.lstMTyp_ID.ItemData(itm) & "' AND "
Next
If Len(ctn) = 0 Then
msgbox "No item selected!", vbCritical, "Report Error"
Exit Sub
Else
ctn = Left(Len(ctn)) - 5)
DoCmd.OpenReport "MyReport", acViewPreview, , ctn
End If

Note: code above assumes MTyp_ID is type text; if numeric, use this
second line instead:

ctn = ctn & "MTyp_ID = " & Me.lstMTyp_ID.ItemData(itm) & " AND "

HTH,
Nikos

Simonglencross wrote:

Nikos,

The VBA way sounds like the better choice would you have any resources

or

information which would help me?I'm reading up on the multi select
list
boxes at the moment.

Thanks again Nikos!

Simon,

The "proper" way to do it is to use a multi-select listbox on a form,
and some VBA code behind it. The "quick and dirty" way (i.e. no

coding)

is to add an extra field in tblMagazineTypes (type yes/no), and show
that on the form (in a subform?) in either continuous forms or

datasheet

view, so the user can click for including a type; then you will need

to

include this field in your query as well, and filter on the True

values.

HTH,
Nikos

Simonglencross wrote:

Thanks for that Nikos,

Do you know how I can set this up so the user only has to select the
magazine types from a form and then clicks a button to print out the

labels,

I can't get me head round it at all.

Many Thanks

Simon

Simon,

Your data structure seems to be correct; if it's what I think it is
based on your last post, then the query to print your labels off of
should look something like:

SELECT tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

FROM (tblMagazineTypes INNER JOIN tblSubscriptions ON

tblMagazineTypes.MTyp_ID = tblSubscriptions.MTyp_ID) INNER JOIN
tblSubscribers ON tblSubscriptions.Sber_ID = tblSubscribers.Sber_ID
WHERE (((tblMagazineTypes.MTyp_ID)="mt1" Or
(tblMagazineTypes.MTyp_ID)="mt2" Or

(tblMagazineTypes.MTyp_ID)="mt4"))

GROUP BY tblSubscribers.Sber_ID, tblSubscribers.Name,

tblSubscribers.Address

(hope my assumptions on field names are clear!)

HTH,
Nikos

Simonglencross wrote:

I shall try and explain this a little better.

I have three tables tblSubscribers, tblSubscriptions and

tblMagazineTypes.

tblSubscribers contains all names and addresses' tblsubscriptions

holds

all

of the subscriptions information i.e what people have subscribed
to

and

the

last table tblMagazineTypes contains the 6 different magazime

types.

I need to be able to set up a way where the user can select any

magazine

type combination and print off the labels with the subscribers

names

and

addresses on rememebering that I only need one label per
subscriber
regardless of the magazine type. for example mr smith may have

subscribed to

magazine 1,2 and 3 and the operator has selected to print labels

for

magazine 1 and 3 in this instance I would only want 1 address
label

and

not

2.

Hope this helps a little more!

message

Can you give us some sample data?

Simonglencross wrote:

I understand this but the database already has a large amount of

data

in

it,

to do what you suggest would invole alot of work is there
another

way?

or

can you explain what I would need to do with the current data?

Your help is much appreciated!

Many Thanks

Simon

message

Simon,

This is a classic many-to-many relationship between subscribers

and

magazines. To handle efficiently, you need to modify your data

design

so

you have one table for subscribers (like you already do), one

for

magazines (tblMagazines with an ID field, name field plus

whatever

else

required), and a third table called tblSubscriptions (with a

subscriber

ID and a magazine ID foreign keys) to represent actual

subcriptions,

breaking the many-to-many relationship down to two one-to-many

ones.

HTH,
Nikos

Simonglencross wrote:

Sory for reposting but desperate for some help

I have created a database and have two tables one called

tblSubscribers

and

another called tblSubscriptions. Within these tables I have a

number

of

subscribers and 6 different magazine type's setup in the

subscriptions

table.
I had setup the database to print off labels, these where

separated

in

to

cat 1, cat2, cat3, cat 4, cat 5, and
cat 6 for example. It is now possible for a subscriber to

subscribe

to

any number of catalogues and these catalogues may well be sent

out

together

for example this week I am sending out cat 1, cat 2 and cat3

but

next

week

it may be cat 2 and cat 6 but I also need to be careful as 1

subscriber

might subscribe to more than 1 catalogue and therefore I would

only

want

one

label with the subscribers name and address and not one for

each

catalogue

selected.

Any advise would be much appreciated, I hope you can
understand

what

I

am

trying to achieve.

Kind Regards

Simon

Click to expand...

Click to expand...

Formating bolded text exclusively.	0	Sep 30, 2021
Advise	7	Feb 12, 2005
transpose variable array	6	Jul 13, 2009
IIF(AND) statement	3	Apr 7, 2010
Countif, IF condition is met	2	Dec 5, 2012
Index, Match within a range of values	4	May 3, 2010
Need some PWA help	0	Apr 15, 2023
Conditional List fields	2	May 3, 2005

re posted

Nikos Yannacopoulos

Simonglencross

Nikos Yannacopoulos

Simonglencross

Nikos Yannacopoulos

Simonglencross

Michel Walsh

Nikos Yannacopoulos

Simonglencross

Ask a Question

Similar Threads