Change VBA Code for a Command Button "Help" ASAP
Hello my coworker is having issues with a command button is whenever she sends email to a State, it would stop processing at a particular using the first command button. However, the second button "restart button" she wants me to change the code for that will allow to her should a State at any any position and to continue to the last state until all emails are finished. However, there is a parameter query which prompts her to enter a state but she doesn't want that any more. In addition, through the process of the code there is a Macro "which runs the parameter query" and a recordset that is opened and the code continue to run an email in sent to a State. My question is how can I change to code to help her stop entering a State so she can choose any State and the rest of the States without her choosing them can be emailed right after the State she chose? Here is an example of the code. Also, do you think that not having the "rs" in front of .movenext is preventing the program from processing "sending emails" to the other states when she press the "first command" button to send emails to all of the states? The code below is for the send command button. "Its similar to the code for the first command button. Only difference is it have only Recordset and table name. I need help. Can I create a combo box listing the states and she can chose the sTates? Or, how can I insert a code or a popup form with vba code for states from a combo box in the below codes to be able to send emails using the email fields below? I would post the query but it is too long and have many tables.
DoCmd.RunMacro ("Test Email Collection") - this is a parameter query
'Set rs = db.OpenRecordset("TestRestartEmailCollectionTable" )
While Not .EOF
SubCycle = !Cycle
Me.STATE = !St
Me.weeklyCount = !Count
Me.AMOUNT = ![AMOUNT] * 0.01
strEmail = IIf(IsNull(![EMail Address]), "", ![EMail Address])
strEmailcc = IIf(IsNull(![EMail Address 1]), "", ![EMail Address 1])
If strEmail = "" And strEmailcc = "" Then
MsgBox "No email address is available for the State of " & !STATE & "!", vbInformation
If strEmail = "" Then
strEmail = strEmailcc
strEmailcc = ""
StateName = !St
'SendMail "Email Collection", strEmail, strEmailcc, , Subj & !Cycle, Msgtext, False
'DoCmd.SendObject acSendReport, "Email Collection", acFormatRTF, strEmail, strEmailcc, , "Weekly Confirmation for Cycle " & !Cycle, vbCr & vbCr & "The attached word document is your notification of this week's Processable file." & vbCr & vbCr & vbCr & "Thanks" & vbCr & vbCr & vbCr & "TOP Operations" & vbCr & vbCr & vbCr & vbCr & "703-305-2314", False
DoCmd.SendObject acSendReport, "TestEmail Collection", acFormatRTF, strEmail, strEmailcc, , Subj & !Cycle, msgtext, False
StateName = ""
DoCmd.SendObject , , , "firstname.lastname@example.org", , , Subj & SubCycle, msgtext, False
MsgBox ("The Collection Confirmation has been sent successfully")
Else ' User chose No.
MsgBox ("Good Bye >>>>>")
Last edited by BNW1; September 18th, 2013 at 05:02 PM..
Reason: Need IMMEDIATE help Today Thank You