Change a Command Button to send emails "report" I Need Programming Help ASAP" Thanks

[BNW2]

Hello ItsMe

I added the rest of the code you gave me. However, after I complled it, it gave me an error message "Wrong number of argumentrs or Invalid Property Assignnent when I input this string strEmail & ctlList.ItemData(0, VarItem) & "; "

forgot the separator

strEmail = strEmail & ctlList.ItemData(0, VarItem) & "; "



I belive email addresses use the semicolon

[ItsMe]

Search your code in that module to find where strEmail is declared.

I thought that might happen. It seemed odd to me that your StateNames sub is doing nothing other than debugging.

My guess is you need to be gathering the info from your ctlList nearby where your strEmail is decalred.

Why do you have this click event? Is it to test the info gathered.

Are you able to follow how the user is triggering the events?

[BNW2]

Hello IsMe.

In order to get rid of the compile error, I had to remove the zero near ItemData and change the "extended" back to None in order for it to run and for me to select a State. I couldn't select more than one State with "extended" or Simple changed in the Multiselect section on the Other tab.

I am still able to paste 1 record into the table in which I need to paste more than one.

Hello ItsMe

I added the rest of the code you gave me. However, after I complled it, it gave me an error message "Wrong number of argumentrs or Invalid Property Assignnent when I input this string strEmail & ctlList.ItemData(0, VarItem) & "; "

[ItsMe]

I will see if I can mock something up here to pull info from a listbox.

There error was not from strEmail not being declared? Maybe it has something to do with only one column. I believe listboxes return integers. I will check it out.

[ItsMe]

You need to have a least two columns in your list box. The first one will be the PK from your table. You will bind your PK column 1 in the properties (Data TAB) so your list box will work. Within VBA column 1 will be the column that has the text.

Column widths in the properties will be something like 0"; 1" to hide the PK

I put the following in to a blank DB with one table and one form. Form has a list box and a button. I put this into the click event of the button.

Private Sub cmdTEst_Click()
Dim strEmail As String
Dim varItem As Variant
For Each varItem In ctlList.ItemsSelected
strEmail = strEmail & Me.ctlList.Column(1, varItem) & " "
Next varItem
MsgBox strEmail
End Sub

[BNW2]

Hello IsMe


Thanks for responding back. I have thought about putting a primary key field in the States Table. You are saying I will be able to multiselect the control to allow me to select more than one State? The listbox will be near the Restart Email Collection Command Button on the form "this button have the main vba code behind it". I pasted the code in this forum already when I first started my thread. What I have been doing is select a state from the listbox and press the command button and only one State is entered into the "TestRestartEmailCollectionTable". However, there is a parameter query that is within the Macro DoCmd.RunMacro (TestRestartCollection1). This is the parameter syntax =[Forms]![Email Collection]![StateNames]
based on an SQL Statement that have other tables that are joined together which allowed me to input the information into the table "TestRestartEmailCollectionTable. I just that I need to be able to input more than one State into the table at the same time.

You need to have a least two columns in your list box. The first one will be the PK from your table. You will bind your PK column 1 in the properties (Data TAB) so your list box will work. Within VBA column 1 will be the column that has the text.

Column widths in the properties will be something like 0"; 1" to hide the PK

I put the following in to a blank DB with one table and one form. Form has a list box and a button. I put this into the click event of the button.

Private Sub cmdTEst_Click()
Dim strEmail As String
Dim varItem As Variant
For Each varItem In ctlList.ItemsSelected
strEmail = strEmail & Me.ctlList.Column(1, varItem) & " "
Next varItem
MsgBox strEmail
End Sub

[ItsMe]

I don't know what to say. You need to concentrate on one section of code and make sure it is doing what you need it to do. You mention populating a table with text that represents names of states selected from a list box. Assuming your list box is set to Extended, the code you pasted in post # 12 should show the results of what is selected in the immediate window of your VBA editor.

Debug.Print ctlList.ItemData(VarItem)

The above line will do that for you except

You have this
Set Frm = Forms![Email Collection]
Set ctlList = Frm!StateNames

This tells me you are not on the same form as the listbox OR you changed the listbox name to ctlList when you built a new form for testing purposes OR you copied some VBA from the help files and pasted it all willy nilly.

I did learn a couple things that I will share with you though. You don't need a key in your list box. You can have only one column and it will work fine. When using .ItemData you don't need to distinguish which column (at least not when there is only one column). So... ctlList.ItemData(VarItem) will work fine as long as you have the correct control name.

AFTER you square away all of that. You need to figure out how your macro is called. Thgere are only two ways I have used to get info from a multilist listbox. One example I already showed you and that is to collect it in a string variable. The other is to step through the selected records and add them to a table one by one. And I have always passed the PK to the FK field.

I suggest you start with getting your list box to work by figuring out what the control name is and what form it is on.

[BNW2]

Hello IsMe

My control name (variable name) is ctlList when I Dimmed my variable
However, my listbox name is StatesName

I don't know what to say. You need to concentrate on one section of code and make sure it is doing what you need it to do. You mention populating a table with text that represents names of states selected from a list box. Assuming your list box is set to Extended, the code you pasted in post # 12 should show the results of what is selected in the immediate window of your VBA editor.

Debug.Print ctlList.ItemData(VarItem)

The above line will do that for you except

You have this
Set Frm = Forms![Email Collection]
Set ctlList = Frm!StateNames

This tells me you are not on the same form as the listbox OR you changed the listbox name to ctlList when you built a new form for testing purposes OR you copied some VBA from the help files and pasted it all willy nilly.

I did learn a couple things that I will share with you though. You don't need a key in your list box. You can have only one column and it will work fine. When using .ItemData you don't need to distinguish which column (at least not when there is only one column). So... ctlList.ItemData(VarItem) will work fine as long as you have the correct control name.

AFTER you square away all of that. You need to figure out how your macro is called. Thgere are only two ways I have used to get info from a multilist listbox. One example I already showed you and that is to collect it in a string variable. The other is to step through the selected records and add them to a table one by one. And I have always passed the PK to the FK field.

I suggest you start with getting your list box to work by figuring out what the control name is and what form it is on.

[ItsMe]

In post #5 I had some questions on what went wrong and when. I also suggested putting aside making any changes to the program until you figure out what works and what doesn't.

As for your click event, this will do a real good job of displaying the results of what was selected in the listbox named StateNames that resides in form Email Collections in your VBA editor's Imediate Window.

I imagine it is possible to have a macro grab the array from the debugger. I have no experience with this. I use the debugger to test code. Which is why one of my other questions was what are you trying to acomplish with your click event. I can not be of any help if what your code is doing is outside of my experience level.

I am having a hard time understanding the relevance of your click event. I suggest you test its functionality by observing the result of it firing your VBA. You can view the result in the Imediate Window and go from there.

[BNW2]

Hello Its Me


Thanks for responding. When I click on the State from a the listbox and click the Restart Email Button, the State is listed in the immediate Window. However, my main concern is to be able to select more than one State and have the State information in the TestRestartEmailCllectionTable. In addtion, the vba code first runs a make table query " here is the make table query below". Where the bolded text is my form name and control name. Is there someway I can insert a character "Syntax" in the parameter string =[Forms]![Email Collection]![StateNames])
that will allow me to select more than one state? There is no way possible I can modify this sql query syntax below which is referencing the fields from different tables. Another person who was in my position designed this query. Also, everything is working fine when the Other Tab for multiselect is "None", and the State and information is entered into the TestRestartEmailCollectionTable. It just that I can not do a multiselect selection from the listbox when I change the Other tab to "simple and extended" and try to import more than one state into the "TestRestartEmailCollectionTable"


SELECT TestCollectionsInput.SubAgency AS State, TestCollectionsInput.NetCollectionsAmount AS Amount, TestCollectionsInput.NetCollectionCount AS [Count], [TestNew State Contacts].Company, [TestNew State Contacts].[Address 1], [TestNew State Contacts].[Address 2], [TestNew State Contacts].Department, [TestNew State Contacts].[City 1], [TestNew State Contacts].[Zip Code], [TestNew State Contacts].[Notify Name], [TestNew State Contacts].[EMail Address], [TestNew State Contacts].[EMail Address 1], [TestNew State Contacts].Region, [TestNew State Contacts].[Region Name], [TestNew State Contacts].[Currently Participating], [TestNew State Contacts].[Participating Next Year], [TestNew State Contacts].[Returned Survey], [TestNew State Contacts].[Data Transmission Type], [TestNew State Contacts].[Primary Contact], [TestNew State Contacts].[Primary Contact Phone #], [TestNew State Contacts].[Primary Contact Phone # Ext], [TestNew State Contacts].[Technical Contact], [TestNew State Contacts].[Technical Contact Phone # Ext], [TestNew State Contacts].[Technical Contact Phone #], [TestNew State Contacts].[Other Contact], [TestNew State Contacts].[Other Contact Phone #], [TestNew State Contacts].[Other Contact Phone # Ext], [TestNew State Contacts].St, [TestNew State Contacts].[network security contact], TestCollectionsInput.Cycle INTO TestRestartEmailCollectionTable
FROM TestCollectionsInput INNER JOIN [TestNew State Contacts] ON TestCollectionsInput.SubAgency=[TestNew State Contacts].St
WHERE ((TestCollectionsInput.SubAgency)=[Forms]![Email Collection]![StateNames])
ORDER BY TestCollectionsInput.SubAgency;









"TestRestartEmailColllection1" then the Recordset is opened to insert the data into the "TestRestartEmailCollectionTable" and then emails are sent to various people attaching a report "testEmail Collection". Alo

In post #5 I had some questions on what went wrong and when. I also suggested putting aside making any changes to the program until you figure out what works and what doesn't.

As for your click event, this will do a real good job of displaying the results of what was selected in the listbox named StateNames that resides in form Email Collections in your VBA editor's Imediate Window.

I imagine it is possible to have a macro grab the array from the debugger. I have no experience with this. I use the debugger to test code. Which is why one of my other questions was what are you trying to acomplish with your click event. I can not be of any help if what your code is doing is outside of my experience level.

I am having a hard time understanding the relevance of your click event. I suggest you test its functionality by observing the result of it firing your VBA. You can view the result in the Imediate Window and go from there.

[ItsMe]

I will keep your post # 25 in mind. Meanwhile, lets focus on the first issue.

Multiselect

Change the Multi Properties option to Extended on your control named "StateNames".

Create a new control button named ctlList on your form "Email Collection". In this new control button, create a click event and place the following code into it.


'Dim Frm As Form
'Dim ctlList As Control
Dim VarItem As Variant
''Return control object varible pointing to list box
'Set Frm = Forms![Email Collection]
'Set ctlList = Frm!StateNames
''Enumerate through selected items.
For Each VarItem In StateNames.ItemsSelected
'Print value of bound column.
Debug.Print StateNames.ItemData(VarItem)
Next VarItem
'end code

Go back to your form in form view and click your new button. Verify your new control is doing something satisfactory by studying the Immediate Window, i.e. the multiselect property is working.

[ItsMe]

I just noticed this line needs to change too. I apologize. I am a little tired....

For Each VarItem In StateNames.ItemsSelected

[BNW2]

The "For Each VarItem In StateNames.ItemsSelected" needs to be changed to what?

[ItsMe]

I edited post # 26

it works, I tested it. Check the edit time

[BNW2]

Can I send you a snapshot of the form to your email address today? I want you to see how the form looks with the ListBox control and the Restart Button. The small little button is the main button that is pressed but when the process stop working at a particular state. The Restart button is used.

I edited post # 26

it works, I tested it. Check the edit time