By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Mail merge and labels button from multiselect listbox

P: n/a
hi

i am trying to set up a mail merge button which takes records from a
multi-select listbox (the contents of which are decided by a query
created by a search from) and not from a specific query.

I have a function GetCriteria()

Expand|Select|Wrap|Line Numbers
  1. Private Function GetCriteria() As String
  2. Dim stDocCriteria As String
  3. Dim VarItm As Variant
  4. For Each VarItm in lstBox.ItemsSelected stDocCroteria =
  5. stDocCriteria & "[ID] = "& lstBox.Column (0,VarItm) & "OR"
  6. Next
  7. If stDocCriteria <> " " Then
  8. stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) -4)
  9. stDocCriteria = "True"
  10. End If
  11. GetCriteria = stDocCriteria
  12. End Function
  13.  
  14. I also have a button to open a report and another to open a form,
  15. these use
  16.  
  17. DoCmd.OpenReport "RptIndividualContacts" acPreview,,GetCriteria()
This works fine but i want to be able to use mail merge in the same
way. Also i want to create mailing labels but a button to the
mailinglabel report needs to be linked to a query.

Can anyone help?

thanx

lou
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
lo***********@hotmail.com (louise) wrote in message news:<39**************************@posting.google. com>...
hi

i am trying to set up a mail merge button which takes records from a
multi-select listbox (the contents of which are decided by a query
created by a search from) and not from a specific query.


If you're just modifying the WHERE statement of the query, don't
change anything. Just build a valid filter/Where clause at runtime,
then open the report and pass the filter. Then just print your report
and you're done.
Nov 12 '05 #2

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

All reports require a RecordSource of a query or a table, therefore
you can't use the values of a ListBox as the RecordSource of a report.

You could create a query that fulfills the requirements of the report
then, using VBA, change the report's Filter & FilterOn properties in
the report's OnOpen event to show only those items selected in the
ListBox.

Order of events
1. Open report
2. Get selected items from the ListBox
3. Set the report's Filter to the items from the ListBox
4. Set the report's FilterOn = True
5. Continue opening the report

Example of Filter (VBA):

Me.Filter = "ID In (1,2,3,4,5)"
Me.FilterOn = True

Instead of using "ID=1 OR ID=2 ..." it is easier to use the In clause.

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP43B7YechKqOuFEgEQK2egCeJgA7KUcOq6xPVXetfbxgBm xfZwUAn3xC
C/tDqesbVRjJiiQOJrp+JzfF
=lsLA
-----END PGP SIGNATURE-----
louise wrote:
hi

i am trying to set up a mail merge button which takes records from a
multi-select listbox (the contents of which are decided by a query
created by a search from) and not from a specific query.

I have a function GetCriteria()

Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm in lstBox.ItemsSelected stDocCroteria =
stDocCriteria & "[ID] = "& lstBox.Column (0,VarItm) & "OR"
Next
If stDocCriteria <> " " Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) -4)
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function

I also have a button to open a report and another to open a form,
these use

DoCmd.OpenReport "RptIndividualContacts" acPreview,,GetCriteria()

This works fine but i want to be able to use mail merge in the same
way. Also i want to create mailing labels but a button to the
mailinglabel report needs to be linked to a query.

Can anyone help?

thanx

lou


Nov 12 '05 #3

P: n/a
Two things:

You open form is using the filter, and it really should use the "where"
clause. (they are often interchange able...but I would use the Where clause.
So, add one more ",".
you get:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "RptIndividualContacts" acPreview,,,GetCriteria()
  2.  
  3. If you download my sample mail merge code. Then the above for doing a mail
  4. merge becomes:
  5.  
  6. dim strSql     as string
  7.  
  8. strSql = "select * from YouQuery where " & GetCriteria()
  9. me.Refresh
  10. MergeAllWord (strSql)
  11.  
  12. The above will start the mail merge process for you. You can find my mail
  13. merge code at:
http://www.attcanada.net/~kallal.msn.../msaccess.html


--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
No************@msn.com
http://www.attcanada.net/~kallal.msn
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.