Multi choice list box
Posted on 2010-09-22
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) & """, "
' 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)
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.