Mail merge and labels button from multiselect listbox

  • Thread starter Thread starter louise
  • Start date Start date
L

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
 
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.
Click to expand...

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.
 
-----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/AwUBP43B7YechKqOuFEgEQK2egCeJgA7KUcOq6xPVXetfbxgBmxfZwUAn3xC
C/tDqesbVRjJiiQOJrp+JzfF
=lsLA
-----END PGP SIGNATURE-----
 
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
 
Back
Top