Use data from subform (List Box) on Form, via Command button?

G

Guest

I am trying to build a form that allows users to select a group of clients by
various characteristics, view that result set in a list box, select any of
those records in the list box and then send an email to all of them.

I cannot figure out how to pass the string I am creating within the List Box
of all the concatenated emails to the email command button. I cannot get the
naming conventions straight...or something more complicated maybe.

here is all the relevant code...any advice is appreciated.

Txs,
Tom

Option Compare Database
Private Sub cmdEmail_Click()
On Error GoTo Err_cmdEmail_Click

Dim strEmail As String
Dim strMailSubject As String
Dim strMsg As String
strEmail = Me![ListBoxClients].strList
strMailSubject = "Subject"
strMsg = "Message"

DoCmd.SendObject objecttype:=acSendNoObject, _
ObjectName:=acSendNoObject, outputformat:=acFormatHTML, _
To:=strEmail, Subject:=strMailSubject, MessageText:=strMsg

Exit_cmdEmail_Click:
Exit Sub

Err_cmdEmail_Click:
MsgBox Err.Description
Resume Exit_cmdEmail_Click

End Sub


Private Sub CommandSearch_Click()

[Forms]![GroupEmail]![ListBoxClients].Requery


End Sub

Private Sub Form_Open(Cancel As Integer)


[Forms]![GroupEmail]![ListBoxClients].Requery

End Sub
Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click


DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub
Private Sub Command24_Click()
On Error GoTo Err_Command24_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub

'Doesn't work
'Private Sub ListBoxClients_Click()
'Dim varItem As Variant
'Dim strList As String

'With Me!ListBoxClients
' 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

Private Sub listBoxClients_Click()

Dim varItem As Variant
Dim strList As String


Dim frm As Form
Dim ctl As Control

Set frm = Forms!GroupEmail
Set ctl = frm!ListBoxClients

For Each varItem In ctl.ItemsSelected
strList = strList & ctl.Column(4, varItem) & ";"
Next varItem

'How can I get the strList passed up to cmdEmail_Click()


End Sub
 
G

Guest

Instead of putting the build of the string (strList) on the click event of
the list box, put it in the click event of the button that sends the email.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.


Tom Mackay said:
I am trying to build a form that allows users to select a group of clients by
various characteristics, view that result set in a list box, select any of
those records in the list box and then send an email to all of them.

I cannot figure out how to pass the string I am creating within the List Box
of all the concatenated emails to the email command button. I cannot get the
naming conventions straight...or something more complicated maybe.

here is all the relevant code...any advice is appreciated.

Txs,
Tom

Option Compare Database
Private Sub cmdEmail_Click()
On Error GoTo Err_cmdEmail_Click

Dim strEmail As String
Dim strMailSubject As String
Dim strMsg As String
strEmail = Me![ListBoxClients].strList
strMailSubject = "Subject"
strMsg = "Message"

DoCmd.SendObject objecttype:=acSendNoObject, _
ObjectName:=acSendNoObject, outputformat:=acFormatHTML, _
To:=strEmail, Subject:=strMailSubject, MessageText:=strMsg

Exit_cmdEmail_Click:
Exit Sub

Err_cmdEmail_Click:
MsgBox Err.Description
Resume Exit_cmdEmail_Click

End Sub


Private Sub CommandSearch_Click()

[Forms]![GroupEmail]![ListBoxClients].Requery


End Sub

Private Sub Form_Open(Cancel As Integer)


[Forms]![GroupEmail]![ListBoxClients].Requery

End Sub
Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click


DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub
Private Sub Command24_Click()
On Error GoTo Err_Command24_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub

'Doesn't work
'Private Sub ListBoxClients_Click()
'Dim varItem As Variant
'Dim strList As String

'With Me!ListBoxClients
' 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

Private Sub listBoxClients_Click()

Dim varItem As Variant
Dim strList As String


Dim frm As Form
Dim ctl As Control

Set frm = Forms!GroupEmail
Set ctl = frm!ListBoxClients

For Each varItem In ctl.ItemsSelected
strList = strList & ctl.Column(4, varItem) & ";"
Next varItem

'How can I get the strList passed up to cmdEmail_Click()


End Sub
Click to expand...
 
G

Guest

Bob...you are a superstar...thanks for the advice, first attempt worked
perfectly! I have a lot to learn still.

Thanks for the help, it's really appreciated.

Tom

boblarson said:
Instead of putting the build of the string (strList) on the click event of
the list box, put it in the click event of the button that sends the email.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.


Tom Mackay said:
I am trying to build a form that allows users to select a group of clients by
various characteristics, view that result set in a list box, select any of
those records in the list box and then send an email to all of them.

I cannot figure out how to pass the string I am creating within the List Box
of all the concatenated emails to the email command button. I cannot get the
naming conventions straight...or something more complicated maybe.

here is all the relevant code...any advice is appreciated.

Txs,
Tom

Option Compare Database
Private Sub cmdEmail_Click()
On Error GoTo Err_cmdEmail_Click

Dim strEmail As String
Dim strMailSubject As String
Dim strMsg As String
strEmail = Me![ListBoxClients].strList
strMailSubject = "Subject"
strMsg = "Message"

DoCmd.SendObject objecttype:=acSendNoObject, _
ObjectName:=acSendNoObject, outputformat:=acFormatHTML, _
To:=strEmail, Subject:=strMailSubject, MessageText:=strMsg

Exit_cmdEmail_Click:
Exit Sub

Err_cmdEmail_Click:
MsgBox Err.Description
Resume Exit_cmdEmail_Click

End Sub


Private Sub CommandSearch_Click()

[Forms]![GroupEmail]![ListBoxClients].Requery


End Sub

Private Sub Form_Open(Cancel As Integer)


[Forms]![GroupEmail]![ListBoxClients].Requery

End Sub
Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click


DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub
Private Sub Command24_Click()
On Error GoTo Err_Command24_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub

'Doesn't work
'Private Sub ListBoxClients_Click()
'Dim varItem As Variant
'Dim strList As String

'With Me!ListBoxClients
' 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

Private Sub listBoxClients_Click()

Dim varItem As Variant
Dim strList As String


Dim frm As Form
Dim ctl As Control

Set frm = Forms!GroupEmail
Set ctl = frm!ListBoxClients

For Each varItem In ctl.ItemsSelected
strList = strList & ctl.Column(4, varItem) & ";"
Next varItem

'How can I get the strList passed up to cmdEmail_Click()


End Sub
Click to expand...
Click to expand...
 
G

Guest

Glad I could help.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.


Tom Mackay said:
Bob...you are a superstar...thanks for the advice, first attempt worked
perfectly! I have a lot to learn still.

Thanks for the help, it's really appreciated.

Tom

boblarson said:
Instead of putting the build of the string (strList) on the click event of
the list box, put it in the click event of the button that sends the email.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.


Tom Mackay said:
I am trying to build a form that allows users to select a group of clients by
various characteristics, view that result set in a list box, select any of
those records in the list box and then send an email to all of them.

I cannot figure out how to pass the string I am creating within the List Box
of all the concatenated emails to the email command button. I cannot get the
naming conventions straight...or something more complicated maybe.

here is all the relevant code...any advice is appreciated.

Txs,
Tom

Option Compare Database
Private Sub cmdEmail_Click()
On Error GoTo Err_cmdEmail_Click

Dim strEmail As String
Dim strMailSubject As String
Dim strMsg As String
strEmail = Me![ListBoxClients].strList
strMailSubject = "Subject"
strMsg = "Message"

DoCmd.SendObject objecttype:=acSendNoObject, _
ObjectName:=acSendNoObject, outputformat:=acFormatHTML, _
To:=strEmail, Subject:=strMailSubject, MessageText:=strMsg

Exit_cmdEmail_Click:
Exit Sub

Err_cmdEmail_Click:
MsgBox Err.Description
Resume Exit_cmdEmail_Click

End Sub


Private Sub CommandSearch_Click()

[Forms]![GroupEmail]![ListBoxClients].Requery


End Sub

Private Sub Form_Open(Cancel As Integer)


[Forms]![GroupEmail]![ListBoxClients].Requery

End Sub
Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click


DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub
Private Sub Command24_Click()
On Error GoTo Err_Command24_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub

'Doesn't work
'Private Sub ListBoxClients_Click()
'Dim varItem As Variant
'Dim strList As String

'With Me!ListBoxClients
' 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

Private Sub listBoxClients_Click()

Dim varItem As Variant
Dim strList As String


Dim frm As Form
Dim ctl As Control

Set frm = Forms!GroupEmail
Set ctl = frm!ListBoxClients

For Each varItem In ctl.ItemsSelected
strList = strList & ctl.Column(4, varItem) & ";"
Next varItem

'How can I get the strList passed up to cmdEmail_Click()


End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Get Data From List Box Selection 2
Send Objects Freezes Access on Cancel 3
Pass selected items in list box as a string 3
query based off Multi select list box 4
email as a pdf 1
Linking List box selection to a query 7
email forms and reports 1
Email from Access2K2 and Outlook 2007? 9

Top