Selection from multi select list box....

[debraj007]

Hi, and thanks for any help you can offer, I'm stumped. I'm working on a
quotation database. I need the ability to enter a quote and send it to
several people. I have the quote form established.

After entering the quote you click a button and a pop up form comes up. On
the pop up, you choose which customers to send the quote to (multi select
list box) and click another button to preview or print the actual quote.

Here's where I'm stumped. I can't figure out how to customize the quote for
the people I chose from the list box (i.e. I would like their names,
addresses, etc. to be on the printed quote, 1 quote per page please). Any
suggestions from ye old wise ones?

Thanks
Deb

 

[Allen Browne]

One approach would be to create a query that uses both tables (the people
you send to, and the quotes), but without any line joining them in the upper
pane of query design. This gives you every possible combination of the 2.

Create a report based on this query, laid out with the person's names at the
top, and the quote underneath. Use the Sorting And Grouping dialog to create
a group header on each QuoteID, and set the properties of the QuoteID header
so each quote starts on a new page.

Create a form where the user selects the quote number, chooses the people in
the multi-select list box, and clicks a button to print them. The Click
event procedure of the command button uses OpenReport with a WhereCondition
so that it prints the right quote for the right people.

For an example of how to build the WhereCondition string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will also need to add to the end of that string so it prints just the
one quote. Something like this:
strWhere = strWhere & " AND (QuoteID = " & Me.[QuoteID]

 

[debraj007]

Thank you Allen, as usual, the perfect solution . One more question. is
there a way to track which customers I sent the quote to (which people I
selected from the list box) so I can pull reports later on if needed?

Deb

One approach would be to create a query that uses both tables (the people
you send to, and the quotes), but without any line joining them in the upper
pane of query design. This gives you every possible combination of the 2.

Create a report based on this query, laid out with the person's names at the
top, and the quote underneath. Use the Sorting And Grouping dialog to create
a group header on each QuoteID, and set the properties of the QuoteID header
so each quote starts on a new page.

Create a form where the user selects the quote number, chooses the people in
the multi-select list box, and clicks a button to print them. The Click
event procedure of the command button uses OpenReport with a WhereCondition
so that it prints the right quote for the right people.

For an example of how to build the WhereCondition string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will also need to add to the end of that string so it prints just the
one quote. Something like this:
strWhere = strWhere & " AND (QuoteID = " & Me.[QuoteID]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, and thanks for any help you can offer, I'm stumped. I'm working on a
quotation database. I need the ability to enter a quote and send it to
several people. I have the quote form established.

After entering the quote you click a button and a pop up form comes up. On
the pop up, you choose which customers to send the quote to (multi select
list box) and click another button to preview or print the actual quote.

Here's where I'm stumped. I can't figure out how to customize the quote
for
the people I chose from the list box (i.e. I would like their names,
addresses, etc. to be on the printed quote, 1 quote per page please). Any
suggestions from ye old wise ones?

Thanks
Deb

 

[Allen Browne]

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description of mailing

b) MailingDetail table, with fields:
MailingID Number relates to Mailing.MailingID
CustomerID Number relates to Customer.CustomerID

Now, the query you created last time: turn it into an Append query (Append
on Query menu, in query design.) Access asks which table to append to:
answer MailingDetail. Type this into a fresh column in the Field row:
MailingID: 99
and indicate this goes to the MailingID column.
Switch the query to SQL View (View menu.)
There's an example of the SQL statement you need to execute.

Now you will write some code to OpenRecordset on the Mailing Table, AddNew
and Update, and get the new MailingID number. You will then create a SQL
string to Execute, adding the new MailingID number into the string in place
of the 99.

If the Execute idea is new, here's an example:
http://allenbrowne.com/ser-60.html
or here's another that uses AddNew and then Execute:
http://allenbrowne.com/ser-57.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen, as usual, the perfect solution . One more question. is
there a way to track which customers I sent the quote to (which people I
selected from the list box) so I can pull reports later on if needed?

Deb

One approach would be to create a query that uses both tables (the people
you send to, and the quotes), but without any line joining them in the
upper
pane of query design. This gives you every possible combination of the 2.

Create a report based on this query, laid out with the person's names at
the
top, and the quote underneath. Use the Sorting And Grouping dialog to
create
a group header on each QuoteID, and set the properties of the QuoteID
header
so each quote starts on a new page.

Create a form where the user selects the quote number, chooses the people
in
the multi-select list box, and clicks a button to print them. The Click
event procedure of the command button uses OpenReport with a
WhereCondition
so that it prints the right quote for the right people.

For an example of how to build the WhereCondition string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will also need to add to the end of that string so it prints just the
one quote. Something like this:
strWhere = strWhere & " AND (QuoteID = " & Me.[QuoteID]

Hi, and thanks for any help you can offer, I'm stumped. I'm working on
a
quotation database. I need the ability to enter a quote and send it to
several people. I have the quote form established.

After entering the quote you click a button and a pop up form comes up.
On
the pop up, you choose which customers to send the quote to (multi
select
list box) and click another button to preview or print the actual
quote.

Here's where I'm stumped. I can't figure out how to customize the quote
for
the people I chose from the list box (i.e. I would like their names,
addresses, etc. to be on the printed quote, 1 quote per page please).
Any
suggestions from ye old wise ones?

 

[debraj007]

Thanks. I tried the add and execute code (modified of course with my field
names, tables, etc.) and it didn't work. So I came up with a couple of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not a subform) with a
listbox and a command button, so I can't really use me.dirty can I, because
I'm not really making changes and there is no record to copy from this
form.... right?

Deb

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description of mailing

b) MailingDetail table, with fields:
MailingID Number relates to Mailing.MailingID
CustomerID Number relates to Customer.CustomerID

Now, the query you created last time: turn it into an Append query (Append
on Query menu, in query design.) Access asks which table to append to:
answer MailingDetail. Type this into a fresh column in the Field row:
MailingID: 99
and indicate this goes to the MailingID column.
Switch the query to SQL View (View menu.)
There's an example of the SQL statement you need to execute.

Now you will write some code to OpenRecordset on the Mailing Table, AddNew
and Update, and get the new MailingID number. You will then create a SQL
string to Execute, adding the new MailingID number into the string in place
of the 99.

If the Execute idea is new, here's an example:
http://allenbrowne.com/ser-60.html
or here's another that uses AddNew and then Execute:
http://allenbrowne.com/ser-57.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you Allen, as usual, the perfect solution . One more question. is
there a way to track which customers I sent the quote to (which people I
selected from the list box) so I can pull reports later on if needed?

Deb

One approach would be to create a query that uses both tables (the people
you send to, and the quotes), but without any line joining them in the
upper
pane of query design. This gives you every possible combination of the 2.

Create a report based on this query, laid out with the person's names at
the
top, and the quote underneath. Use the Sorting And Grouping dialog to
create
a group header on each QuoteID, and set the properties of the QuoteID
header
so each quote starts on a new page.

Create a form where the user selects the quote number, chooses the people
in
the multi-select list box, and clicks a button to print them. The Click
event procedure of the command button uses OpenReport with a
WhereCondition
so that it prints the right quote for the right people.

For an example of how to build the WhereCondition string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will also need to add to the end of that string so it prints just the
one quote. Something like this:
strWhere = strWhere & " AND (QuoteID = " & Me.[QuoteID]

Hi, and thanks for any help you can offer, I'm stumped. I'm working on
a
quotation database. I need the ability to enter a quote and send it to
several people. I have the quote form established.

After entering the quote you click a button and a pop up form comes up.
On
the pop up, you choose which customers to send the quote to (multi
select
list box) and click another button to preview or print the actual
quote.

Here's where I'm stumped. I can't figure out how to customize the quote
for
the people I chose from the list box (i.e. I would like their names,
addresses, etc. to be on the printed quote, 1 quote per page please).
Any
suggestions from ye old wise ones?

 

[Allen Browne]

That's right: if the form is unbound, you can skip the part that check if it
is dirty, since an unbound form can't be dirty.

You can still use the approach of creating the Mailing record, and then
storing the customers in the mailing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I tried the add and execute code (modified of course with my field
names, tables, etc.) and it didn't work. So I came up with a couple of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not a subform) with
a
listbox and a command button, so I can't really use me.dirty can I,
because
I'm not really making changes and there is no record to copy from this
form.... right?

Deb

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description of mailing

b) MailingDetail table, with fields:
MailingID Number relates to Mailing.MailingID
CustomerID Number relates to Customer.CustomerID

Now, the query you created last time: turn it into an Append query
(Append
on Query menu, in query design.) Access asks which table to append to:
answer MailingDetail. Type this into a fresh column in the Field row:
MailingID: 99
and indicate this goes to the MailingID column.
Switch the query to SQL View (View menu.)
There's an example of the SQL statement you need to execute.

Now you will write some code to OpenRecordset on the Mailing Table,
AddNew
and Update, and get the new MailingID number. You will then create a SQL
string to Execute, adding the new MailingID number into the string in
place
of the 99.

If the Execute idea is new, here's an example:
http://allenbrowne.com/ser-60.html
or here's another that uses AddNew and then Execute:
http://allenbrowne.com/ser-57.html

Thank you Allen, as usual, the perfect solution . One more question.
is
there a way to track which customers I sent the quote to (which people
I
selected from the list box) so I can pull reports later on if needed?

Deb

:

One approach would be to create a query that uses both tables (the
people
you send to, and the quotes), but without any line joining them in the
upper
pane of query design. This gives you every possible combination of the
2.

Create a report based on this query, laid out with the person's names
at
the
top, and the quote underneath. Use the Sorting And Grouping dialog to
create
a group header on each QuoteID, and set the properties of the QuoteID
header
so each quote starts on a new page.

Create a form where the user selects the quote number, chooses the
people
in
the multi-select list box, and clicks a button to print them. The
Click
event procedure of the command button uses OpenReport with a
WhereCondition
so that it prints the right quote for the right people.

For an example of how to build the WhereCondition string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will also need to add to the end of that string so it prints just
the
one quote. Something like this:
strWhere = strWhere & " AND (QuoteID = " & Me.[QuoteID]

Hi, and thanks for any help you can offer, I'm stumped. I'm working
on
a
quotation database. I need the ability to enter a quote and send it
to
several people. I have the quote form established.

After entering the quote you click a button and a pop up form comes
up.
On
the pop up, you choose which customers to send the quote to (multi
select
list box) and click another button to preview or print the actual
quote.

Here's where I'm stumped. I can't figure out how to customize the
quote
for
the people I chose from the list box (i.e. I would like their names,
addresses, etc. to be on the printed quote, 1 quote per page
please).
Any
suggestions from ye old wise ones?

 

[debraj007]

I apologize for taking so long to get back to this Allen, I got pulled into
another project.

So here's my entire code for the on click event of the form and it doesn't
work. Perhaps I misunderstood something in the code as I used pieces and
parts from your example. When I say it doesn't work, nothing happens, no
message, no update, nothing. Any suggestions?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strSql As String
Dim lngID As Long

strDoc = "rptQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

If Me.NewRecord Then
With Me.RecordsetClone
.AddNew
!ProjectID = Me.ProjectID
!MailingDate = Date
.Update

.Bookmark = .LastModified
lngID = !ProjectID
End With

If Me.[frmQuoteBidders].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblMailingDetail] ( MailingID,
ContractorID ) " & _
"SELECT " & lngID & " AS MailingID,
tblContractors.ContractorID " & _
"FROM [tblContractors, tblProjects] INNER JOIN
tblProjectDetails ON tblProjects.ProjectID = " & tblProjectDetails.ProjectID
& ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End Sub

Thanks,
Deb

That's right: if the form is unbound, you can skip the part that check if it
is dirty, since an unbound form can't be dirty.

You can still use the approach of creating the Mailing record, and then
storing the customers in the mailing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I tried the add and execute code (modified of course with my field
names, tables, etc.) and it didn't work. So I came up with a couple of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not a subform) with
a
listbox and a command button, so I can't really use me.dirty can I,
because
I'm not really making changes and there is no record to copy from this
form.... right?

Deb

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description of mailing

b) MailingDetail table, with fields:
MailingID Number relates to Mailing.MailingID
CustomerID Number relates to Customer.CustomerID

Now, the query you created last time: turn it into an Append query
(Append
on Query menu, in query design.) Access asks which table to append to:
answer MailingDetail. Type this into a fresh column in the Field row:
MailingID: 99
and indicate this goes to the MailingID column.
Switch the query to SQL View (View menu.)
There's an example of the SQL statement you need to execute.

Now you will write some code to OpenRecordset on the Mailing Table,
AddNew
and Update, and get the new MailingID number. You will then create a SQL
string to Execute, adding the new MailingID number into the string in
place
of the 99.

If the Execute idea is new, here's an example:
http://allenbrowne.com/ser-60.html
or here's another that uses AddNew and then Execute:
http://allenbrowne.com/ser-57.html

Thank you Allen, as usual, the perfect solution . One more question.
is
there a way to track which customers I sent the quote to (which people
I
selected from the list box) so I can pull reports later on if needed?

Deb

:

One approach would be to create a query that uses both tables (the
people
you send to, and the quotes), but without any line joining them in the
upper
pane of query design. This gives you every possible combination of the
2.

Create a report based on this query, laid out with the person's names
at
the
top, and the quote underneath. Use the Sorting And Grouping dialog to
create
a group header on each QuoteID, and set the properties of the QuoteID
header
so each quote starts on a new page.

Create a form where the user selects the quote number, chooses the
people
in
the multi-select list box, and clicks a button to print them. The
Click
event procedure of the command button uses OpenReport with a
WhereCondition
so that it prints the right quote for the right people.

For an example of how to build the WhereCondition string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will also need to add to the end of that string so it prints just
the
one quote. Something like this:
strWhere = strWhere & " AND (QuoteID = " & Me.[QuoteID]

Hi, and thanks for any help you can offer, I'm stumped. I'm working
on
a
quotation database. I need the ability to enter a quote and send it
to
several people. I have the quote form established.

After entering the quote you click a button and a pop up form comes
up.
On
the pop up, you choose which customers to send the quote to (multi
select
list box) and click another button to preview or print the actual
quote.

Here's where I'm stumped. I can't figure out how to customize the
quote
for
the people I chose from the list box (i.e. I would like their names,
addresses, etc. to be on the printed quote, 1 quote per page
please).
Any
suggestions from ye old wise ones?

 

[Allen Browne]

You have an End If at the very end.
It needs to be further up, so delete that line.

Immediately below the DoCmd.OpenReport line, add:
Else
MsgBox "No project number to add."
End If

Until you get this working, you may also want to comment out the error
handler on line 2 by adding a single quote:
'On Error Goto ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I apologize for taking so long to get back to this Allen, I got pulled into
another project.

So here's my entire code for the on click event of the form and it doesn't
work. Perhaps I misunderstood something in the code as I used pieces and
parts from your example. When I say it doesn't work, nothing happens, no
message, no update, nothing. Any suggestions?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strSql As String
Dim lngID As Long

strDoc = "rptQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

If Me.NewRecord Then
With Me.RecordsetClone
.AddNew
!ProjectID = Me.ProjectID
!MailingDate = Date
.Update

.Bookmark = .LastModified
lngID = !ProjectID
End With

If Me.[frmQuoteBidders].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tblMailingDetail] ( MailingID,
ContractorID ) " & _
"SELECT " & lngID & " AS MailingID,
tblContractors.ContractorID " & _
"FROM [tblContractors, tblProjects] INNER JOIN
tblProjectDetails ON tblProjects.ProjectID = " &
tblProjectDetails.ProjectID
& ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End Sub

Thanks,
Deb

That's right: if the form is unbound, you can skip the part that check if
it
is dirty, since an unbound form can't be dirty.

You can still use the approach of creating the Mailing record, and then
storing the customers in the mailing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I tried the add and execute code (modified of course with my
field
names, tables, etc.) and it didn't work. So I came up with a couple of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not a subform)
with
a
listbox and a command button, so I can't really use me.dirty can I,
because
I'm not really making changes and there is no record to copy from this
form.... right?

Deb




:

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description of
mailing

b) MailingDetail table, with fields:
MailingID Number relates to Mailing.MailingID
CustomerID Number relates to Customer.CustomerID

Now, the query you created last time: turn it into an Append query
(Append
on Query menu, in query design.) Access asks which table to append to:
answer MailingDetail. Type this into a fresh column in the Field row:
MailingID: 99
and indicate this goes to the MailingID column.
Switch the query to SQL View (View menu.)
There's an example of the SQL statement you need to execute.

Now you will write some code to OpenRecordset on the Mailing Table,
AddNew
and Update, and get the new MailingID number. You will then create a
SQL
string to Execute, adding the new MailingID number into the string in
place
of the 99.

If the Execute idea is new, here's an example:
http://allenbrowne.com/ser-60.html
or here's another that uses AddNew and then Execute:
http://allenbrowne.com/ser-57.html

Thank you Allen, as usual, the perfect solution . One more
question.
is
there a way to track which customers I sent the quote to (which
people
I
selected from the list box) so I can pull reports later on if
needed?

Deb

:

One approach would be to create a query that uses both tables (the
people
you send to, and the quotes), but without any line joining them in
the
upper
pane of query design. This gives you every possible combination of
the
2.

Create a report based on this query, laid out with the person's
names
at
the
top, and the quote underneath. Use the Sorting And Grouping dialog
to
create
a group header on each QuoteID, and set the properties of the
QuoteID
header
so each quote starts on a new page.

Create a form where the user selects the quote number, chooses the
people
in
the multi-select list box, and clicks a button to print them. The
Click
event procedure of the command button uses OpenReport with a
WhereCondition
so that it prints the right quote for the right people.

For an example of how to build the WhereCondition string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will also need to add to the end of that string so it prints
just
the
one quote. Something like this:
strWhere = strWhere & " AND (QuoteID = " & Me.[QuoteID]

Hi, and thanks for any help you can offer, I'm stumped. I'm
working
on
a
quotation database. I need the ability to enter a quote and send
it
to
several people. I have the quote form established.

After entering the quote you click a button and a pop up form
comes
up.
On
the pop up, you choose which customers to send the quote to
(multi
select
list box) and click another button to preview or print the actual
quote.

Here's where I'm stumped. I can't figure out how to customize the
quote
for
the people I chose from the list box (i.e. I would like their
names,
addresses, etc. to be on the printed quote, 1 quote per page
please).
Any
suggestions from ye old wise ones?

 

[debraj007]

That did get me farther, now I get the message box, but it still isn't
appending. Could it have anything to do with the fact that I'm getting the
project# from the previous form (=Forms!frmAddQuote!ProjectID as control
source)?

Thanks,
Deb

You have an End If at the very end.
It needs to be further up, so delete that line.

Immediately below the DoCmd.OpenReport line, add:
Else
MsgBox "No project number to add."
End If

Until you get this working, you may also want to comment out the error
handler on line 2 by adding a single quote:
'On Error Goto ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I apologize for taking so long to get back to this Allen, I got pulled into
another project.

So here's my entire code for the on click event of the form and it doesn't
work. Perhaps I misunderstood something in the code as I used pieces and
parts from your example. When I say it doesn't work, nothing happens, no
message, no update, nothing. Any suggestions?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strSql As String
Dim lngID As Long

strDoc = "rptQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

If Me.NewRecord Then
With Me.RecordsetClone
.AddNew
!ProjectID = Me.ProjectID
!MailingDate = Date
.Update

.Bookmark = .LastModified
lngID = !ProjectID
End With

If Me.[frmQuoteBidders].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tblMailingDetail] ( MailingID,
ContractorID ) " & _
"SELECT " & lngID & " AS MailingID,
tblContractors.ContractorID " & _
"FROM [tblContractors, tblProjects] INNER JOIN
tblProjectDetails ON tblProjects.ProjectID = " &
tblProjectDetails.ProjectID
& ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End Sub

Thanks,
Deb

That's right: if the form is unbound, you can skip the part that check if
it
is dirty, since an unbound form can't be dirty.

You can still use the approach of creating the Mailing record, and then
storing the customers in the mailing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I tried the add and execute code (modified of course with my
field
names, tables, etc.) and it didn't work. So I came up with a couple of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not a subform)
with
a
listbox and a command button, so I can't really use me.dirty can I,
because
I'm not really making changes and there is no record to copy from this
form.... right?

Deb




:

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description of
mailing

b) MailingDetail table, with fields:
MailingID Number relates to Mailing.MailingID
CustomerID Number relates to Customer.CustomerID

Now, the query you created last time: turn it into an Append query
(Append
on Query menu, in query design.) Access asks which table to append to:
answer MailingDetail. Type this into a fresh column in the Field row:
MailingID: 99
and indicate this goes to the MailingID column.
Switch the query to SQL View (View menu.)
There's an example of the SQL statement you need to execute.

Now you will write some code to OpenRecordset on the Mailing Table,
AddNew
and Update, and get the new MailingID number. You will then create a
SQL
string to Execute, adding the new MailingID number into the string in
place
of the 99.

If the Execute idea is new, here's an example:
http://allenbrowne.com/ser-60.html
or here's another that uses AddNew and then Execute:
http://allenbrowne.com/ser-57.html

Thank you Allen, as usual, the perfect solution . One more
question.
is
there a way to track which customers I sent the quote to (which
people
I
selected from the list box) so I can pull reports later on if
needed?

Deb

:

One approach would be to create a query that uses both tables (the
people
you send to, and the quotes), but without any line joining them in
the
upper
pane of query design. This gives you every possible combination of
the
2.

Create a report based on this query, laid out with the person's
names
at
the
top, and the quote underneath. Use the Sorting And Grouping dialog
to
create
a group header on each QuoteID, and set the properties of the
QuoteID
header
so each quote starts on a new page.

Create a form where the user selects the quote number, chooses the
people
in
the multi-select list box, and clicks a button to print them. The
Click
event procedure of the command button uses OpenReport with a
WhereCondition
so that it prints the right quote for the right people.

For an example of how to build the WhereCondition string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will also need to add to the end of that string so it prints
just
the
one quote. Something like this:
strWhere = strWhere & " AND (QuoteID = " & Me.[QuoteID]

Hi, and thanks for any help you can offer, I'm stumped. I'm
working
on
a
quotation database. I need the ability to enter a quote and send
it
to
several people. I have the quote form established.

After entering the quote you click a button and a pop up form
comes
up.
On
the pop up, you choose which customers to send the quote to
(multi
select
list box) and click another button to preview or print the actual
quote.

Here's where I'm stumped. I can't figure out how to customize the
quote
for
the people I chose from the list box (i.e. I would like their
names,
addresses, etc. to be on the printed quote, 1 quote per page
please).
Any
suggestions from ye old wise ones?

 

[Allen Browne]

Your If block is testing if you are at a new record.
If you have brought the ProjectID over, perhaps you have not saved the
record yet, so you might add:
Me.Dirty = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That did get me farther, now I get the message box, but it still isn't
appending. Could it have anything to do with the fact that I'm getting the
project# from the previous form (=Forms!frmAddQuote!ProjectID as control
source)?

Thanks,
Deb

You have an End If at the very end.
It needs to be further up, so delete that line.

Immediately below the DoCmd.OpenReport line, add:
Else
MsgBox "No project number to add."
End If

Until you get this working, you may also want to comment out the error
handler on line 2 by adding a single quote:
'On Error Goto ...

I apologize for taking so long to get back to this Allen, I got pulled
into
another project.

So here's my entire code for the on click event of the form and it
doesn't
work. Perhaps I misunderstood something in the code as I used pieces
and
parts from your example. When I say it doesn't work, nothing happens,
no
message, no update, nothing. Any suggestions?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strSql As String
Dim lngID As Long

strDoc = "rptQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) &
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

If Me.NewRecord Then
With Me.RecordsetClone
.AddNew
!ProjectID = Me.ProjectID
!MailingDate = Date
.Update

.Bookmark = .LastModified
lngID = !ProjectID
End With

If Me.[frmQuoteBidders].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tblMailingDetail] ( MailingID,
ContractorID ) " & _
"SELECT " & lngID & " AS MailingID,
tblContractors.ContractorID " & _
"FROM [tblContractors, tblProjects] INNER JOIN
tblProjectDetails ON tblProjects.ProjectID = " &
tblProjectDetails.ProjectID
& ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End Sub

Thanks,
Deb

:

That's right: if the form is unbound, you can skip the part that check
if
it
is dirty, since an unbound form can't be dirty.

You can still use the approach of creating the Mailing record, and
then
storing the customers in the mailing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I tried the add and execute code (modified of course with my
field
names, tables, etc.) and it didn't work. So I came up with a couple
of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not a
subform)
with
a
listbox and a command button, so I can't really use me.dirty can I,
because
I'm not really making changes and there is no record to copy from
this
form.... right?

Deb




:

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description of
mailing

b) MailingDetail table, with fields:
MailingID Number relates to Mailing.MailingID
CustomerID Number relates to Customer.CustomerID

Now, the query you created last time: turn it into an Append query
(Append
on Query menu, in query design.) Access asks which table to append
to:
answer MailingDetail. Type this into a fresh column in the Field
row:
MailingID: 99
and indicate this goes to the MailingID column.
Switch the query to SQL View (View menu.)
There's an example of the SQL statement you need to execute.

Now you will write some code to OpenRecordset on the Mailing Table,
AddNew
and Update, and get the new MailingID number. You will then create
a
SQL
string to Execute, adding the new MailingID number into the string
in
place
of the 99.

If the Execute idea is new, here's an example:
http://allenbrowne.com/ser-60.html
or here's another that uses AddNew and then Execute:
http://allenbrowne.com/ser-57.html

Thank you Allen, as usual, the perfect solution . One more
question.
is
there a way to track which customers I sent the quote to (which
people
I
selected from the list box) so I can pull reports later on if
needed?

Deb

:

One approach would be to create a query that uses both tables
(the
people
you send to, and the quotes), but without any line joining them
in
the
upper
pane of query design. This gives you every possible combination
of
the
2.

Create a report based on this query, laid out with the person's
names
at
the
top, and the quote underneath. Use the Sorting And Grouping
dialog
to
create
a group header on each QuoteID, and set the properties of the
QuoteID
header
so each quote starts on a new page.

Create a form where the user selects the quote number, chooses
the
people
in
the multi-select list box, and clicks a button to print them.
The
Click
event procedure of the command button uses OpenReport with a
WhereCondition
so that it prints the right quote for the right people.

For an example of how to build the WhereCondition string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will also need to add to the end of that string so it prints
just
the
one quote. Something like this:
strWhere = strWhere & " AND (QuoteID = " & Me.[QuoteID]

message
Hi, and thanks for any help you can offer, I'm stumped. I'm
working
on
a
quotation database. I need the ability to enter a quote and
send
it
to
several people. I have the quote form established.

After entering the quote you click a button and a pop up form
comes
up.
On
the pop up, you choose which customers to send the quote to
(multi
select
list box) and click another button to preview or print the
actual
quote.

Here's where I'm stumped. I can't figure out how to customize
the
quote
for
the people I chose from the list box (i.e. I would like their
names,
addresses, etc. to be on the printed quote, 1 quote per page
please).
Any
suggestions from ye old wise ones?

 

[debraj007]

I removed the block testing for a new record and added the dirty statement,
but now getting a message that I improperly referenced dirty. Is that because
I'm referencing an unbound form? Do I need to add a recordsource to the form?

Thanks,
Deb

Your If block is testing if you are at a new record.
If you have brought the ProjectID over, perhaps you have not saved the
record yet, so you might add:
Me.Dirty = False

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

That did get me farther, now I get the message box, but it still isn't
appending. Could it have anything to do with the fact that I'm getting the
project# from the previous form (=Forms!frmAddQuote!ProjectID as control
source)?

Thanks,
Deb

You have an End If at the very end.
It needs to be further up, so delete that line.

Immediately below the DoCmd.OpenReport line, add:
Else
MsgBox "No project number to add."
End If

Until you get this working, you may also want to comment out the error
handler on line 2 by adding a single quote:
'On Error Goto ...

I apologize for taking so long to get back to this Allen, I got pulled
into
another project.

So here's my entire code for the on click event of the form and it
doesn't
work. Perhaps I misunderstood something in the code as I used pieces
and
parts from your example. When I say it doesn't work, nothing happens,
no
message, no update, nothing. Any suggestions?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strSql As String
Dim lngID As Long

strDoc = "rptQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) &
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

If Me.NewRecord Then
With Me.RecordsetClone
.AddNew
!ProjectID = Me.ProjectID
!MailingDate = Date
.Update

.Bookmark = .LastModified
lngID = !ProjectID
End With

If Me.[frmQuoteBidders].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tblMailingDetail] ( MailingID,
ContractorID ) " & _
"SELECT " & lngID & " AS MailingID,
tblContractors.ContractorID " & _
"FROM [tblContractors, tblProjects] INNER JOIN
tblProjectDetails ON tblProjects.ProjectID = " &
tblProjectDetails.ProjectID
& ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End Sub

Thanks,
Deb

:

That's right: if the form is unbound, you can skip the part that check
if
it
is dirty, since an unbound form can't be dirty.

You can still use the approach of creating the Mailing record, and
then
storing the customers in the mailing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I tried the add and execute code (modified of course with my
field
names, tables, etc.) and it didn't work. So I came up with a couple
of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not a
subform)
with
a
listbox and a command button, so I can't really use me.dirty can I,
because
I'm not really making changes and there is no record to copy from
this
form.... right?

Deb




:

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description of
mailing

b) MailingDetail table, with fields:
MailingID Number relates to Mailing.MailingID
CustomerID Number relates to Customer.CustomerID

Now, the query you created last time: turn it into an Append query
(Append
on Query menu, in query design.) Access asks which table to append
to:
answer MailingDetail. Type this into a fresh column in the Field
row:
MailingID: 99
and indicate this goes to the MailingID column.
Switch the query to SQL View (View menu.)
There's an example of the SQL statement you need to execute.

Now you will write some code to OpenRecordset on the Mailing Table,
AddNew
and Update, and get the new MailingID number. You will then create
a
SQL
string to Execute, adding the new MailingID number into the string
in
place
of the 99.

If the Execute idea is new, here's an example:
http://allenbrowne.com/ser-60.html
or here's another that uses AddNew and then Execute:
http://allenbrowne.com/ser-57.html

Thank you Allen, as usual, the perfect solution . One more
question.
is
there a way to track which customers I sent the quote to (which
people
I
selected from the list box) so I can pull reports later on if
needed?

Deb

:

One approach would be to create a query that uses both tables
(the
people
you send to, and the quotes), but without any line joining them
in
the
upper
pane of query design. This gives you every possible combination
of
the
2.

Create a report based on this query, laid out with the person's
names
at
the
top, and the quote underneath. Use the Sorting And Grouping
dialog
to
create
a group header on each QuoteID, and set the properties of the
QuoteID
header
so each quote starts on a new page.

Create a form where the user selects the quote number, chooses
the
people
in
the multi-select list box, and clicks a button to print them.
The
Click
event procedure of the command button uses OpenReport with a
WhereCondition
so that it prints the right quote for the right people.

For an example of how to build the WhereCondition string, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will also need to add to the end of that string so it prints
just
the
one quote. Something like this:
strWhere = strWhere & " AND (QuoteID = " & Me.[QuoteID]

message
Hi, and thanks for any help you can offer, I'm stumped. I'm
working
on
a
quotation database. I need the ability to enter a quote and
send
it
to
several people. I have the quote form established.

After entering the quote you click a button and a pop up form
comes
up.
On
the pop up, you choose which customers to send the quote to
(multi
select
list box) and click another button to preview or print the
actual
quote.

Here's where I'm stumped. I can't figure out how to customize
the
quote
for
the people I chose from the list box (i.e. I would like their

 

[Allen Browne]

Yes: If the form is unbound, it cannot be Dirty.

It also cannot have a NewRecord.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I removed the block testing for a new record and added the dirty statement,
but now getting a message that I improperly referenced dirty. Is that
because
I'm referencing an unbound form? Do I need to add a recordsource to the
form?

Thanks,
Deb

Your If block is testing if you are at a new record.
If you have brought the ProjectID over, perhaps you have not saved the
record yet, so you might add:
Me.Dirty = False

That did get me farther, now I get the message box, but it still isn't
appending. Could it have anything to do with the fact that I'm getting
the
project# from the previous form (=Forms!frmAddQuote!ProjectID as
control
source)?

Thanks,
Deb

:

You have an End If at the very end.
It needs to be further up, so delete that line.

Immediately below the DoCmd.OpenReport line, add:
Else
MsgBox "No project number to add."
End If

Until you get this working, you may also want to comment out the error
handler on line 2 by adding a single quote:
'On Error Goto ...

I apologize for taking so long to get back to this Allen, I got
pulled
into
another project.

So here's my entire code for the on click event of the form and it
doesn't
work. Perhaps I misunderstood something in the code as I used pieces
and
parts from your example. When I say it doesn't work, nothing
happens,
no
message, no update, nothing. Any suggestions?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strSql As String
Dim lngID As Long

strDoc = "rptQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
&
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

If Me.NewRecord Then
With Me.RecordsetClone
.AddNew
!ProjectID = Me.ProjectID
!MailingDate = Date
.Update

.Bookmark = .LastModified
lngID = !ProjectID
End With

If Me.[frmQuoteBidders].Form.RecordsetClone.RecordCount >
0
Then
strSql = "INSERT INTO [tblMailingDetail] ( MailingID,
ContractorID ) " & _
"SELECT " & lngID & " AS MailingID,
tblContractors.ContractorID " & _
"FROM [tblContractors, tblProjects] INNER JOIN
tblProjectDetails ON tblProjects.ProjectID = " &
tblProjectDetails.ProjectID
& ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End Sub

Thanks,
Deb

:

That's right: if the form is unbound, you can skip the part that
check
if
it
is dirty, since an unbound form can't be dirty.

You can still use the approach of creating the Mailing record, and
then
storing the customers in the mailing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I tried the add and execute code (modified of course with
my
field
names, tables, etc.) and it didn't work. So I came up with a
couple
of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not a
subform)
with
a
listbox and a command button, so I can't really use me.dirty can
I,
because
I'm not really making changes and there is no record to copy from
this
form.... right?

Deb




:

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description of
mailing

b) MailingDetail table, with fields:
MailingID Number relates to
Mailing.MailingID
CustomerID Number relates to Customer.CustomerID

Now, the query you created last time: turn it into an Append
query
(Append
on Query menu, in query design.) Access asks which table to
append
to:
answer MailingDetail. Type this into a fresh column in the Field
row:
MailingID: 99
and indicate this goes to the MailingID column.
Switch the query to SQL View (View menu.)
There's an example of the SQL statement you need to execute.

Now you will write some code to OpenRecordset on the Mailing
Table,
AddNew
and Update, and get the new MailingID number. You will then
create
a
SQL
string to Execute, adding the new MailingID number into the
string
in
place
of the 99.

If the Execute idea is new, here's an example:
http://allenbrowne.com/ser-60.html
or here's another that uses AddNew and then Execute:
http://allenbrowne.com/ser-57.html

message
Thank you Allen, as usual, the perfect solution . One more
question.
is
there a way to track which customers I sent the quote to
(which
people
I
selected from the list box) so I can pull reports later on if
needed?

Deb

:

One approach would be to create a query that uses both tables
(the
people
you send to, and the quotes), but without any line joining
them
in
the
upper
pane of query design. This gives you every possible
combination
of
the
2.

Create a report based on this query, laid out with the
person's
names
at
the
top, and the quote underneath. Use the Sorting And Grouping
dialog
to
create
a group header on each QuoteID, and set the properties of the
QuoteID
header
so each quote starts on a new page.

Create a form where the user selects the quote number,
chooses
the
people
in
the multi-select list box, and clicks a button to print them.
The
Click
event procedure of the command button uses OpenReport with a
WhereCondition
so that it prints the right quote for the right people.

For an example of how to build the WhereCondition string,
see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will also need to add to the end of that string so it
prints
just
the
one quote. Something like this:
strWhere = strWhere & " AND (QuoteID = " & Me.[QuoteID]

message
Hi, and thanks for any help you can offer, I'm stumped. I'm
working
on
a
quotation database. I need the ability to enter a quote and
send
it
to
several people. I have the quote form established.

After entering the quote you click a button and a pop up
form
comes
up.
On
the pop up, you choose which customers to send the quote to
(multi
select
list box) and click another button to preview or print the
actual
quote.

Here's where I'm stumped. I can't figure out how to
customize
the
quote
for
the people I chose from the list box (i.e. I would like
their

 

[debraj007]

Okay, I give up... I'm gonna need a little more guidance if you don't mind.
I've researched, written and rewritten code (and copied some too), and I'm
still confused. The add new, update and execute is new to me so I'm just not
getting something here.

On my form you enter quote information, then click a button that says Send.
When you click Send, a pop up form comes up a listbox that contains the
customers you can choose to send the quote to (multiselect, simple). That
form has a button that says Preview which brings up my report.

You showed me how to get the report to customize to each customer, but I'm
still having issues trying to figure out:

1. When and how to get the customers appended to the Mailing table
2. How to get the Mailing Id and when and how to append to the
MailingDetails table.

Should I use on update of the listbox? The Preview button for the report?

Sorry if I sound dense, this one is just not coming to me very easily for
some reason.

Thanks
Deb

Yes: If the form is unbound, it cannot be Dirty.

It also cannot have a NewRecord.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I removed the block testing for a new record and added the dirty statement,
but now getting a message that I improperly referenced dirty. Is that
because
I'm referencing an unbound form? Do I need to add a recordsource to the
form?

Thanks,
Deb

Your If block is testing if you are at a new record.
If you have brought the ProjectID over, perhaps you have not saved the
record yet, so you might add:
Me.Dirty = False

That did get me farther, now I get the message box, but it still isn't
appending. Could it have anything to do with the fact that I'm getting
the
project# from the previous form (=Forms!frmAddQuote!ProjectID as
control
source)?

Thanks,
Deb

:

You have an End If at the very end.
It needs to be further up, so delete that line.

Immediately below the DoCmd.OpenReport line, add:
Else
MsgBox "No project number to add."
End If

Until you get this working, you may also want to comment out the error
handler on line 2 by adding a single quote:
'On Error Goto ...

I apologize for taking so long to get back to this Allen, I got
pulled
into
another project.

So here's my entire code for the on click event of the form and it
doesn't
work. Perhaps I misunderstood something in the code as I used pieces
and
parts from your example. When I say it doesn't work, nothing
happens,
no
message, no update, nothing. Any suggestions?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strSql As String
Dim lngID As Long

strDoc = "rptQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem)
&
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen) &
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

If Me.NewRecord Then
With Me.RecordsetClone
.AddNew
!ProjectID = Me.ProjectID
!MailingDate = Date
.Update

.Bookmark = .LastModified
lngID = !ProjectID
End With

If Me.[frmQuoteBidders].Form.RecordsetClone.RecordCount >
0
Then
strSql = "INSERT INTO [tblMailingDetail] ( MailingID,
ContractorID ) " & _
"SELECT " & lngID & " AS MailingID,
tblContractors.ContractorID " & _
"FROM [tblContractors, tblProjects] INNER JOIN
tblProjectDetails ON tblProjects.ProjectID = " &
tblProjectDetails.ProjectID
& ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End Sub

Thanks,
Deb

:

That's right: if the form is unbound, you can skip the part that
check
if
it
is dirty, since an unbound form can't be dirty.

You can still use the approach of creating the Mailing record, and
then
storing the customers in the mailing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I tried the add and execute code (modified of course with
my
field
names, tables, etc.) and it didn't work. So I came up with a
couple
of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not a
subform)
with
a
listbox and a command button, so I can't really use me.dirty can
I,
because
I'm not really making changes and there is no record to copy from
this
form.... right?

Deb




:

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description of
mailing

b) MailingDetail table, with fields:
MailingID Number relates to
Mailing.MailingID
CustomerID Number relates to Customer.CustomerID

Now, the query you created last time: turn it into an Append
query
(Append
on Query menu, in query design.) Access asks which table to
append
to:
answer MailingDetail. Type this into a fresh column in the Field
row:
MailingID: 99
and indicate this goes to the MailingID column.
Switch the query to SQL View (View menu.)
There's an example of the SQL statement you need to execute.

Now you will write some code to OpenRecordset on the Mailing
Table,
AddNew
and Update, and get the new MailingID number. You will then
create
a
SQL
string to Execute, adding the new MailingID number into the
string
in
place
of the 99.

If the Execute idea is new, here's an example:
http://allenbrowne.com/ser-60.html
or here's another that uses AddNew and then Execute:
http://allenbrowne.com/ser-57.html

message
Thank you Allen, as usual, the perfect solution . One more
question.
is
there a way to track which customers I sent the quote to
(which
people
I
selected from the list box) so I can pull reports later on if
needed?

Deb

:

One approach would be to create a query that uses both tables
(the
people
you send to, and the quotes), but without any line joining
them
in
the
upper
pane of query design. This gives you every possible
combination
of
the
2.

Create a report based on this query, laid out with the
person's
names
at
the
top, and the quote underneath. Use the Sorting And Grouping
dialog
to
create
a group header on each QuoteID, and set the properties of the
QuoteID
header
so each quote starts on a new page.

Create a form where the user selects the quote number,
chooses
the
people
in
the multi-select list box, and clicks a button to print them.
The
Click
event procedure of the command button uses OpenReport with a
WhereCondition
so that it prints the right quote for the right people.

 

[Allen Browne]

The process is to add the customers to the mailing *first*.
Once that's done, you can use a report or export or whatever, based on a
query that just picks everyone in the mailing.

You will have 2 tables:
- The Mail table ends up with one record for each mailing.
- The MailDetail ends up with one record for each customer in each mailing.

You could create a main form, bound to the Mail table, with a subform where
you select the customers for the mailing. You can add/delete customers in
the subform to add them to the mailing, just like any other subform.

If you want to program a multi-select list box to add multiple customers at
once, that will be more involved code. You will need to learn how to loop
through the ItemsSelected collection of the list box, generating a WHERE
string to use in the Append query statement you will execute. If you can't
get that going, you can still add people to the mailing as described above.

Ultimately, this Mail form will have a command button to export the mailing
in some way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Okay, I give up... I'm gonna need a little more guidance if you don't
mind.
I've researched, written and rewritten code (and copied some too), and I'm
still confused. The add new, update and execute is new to me so I'm just
not
getting something here.

On my form you enter quote information, then click a button that says
Send.
When you click Send, a pop up form comes up a listbox that contains the
customers you can choose to send the quote to (multiselect, simple). That
form has a button that says Preview which brings up my report.

You showed me how to get the report to customize to each customer, but I'm
still having issues trying to figure out:

1. When and how to get the customers appended to the Mailing table
2. How to get the Mailing Id and when and how to append to the
MailingDetails table.

Should I use on update of the listbox? The Preview button for the report?

Sorry if I sound dense, this one is just not coming to me very easily for
some reason.

Thanks
Deb

Yes: If the form is unbound, it cannot be Dirty.

It also cannot have a NewRecord.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I removed the block testing for a new record and added the dirty
statement,
but now getting a message that I improperly referenced dirty. Is that
because
I'm referencing an unbound form? Do I need to add a recordsource to the
form?

Thanks,
Deb

:

Your If block is testing if you are at a new record.
If you have brought the ProjectID over, perhaps you have not saved the
record yet, so you might add:
Me.Dirty = False

That did get me farther, now I get the message box, but it still
isn't
appending. Could it have anything to do with the fact that I'm
getting
the
project# from the previous form (=Forms!frmAddQuote!ProjectID as
control
source)?

Thanks,
Deb

:

You have an End If at the very end.
It needs to be further up, so delete that line.

Immediately below the DoCmd.OpenReport line, add:
Else
MsgBox "No project number to add."
End If

Until you get this working, you may also want to comment out the
error
handler on line 2 by adding a single quote:
'On Error Goto ...

I apologize for taking so long to get back to this Allen, I got
pulled
into
another project.

So here's my entire code for the on click event of the form and
it
doesn't
work. Perhaps I misunderstood something in the code as I used
pieces
and
parts from your example. When I say it doesn't work, nothing
happens,
no
message, no update, nothing. Any suggestions?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strSql As String
Dim lngID As Long

strDoc = "rptQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim &
.ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1,
varItem)
&
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen)
&
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

If Me.NewRecord Then
With Me.RecordsetClone
.AddNew
!ProjectID = Me.ProjectID
!MailingDate = Date
.Update

.Bookmark = .LastModified
lngID = !ProjectID
End With

If
Me.[frmQuoteBidders].Form.RecordsetClone.RecordCount >
0
Then
strSql = "INSERT INTO [tblMailingDetail] (
MailingID,
ContractorID ) " & _
"SELECT " & lngID & " AS MailingID,
tblContractors.ContractorID " & _
"FROM [tblContractors, tblProjects] INNER JOIN
tblProjectDetails ON tblProjects.ProjectID = " &
tblProjectDetails.ProjectID
& ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

DoCmd.OpenReport strDoc, acViewPreview,
WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End Sub

Thanks,
Deb

:

That's right: if the form is unbound, you can skip the part that
check
if
it
is dirty, since an unbound form can't be dirty.

You can still use the approach of creating the Mailing record,
and
then
storing the customers in the mailing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Thanks. I tried the add and execute code (modified of course
with
my
field
names, tables, etc.) and it didn't work. So I came up with a
couple
of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not a
subform)
with
a
listbox and a command button, so I can't really use me.dirty
can
I,
because
I'm not really making changes and there is no record to copy
from
this
form.... right?

Deb




:

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description
of
mailing

b) MailingDetail table, with fields:
MailingID Number relates to
Mailing.MailingID
CustomerID Number relates to
Customer.CustomerID

Now, the query you created last time: turn it into an Append
query
(Append
on Query menu, in query design.) Access asks which table to
append
to:
answer MailingDetail. Type this into a fresh column in the
Field
row:
MailingID: 99
and indicate this goes to the MailingID column.
Switch the query to SQL View (View menu.)
There's an example of the SQL statement you need to execute.

Now you will write some code to OpenRecordset on the Mailing
Table,
AddNew
and Update, and get the new MailingID number. You will then
create
a
SQL
string to Execute, adding the new MailingID number into the
string
in
place
of the 99.

If the Execute idea is new, here's an example:
http://allenbrowne.com/ser-60.html
or here's another that uses AddNew and then Execute:
http://allenbrowne.com/ser-57.html

message
Thank you Allen, as usual, the perfect solution . One
more
question.
is
there a way to track which customers I sent the quote to
(which
people
I
selected from the list box) so I can pull reports later on
if
needed?

Deb

:

One approach would be to create a query that uses both
tables
(the
people
you send to, and the quotes), but without any line joining
them
in
the
upper
pane of query design. This gives you every possible
combination
of
the
2.

Create a report based on this query, laid out with the
person's
names
at
the
top, and the quote underneath. Use the Sorting And
Grouping
dialog
to
create
a group header on each QuoteID, and set the properties of
the
QuoteID
header
so each quote starts on a new page.

Create a form where the user selects the quote number,
chooses
the
people
in
the multi-select list box, and clicks a button to print
them.
The
Click
event procedure of the command button uses OpenReport with
a
WhereCondition
so that it prints the right quote for the right people.

 

[debraj007]

Alan, Alan, Alan, guess what? I got it to work! I create a record in the
mailing table, then create a record in the mailing details table perfectly.
But of course, there is one small problem. Instead of inserting the record I
chose from my listbox, it inserts all records in the mailing details table
(but to the right record!).

I'm excited I got this far, can you help me figure out why it's doing this?
I'm assuming it has to do with the looping through the next record. Here's
the code.... brilliant suggestions? Thanks so far, I'm excited that I've got
it working this much

Deb

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim StrSQL As String


strDoc = "rptConstructionQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "

End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
StrSQL = "INSERT INTO [tblMailingDetail] ( MailingID, ContractorID
) " & _
"SELECT MailingID, ContractorID " & _
"FROM [tblMailing], [tblContractors] WHERE
tblMailing.ProjectID = " & Me.ProjectID & ";"
DBEngine(0)(0).Execute StrSQL, dbFailOnError
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

The process is to add the customers to the mailing *first*.
Once that's done, you can use a report or export or whatever, based on a
query that just picks everyone in the mailing.

You will have 2 tables:
- The Mail table ends up with one record for each mailing.
- The MailDetail ends up with one record for each customer in each mailing.

You could create a main form, bound to the Mail table, with a subform where
you select the customers for the mailing. You can add/delete customers in
the subform to add them to the mailing, just like any other subform.

If you want to program a multi-select list box to add multiple customers at
once, that will be more involved code. You will need to learn how to loop
through the ItemsSelected collection of the list box, generating a WHERE
string to use in the Append query statement you will execute. If you can't
get that going, you can still add people to the mailing as described above.

Ultimately, this Mail form will have a command button to export the mailing
in some way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Okay, I give up... I'm gonna need a little more guidance if you don't
mind.
I've researched, written and rewritten code (and copied some too), and I'm
still confused. The add new, update and execute is new to me so I'm just
not
getting something here.

On my form you enter quote information, then click a button that says
Send.
When you click Send, a pop up form comes up a listbox that contains the
customers you can choose to send the quote to (multiselect, simple). That
form has a button that says Preview which brings up my report.

You showed me how to get the report to customize to each customer, but I'm
still having issues trying to figure out:

1. When and how to get the customers appended to the Mailing table
2. How to get the Mailing Id and when and how to append to the
MailingDetails table.

Should I use on update of the listbox? The Preview button for the report?

Sorry if I sound dense, this one is just not coming to me very easily for
some reason.

Thanks
Deb

Yes: If the form is unbound, it cannot be Dirty.

It also cannot have a NewRecord.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I removed the block testing for a new record and added the dirty
statement,
but now getting a message that I improperly referenced dirty. Is that
because
I'm referencing an unbound form? Do I need to add a recordsource to the
form?

Thanks,
Deb

:

Your If block is testing if you are at a new record.
If you have brought the ProjectID over, perhaps you have not saved the
record yet, so you might add:
Me.Dirty = False

That did get me farther, now I get the message box, but it still
isn't
appending. Could it have anything to do with the fact that I'm
getting
the
project# from the previous form (=Forms!frmAddQuote!ProjectID as
control
source)?

Thanks,
Deb

:

You have an End If at the very end.
It needs to be further up, so delete that line.

Immediately below the DoCmd.OpenReport line, add:
Else
MsgBox "No project number to add."
End If

Until you get this working, you may also want to comment out the
error
handler on line 2 by adding a single quote:
'On Error Goto ...

I apologize for taking so long to get back to this Allen, I got
pulled
into
another project.

So here's my entire code for the on click event of the form and
it
doesn't
work. Perhaps I misunderstood something in the code as I used
pieces
and
parts from your example. When I say it doesn't work, nothing
happens,
no
message, no update, nothing. Any suggestions?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strSql As String
Dim lngID As Long

strDoc = "rptQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim &
.ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1,
varItem)
&
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen)
&
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

If Me.NewRecord Then
With Me.RecordsetClone
.AddNew
!ProjectID = Me.ProjectID
!MailingDate = Date
.Update

.Bookmark = .LastModified
lngID = !ProjectID
End With

If
Me.[frmQuoteBidders].Form.RecordsetClone.RecordCount >
0
Then
strSql = "INSERT INTO [tblMailingDetail] (
MailingID,
ContractorID ) " & _
"SELECT " & lngID & " AS MailingID,
tblContractors.ContractorID " & _
"FROM [tblContractors, tblProjects] INNER JOIN
tblProjectDetails ON tblProjects.ProjectID = " &
tblProjectDetails.ProjectID
& ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

DoCmd.OpenReport strDoc, acViewPreview,
WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End Sub

Thanks,
Deb

:

That's right: if the form is unbound, you can skip the part that
check
if
it
is dirty, since an unbound form can't be dirty.

You can still use the approach of creating the Mailing record,
and
then
storing the customers in the mailing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Thanks. I tried the add and execute code (modified of course
with
my
field
names, tables, etc.) and it didn't work. So I came up with a
couple
of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not a
subform)
with
a
listbox and a command button, so I can't really use me.dirty
can
I,
because
I'm not really making changes and there is no record to copy
from
this
form.... right?

Deb




:

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to Quote.QuoteID
Descrip Text optional description
of
mailing

b) MailingDetail table, with fields:
MailingID Number relates to
Mailing.MailingID
CustomerID Number relates to
Customer.CustomerID

Now, the query you created last time: turn it into an Append
query
(Append
on Query menu, in query design.) Access asks which table to
append
to:
answer MailingDetail. Type this into a fresh column in the
Field
row:
MailingID: 99

 

[Allen Browne]

Great. Good progress.

I'm not exactly clear on this, but it seems you are using a multi-select
list box to choose the contractors. You built up the WHERE string, but then
didn't use it in your INSERT query statement.

The statement should probably be something like this:
strSQL = "INSERT INTO tblMailingDetail ( MailingID, ContractorID ) " & _
"SELECT " & 999 & " AS MailingID, ContractorID " & _
"FROM tblContractors WHERE " & strWhere & ";"

This assumes you have already added the new MailingID record, and got its
number. Substitute that number instead of 999.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Alan, Alan, Alan, guess what? I got it to work! I create a record in the
mailing table, then create a record in the mailing details table
perfectly.
But of course, there is one small problem. Instead of inserting the record
I
chose from my listbox, it inserts all records in the mailing details table
(but to the right record!).

I'm excited I got this far, can you help me figure out why it's doing
this?
I'm assuming it has to do with the looping through the next record. Here's
the code.... brilliant suggestions? Thanks so far, I'm excited that I've
got
it working this much

Deb

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim StrSQL As String


strDoc = "rptConstructionQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"

End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
StrSQL = "INSERT INTO [tblMailingDetail] ( MailingID, ContractorID
) " & _
"SELECT MailingID, ContractorID " & _
"FROM [tblMailing], [tblContractors] WHERE
tblMailing.ProjectID = " & Me.ProjectID & ";"
DBEngine(0)(0).Execute StrSQL, dbFailOnError
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

The process is to add the customers to the mailing *first*.
Once that's done, you can use a report or export or whatever, based on a
query that just picks everyone in the mailing.

You will have 2 tables:
- The Mail table ends up with one record for each mailing.
- The MailDetail ends up with one record for each customer in each
mailing.

You could create a main form, bound to the Mail table, with a subform
where
you select the customers for the mailing. You can add/delete customers in
the subform to add them to the mailing, just like any other subform.

If you want to program a multi-select list box to add multiple customers
at
once, that will be more involved code. You will need to learn how to loop
through the ItemsSelected collection of the list box, generating a WHERE
string to use in the Append query statement you will execute. If you
can't
get that going, you can still add people to the mailing as described
above.

Ultimately, this Mail form will have a command button to export the
mailing
in some way.

Okay, I give up... I'm gonna need a little more guidance if you don't
mind.
I've researched, written and rewritten code (and copied some too), and
I'm
still confused. The add new, update and execute is new to me so I'm
just
not
getting something here.

On my form you enter quote information, then click a button that says
Send.
When you click Send, a pop up form comes up a listbox that contains the
customers you can choose to send the quote to (multiselect, simple).
That
form has a button that says Preview which brings up my report.

You showed me how to get the report to customize to each customer, but
I'm
still having issues trying to figure out:

1. When and how to get the customers appended to the Mailing table
2. How to get the Mailing Id and when and how to append to the
MailingDetails table.

Should I use on update of the listbox? The Preview button for the
report?

Sorry if I sound dense, this one is just not coming to me very easily
for
some reason.

Thanks
Deb

:

Yes: If the form is unbound, it cannot be Dirty.

It also cannot have a NewRecord.

I removed the block testing for a new record and added the dirty
statement,
but now getting a message that I improperly referenced dirty. Is
that
because
I'm referencing an unbound form? Do I need to add a recordsource to
the
form?

Thanks,
Deb

:

Your If block is testing if you are at a new record.
If you have brought the ProjectID over, perhaps you have not saved
the
record yet, so you might add:
Me.Dirty = False

That did get me farther, now I get the message box, but it still
isn't
appending. Could it have anything to do with the fact that I'm
getting
the
project# from the previous form (=Forms!frmAddQuote!ProjectID as
control
source)?

Thanks,
Deb

:

You have an End If at the very end.
It needs to be further up, so delete that line.

Immediately below the DoCmd.OpenReport line, add:
Else
MsgBox "No project number to add."
End If

Until you get this working, you may also want to comment out the
error
handler on line 2 by adding a single quote:
'On Error Goto ...

message
I apologize for taking so long to get back to this Allen, I got
pulled
into
another project.

So here's my entire code for the on click event of the form
and
it
doesn't
work. Perhaps I misunderstood something in the code as I used
pieces
and
parts from your example. When I say it doesn't work, nothing
happens,
no
message, no update, nothing. Any suggestions?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strSql As String
Dim lngID As Long

strDoc = "rptQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim &
.ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1,
varItem)
&
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere,
lngLen)
&
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip,
lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

If Me.NewRecord Then
With Me.RecordsetClone
.AddNew
!ProjectID = Me.ProjectID
!MailingDate = Date
.Update

.Bookmark = .LastModified
lngID = !ProjectID
End With

If
Me.[frmQuoteBidders].Form.RecordsetClone.RecordCount >
0
Then
strSql = "INSERT INTO [tblMailingDetail] (
MailingID,
ContractorID ) " & _
"SELECT " & lngID & " AS MailingID,
tblContractors.ContractorID " & _
"FROM [tblContractors, tblProjects] INNER
JOIN
tblProjectDetails ON tblProjects.ProjectID = " &
tblProjectDetails.ProjectID
& ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If

DoCmd.OpenReport strDoc, acViewPreview,
WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description,
,
"cmdPreview_Click"
End If
Resume Exit_Handler
End If
End Sub

Thanks,
Deb

:

That's right: if the form is unbound, you can skip the part
that
check
if
it
is dirty, since an unbound form can't be dirty.

You can still use the approach of creating the Mailing
record,
and
then
storing the customers in the mailing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Thanks. I tried the add and execute code (modified of
course
with
my
field
names, tables, etc.) and it didn't work. So I came up with
a
couple
of
different reasons that may have happened:

1. The ever present, maybe I screwed something up
2. The form I'm using to select customers is a pop up (not
a
subform)
with
a
listbox and a command button, so I can't really use
me.dirty
can
I,
because
I'm not really making changes and there is no record to
copy
from
this
form.... right?

Deb




:

To do that you need to store that info.

2 tables:
a) Mailing table, with fields:
MailingID AutoNumber primary key
MailingDate Date/Time when you sent this
QuoteID Number relates to
Quote.QuoteID
Descrip Text optional
description
of
mailing

b) MailingDetail table, with fields:
MailingID Number relates to
Mailing.MailingID
CustomerID Number relates to
Customer.CustomerID

Now, the query you created last time: turn it into an
Append
query
(Append
on Query menu, in query design.) Access asks which table
to
append
to:
answer MailingDetail. Type this into a fresh column in the
Field
row:
MailingID: 99

 

[debraj007]

OH MY GOSH, OH MY GOSH, OH MY GOSH! ALLEN, ALLEN, ALLEN, IT WORKS! AND IT
WORKS PERFECTLY!!!!!!!!!!!!!! Sorry for the caps, I'm so excited I can't
stand it! (Now my husband thinks I've lost it, finally). I think I will
celebrate. THANK YOU SO MUCH for the very patient help, could not have
accomplished this without you. Thank you, thank you, a thousand times thank
you .

Deb

Great. Good progress.

I'm not exactly clear on this, but it seems you are using a multi-select
list box to choose the contractors. You built up the WHERE string, but then
didn't use it in your INSERT query statement.

The statement should probably be something like this:
strSQL = "INSERT INTO tblMailingDetail ( MailingID, ContractorID ) " & _
"SELECT " & 999 & " AS MailingID, ContractorID " & _
"FROM tblContractors WHERE " & strWhere & ";"

This assumes you have already added the new MailingID record, and got its
number. Substitute that number instead of 999.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Alan, Alan, Alan, guess what? I got it to work! I create a record in the
mailing table, then create a record in the mailing details table
perfectly.
But of course, there is one small problem. Instead of inserting the record
I
chose from my listbox, it inserts all records in the mailing details table
(but to the right record!).

I'm excited I got this far, can you help me figure out why it's doing
this?
I'm assuming it has to do with the looping through the next record. Here's
the code.... brilliant suggestions? Thanks so far, I'm excited that I've
got
it working this much

Deb

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim StrSQL As String


strDoc = "rptConstructionQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"

End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
StrSQL = "INSERT INTO [tblMailingDetail] ( MailingID, ContractorID
) " & _
"SELECT MailingID, ContractorID " & _
"FROM [tblMailing], [tblContractors] WHERE
tblMailing.ProjectID = " & Me.ProjectID & ";"
DBEngine(0)(0).Execute StrSQL, dbFailOnError
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip, lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

The process is to add the customers to the mailing *first*.
Once that's done, you can use a report or export or whatever, based on a
query that just picks everyone in the mailing.

You will have 2 tables:
- The Mail table ends up with one record for each mailing.
- The MailDetail ends up with one record for each customer in each
mailing.

You could create a main form, bound to the Mail table, with a subform
where
you select the customers for the mailing. You can add/delete customers in
the subform to add them to the mailing, just like any other subform.

If you want to program a multi-select list box to add multiple customers
at
once, that will be more involved code. You will need to learn how to loop
through the ItemsSelected collection of the list box, generating a WHERE
string to use in the Append query statement you will execute. If you
can't
get that going, you can still add people to the mailing as described
above.

Ultimately, this Mail form will have a command button to export the
mailing
in some way.

Okay, I give up... I'm gonna need a little more guidance if you don't
mind.
I've researched, written and rewritten code (and copied some too), and
I'm
still confused. The add new, update and execute is new to me so I'm
just
not
getting something here.

On my form you enter quote information, then click a button that says
Send.
When you click Send, a pop up form comes up a listbox that contains the
customers you can choose to send the quote to (multiselect, simple).
That
form has a button that says Preview which brings up my report.

You showed me how to get the report to customize to each customer, but
I'm
still having issues trying to figure out:

1. When and how to get the customers appended to the Mailing table
2. How to get the Mailing Id and when and how to append to the
MailingDetails table.

Should I use on update of the listbox? The Preview button for the
report?

Sorry if I sound dense, this one is just not coming to me very easily
for
some reason.

Thanks
Deb

:

Yes: If the form is unbound, it cannot be Dirty.

It also cannot have a NewRecord.

I removed the block testing for a new record and added the dirty
statement,
but now getting a message that I improperly referenced dirty. Is
that
because
I'm referencing an unbound form? Do I need to add a recordsource to
the
form?

Thanks,
Deb

:

Your If block is testing if you are at a new record.
If you have brought the ProjectID over, perhaps you have not saved
the
record yet, so you might add:
Me.Dirty = False

That did get me farther, now I get the message box, but it still
isn't
appending. Could it have anything to do with the fact that I'm
getting
the
project# from the previous form (=Forms!frmAddQuote!ProjectID as
control
source)?

Thanks,
Deb

:

You have an End If at the very end.
It needs to be further up, so delete that line.

Immediately below the DoCmd.OpenReport line, add:
Else
MsgBox "No project number to add."
End If

Until you get this working, you may also want to comment out the
error
handler on line 2 by adding a single quote:
'On Error Goto ...

message
I apologize for taking so long to get back to this Allen, I got
pulled
into
another project.

So here's my entire code for the on click event of the form
and
it
doesn't
work. Perhaps I misunderstood something in the code as I used
pieces
and
parts from your example. When I say it doesn't work, nothing
happens,
no
message, no update, nothing. Any suggestions?

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String
Dim strSql As String
Dim lngID As Long

strDoc = "rptQuote"

With Me.lstBidders
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere = strWhere & strDelim &
.ItemData(varItem) &
strDelim & ","
strDescrip = strDescrip & """" & .Column(1,
varItem)
&
""",
"
End If
Next
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[ContractorID] IN (" & Left$(strWhere,
lngLen)
&
")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Bidders: " & Left$(strDescrip,
lngLen)
End If
End If

If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

If Me.NewRecord Then
With Me.RecordsetClone
.AddNew
!ProjectID = Me.ProjectID
!MailingDate = Date
.Update

.Bookmark = .LastModified
lngID = !ProjectID
End With

If
Me.[frmQuoteBidders].Form.RecordsetClone.RecordCount >
0
Then
strSql = "INSERT INTO [tblMailingDetail] (