Multiple selection list box

[Thompyt]

At the moment I am using an unbound combobox to make a selection



Row Source: SELECT DISTINCT Field.[Field Name], Field.Field_ID FROM Field ORDER BY Field.Field_ID;

This then uses the below code to filter the fields, which sets it up for a text email.

[Private Sub Send_AORB_OOB_Click()
On Error GoTo Error
Dim strSubject, strBody, strAddresses As String
    DoCmd.RunCommand acCmdSaveRecord
If IsNull(CR_Numbers) Then
    MsgBox "There are no OOB CRs to send"
    DoCmd.Close acForm, "Email_AORB"
    Exit Sub
Else
With Me
If Not IsNull(.cbxNumber) And Not IsNull(.Priority) And Not IsNull(.Hours) Then
    strBody = "Action Officers," & vbCrLf & "This is a follow-on from today's AORB/ERB discussion on CR " _
        & !CR_Numbers & ".  If needed, please back-brief your O6 for SA, and let me know if there are any issues or concerns. " _
        & "The Change Request priority is " & !Priority & " with " & !Hr & " hours until CR is automatically approved.  " _
        & "Please provide your votes NLT " & !DTG & ". Please call if you have any questions or issues." & vbCrLf & vbCrLf & vbCrLf
    strBody = strBody & "Date Issue Identified: " & Chr(9) & Chr(9) & Chr(9) & !Dates & vbCrLf & vbCrLf
    strBody = strBody & "Priority: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) & !Priority & vbCrLf
    strBody = strBody & "CR Number: " & Chr(9) & Chr(9) & Chr(9) & Chr(9) & !CR_Numbers & vbCrLf
    strBody = strBody & "Change Requested: " & Chr(9) & ![Change Requested] & vbCrLf & vbCrLf
    strBody = strBody & "Unit & Section: " & Chr(9) & Chr(9) & Chr(9) & !UNITS & vbCrLf
    strBody = strBody & "MTOE Para & Bumper Number: " & Chr(9) & ![MTOE Paras] & vbCrLf & vbCrLf
    strBody = strBody & "Rationale: " & Chr(9) & Chr(9) & !Rationale & vbCrLf & vbCrLf
    strBody = strBody & "Notes: " & Chr(9) & Chr(9) & Chr(9) & !NOTES & vbCrLf
    strBody = strBody & "Action Items: " & Chr(9) & Chr(9) & !Action_Items & vbCrLf & vbCrLf & vbCrLf & vbCrLf
    strBody = strBody & "V/R" & vbCrLf & vbCrLf
    strBody = strBody & "Configuration Management" & vbCrLf
    strBody = strBody & "Brigade Modernization Command (BMC)" & vbCrLf
    strBody = strBody & "Network Integration Division (NID)" & vbCrLf
    strBody = strBody & "BLDG 2, Sheridan Road" & vbCrLf
    strBody = strBody & "Ft Bliss, TX 79916" & vbCrLf
    strBody = strBody & "Person" & Chr(9) & "(915)569-5167" & Chr(9) & "Email Addy" & vbCrLf
    strSubject = !Priority & " OOB AORB Change Request Number " & !CR_Numbers & " - " & ![Change Requested]
    DoCmd.Close acForm, "Email_AORB"
    DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddresses, , , strSubject, strBody, True
End If
End With
Exit Sub
Error_Handler_Exit:
Exit Sub
Error:
Select Case Err.Number
    Case 2501
    Err.Clear
Resume Error_Handler_Exit
    Case Else
    MsgBox "Error No. " & Err.Number & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
    Err.Clear
Resume Error_Handler_Exit
End Select
Exit Sub
End If

am running this in VBA for the unbound combobox.

Private Sub cbxNumber_AfterUpdate()
Me.Filter = "CR_Numbers=" & Me.cbxNumber
Me.FilterOn = True
End Sub

How would I go about using a listbox where I can select multiple fields? I would like to send more than 1 CR per email. I think I would also need some kind of loop code in the email?

[rpeare]

multi select list boxes do not have a 'default' value when an item is selected you have to cycle through the items in the listbox to find out if they are selected

assume your list is named myList

with mylist
    for i = 1 to .listcount - 1
        If .selected(i) then
            debug.print .listcount(0,i)
            'assuming your first column is your bound column you can retreive the pk of the list and then do what you want with it
        endif
    next i
end with

[Thompyt]

With Mylist - MyList should be the listbox control name? I have the following code in the [By Field] Query under the [Field Name] criteria to link it to the Form![Select Field]![My Field] control: Like [Forms]![Select Field].[My Field] & "*"

I should rename MyList to [Field Name] to match the form contol for the loop?

Private Sub Open_By_Field_Report_Click()
Dim MyField As Control
Set MyField = Forms![Copy of Select Field]![My Field]
With MyField
 For i = 1 To Forms![Copy of Select Field]![My Field].ListCount - 1
        If Selected(i) Then
           Debug.Print .ListCount(0, i)
        End If
    Next i
End With
        DoCmd.OpenReport "By Field Report", acViewReport, "", "", acNormal
        DoCmd.Close acForm, "Copy of Select Field"
End Sub

I get a runtime error 451 Property let procedure not defined and property get procedure did not return an object.

[rpeare]

You can't use the items in a multi select list box in a query unless you append them to a temporary table then link your stuff to the items in the temporary table. where I have the debug.print .listcount(0,i) it's just to give you an idea of how to retrieve the index of the list box and use that, which would have to be appended to your temp table.

[Thompyt]

Looks like I'm SOL then as I have no clue, to date of how to start a temp table. Why would you need a temp table when all the answer it is looking for are in a separate table with 2 fields in it. Field_ID & [Field Name]? Would this be where only the selected fields are?

Thanks

[rpeare]

I do not understand what you are trying to say.

A multi select list box can NOT be used as the selection criteria for a query unless you do one of two things.

Cycle through the multi select list box and append the primary key field to a temp table

OR

Create a filter string and apply the filter string to a report

I personally do not use filters, I find them cumbersome and not as easy to use. Where I have the debug.print statement for the value of the multi select list box, that is where you would append a record to your temp table (but you'd have to remember to flush the temp table before you started). Once the temp table is populated with the primary key value of the list box (the bound column) you should be able to build queries based on the temp table (the selected items).

[Thompyt]

Again, I am a novice on this and I do not understand all the intricacies. Since I am unable to make a temp table, denotes I cannot complete. It seems I am out to far into the super intricacies.

Thanks