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]

Re: Mail merge and labels button from multiselect listbox

louise_corson@h otmail.com (louise) wrote in message news:<39e8a839. 0310150206.4c7f 79ea@posting.go ogle.com>...[color=blue]
> 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.[/color]

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]

Re: Mail merge and labels button from multiselect listbox

-----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:
[color=blue]
> 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[/color]

[Albert D. Kallal]

Re: Mail merge and labels button from multiselect listbox

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/msaccess.html

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
NoooSPAmkallal@ msn.com

http://www.attcanada.net/~kallal.msn