Copy selected values from listbox to textbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to copy only the selected values from a listbox in a subform
to a textbox on the main form and format the selected text so that each
selection is separated by a semicolon in the textbox? I am trying to select
multiple email addresses from the listbox and copy them to the textbox where
the SendObject command will use the textbox to address the email. When I use
two listboxes, SendObject only sends the email to the first address listed.
Any suggestions?
 
This is exactly what you are looking for:

Private Sub lstEmail_Click()
Dim varItem As Variant
Dim strList As String

With Me!lstEmail
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ";"
Next varItem
strList = Left$(strList, Len(strList) - 1)
Me!txtSelected = strList
End If
End With
End Sub

Perhaps some error handling might be in order.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
I think this will work, but the listbox is on a separate form than the
textbox and when the textbox form is closed, the code reports an error and
says it can't find the form with the textbox. Is there a way to get around
this? My code is below:
----------
Private Sub Form_Close()

Dim varItem As Variant
Dim strList As String

With Me!lstProgram_Manager
If .MultiSelect = 0 Then
Forms![Tasks Requiring to be scheduled].txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ";"
Next varItem
strList = Left$(strList, Len(strList) - 1)
Forms![Tasks Requiring to be scheduled].txtSelected = strList
End If
End With
End Sub
 
Ok, I'm partly there. I was able to copy the selected items from the list
box (lst_PM) in the form (Frm_PM) to the text box (T_Reminder_Mailing_List)
in a separate form (TblCompliance_General). I am having two problems:

1. Once I close the form with the text box (TblCompliance_General), the
selected text that was copied from the list box goes away. I need to be able
to save the text in the text box.

2. I have a third form (Tasks Requiring to be scheduled) that runs on a
query and sends emails to the program managers using T_Reminder_Mailing_List
in TblCompliance_General. This will only work if TblCompliance_General is
open. Is there a way to send the emails when TblCompliance_General is
closed? My email code is pasted below:

---------------
Sub SeparateEmails()
On Error GoTo Err_SeparateEmails

Dim strUser As Control
Dim strAct As Control

Forms![Tasks Requiring to be scheduled].Filter = "[AN_AutoNumber] = " &
Me![AN_AutoNumber]
Forms![Tasks Requiring to be scheduled].FilterOn = True

Forms![TblCompliance_General].Filter = "[AN_AutoNumber] = " &
Me![AN_AutoNumber]
Forms![TblCompliance_General].FilterOn = True


DT_Scheduled = Now()

Set strUser = [Form_TblCompliance_General].T_Reminder_Mailing_List
Set strAct = [Form_Tasks Requiring to be scheduled].T_CompAct
DoCmd.SendObject acSendForm, "Tasks Requiring to be scheduled", "HTML
(*.htm; *.html)", strUser, "", "", "Please schedule: " + strAct, "See
attached report for details. Please reply to this message for schedule
confirmation.", True, ""

Forms![Tasks Requiring to be scheduled].Filter = ""
Forms![TblCompliance_General].Filter = ""

Exit_SeparateEmails:
Exit Sub

Err_SeparateEmails:
MsgBox Err.Description
Forms![Tasks Requiring to be scheduled].Filter = ""
Forms![TblCompliance_General].Filter = ""
Resume Exit_SeparateEmails

End Sub
 
Back
Top