Multi choice list box

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.

ianLMurdochAsked:
Who is Participating?
 
Armen Stein - Microsoft Access MVP since 2006Connect With a Mentor PresidentCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.