Solved

Multi choice list box

Posted on 2010-09-22
1
813 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
1 Comment
 
LVL 9

Accepted Solution

by:
Armen Stein - Microsoft Access MVP since 2006 earned 250 total points
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now