Solved

Multi choice list box

Posted on 2010-09-22
1
818 Views
Last Modified: 2012-05-10
My goal is to create an dialog form that will be used to define the criteria to to create a mail merge for all contacts matching the selection criteria.

The data required for this is stored in two tables CUSTOMERS and CONTACTSNAMES
I need to extract the contacts based on JobTitle and display for the merge the
CustomerName, ContactName, Address, Postcode, Town, and Country

My plan is to offer up a form containing an UNBOUND text box (with multiselect set to Simple)
and a button to execute the search

The unbound text box has row source
SELECT DISTINCT ContactNames.jobtitle FROM ContactNames UNION SELECT " All" FROM contactnames;  - (The All is added to allow the option of mail merging ALL contacts)

On the RUN button I tried various code but I seem to fail on opening the report with the denerated docmd.openrrport command where I am trying to pass the selected list items.

After declares I have this code

'    Loop through the ItemsSelected in the list box.
  With Me.lsttitle               ' the name of the unbound text box
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter
                strWhere = .ItemData(varItem)                
                strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
                'Build up the description from the text in the visible column.
                strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
            End If
        Next
    End With
   '
    ' Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1    ' remove comma
    If lngLen > 0 Then
        strWhere = "[jobtitle] IN (" & Left$(strWhere, lngLen) & ")"
        lngLen = Len(strDescrip) - 2
        If lngLen > 0 Then
            strDescrip = "jobtitles: " & Left$(strDescrip, lngLen)
        End If
    End If

DoCmd.OpenReport "report", acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip


Now this does not work and I don't get my data presented on the report.  ANy ideas what I am doing wrong ?

Or is there a better way to do this with an automatic selection and merging process.

0
Comment
Question by:ianLMurdoch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 9

Accepted Solution

by:
Armen Stein - Microsoft Access MVP since 2006 earned 250 total points
ID: 33740387
Overall your approach seems good.  This is a good time to break the problem into pieces.

Place a breakpoint before your OpenReport.  Type ?strWhere and hit Enter.  Examine your Where clause to see if it makes sense.  Open the query behind the report in SQL view and paste the Where clause onto it, then run it.  Do you get the records you expect?

If you don't, then you need to fix your Where clause.  If you do, then something is wrong in the report.

Cheers,
Armen
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question