Solved

Multi choice list box

Posted on 2010-09-22
1
815 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
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

770 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