Mail merge and labels button from multiselect listbox

[louise]

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

[Pieter Linden]

lo***********@h otmail.com (louise) wrote in message news:<39******* *************** ****@posting.go ogle.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.

[MGFoster]

-----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/AwUBP43B7YechKq OuFEgEQK2egCeJg A7KUcOq6xPVXetf bxgBmxfZwUAn3xC
C/tDqesbVRjJiiQOJ rp+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.ItemsSel ected stDocCroteria =
stDocCriteria & "[ID] = "& lstBox.Column (0,VarItm) & "OR"
Next
If stDocCriteria <> " " Then
stDocCriteria = Left(stDocCrite ria, Len(stDocCriter ia) -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.OpenRepor t "RptIndividualC ontacts" acPreview,,GetC riteria()

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

[Albert D. Kallal]

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:

DoCmd.OpenReport "RptIndividualContacts" acPreview,,,GetCriteria()
 
If you download my sample mail merge code. Then the above for doing a mail
merge becomes:
 
dim strSql     as string
 
strSql = "select * from YouQuery where " & GetCriteria()
me.Refresh
MergeAllWord (strSql)
 
The above will start the mail merge process for you. You can find my mail
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