?
Solved

Multi choice list box

Posted on 2010-09-22
1
Medium Priority
?
819 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 1000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

765 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