Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 18th, 2013, 02:59 PM
Registered User
Join Date: Sep 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Exclamation 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" )

With rs
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 = ""
End If
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 = ""

End If

With rs

DoCmd.SendObject , , , "sharvey@disneyworld.com", , , Subj & SubCycle, msgtext, False

End With
MsgBox ("The Collection Confirmation has been sent successfully")
End With

Else ' User chose No.
MsgBox ("Good Bye >>>>>")

End If
End Sub

Last edited by BNW1; September 18th, 2013 at 05:02 PM.. Reason: Need IMMEDIATE help Today Thank You
Old October 8th, 2013, 02:47 PM
Friend of Wrox
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts

Did you get this resolved?
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need code corrections "get sub or function not defined" error" bluesboytoo Excel VBA 1 October 22nd, 2009 11:12 PM
How to theme the "Browse" button of "FileUpload" control? varunbwj BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 2 October 14th, 2009 01:22 AM
Add a CheckBox DataColumn to my DataGridView, Null format: "" or "True" but Error: F ismailc C# 2005 0 September 25th, 2009 04:56 AM
Code not going as planned: "icicle" vs "savedinstancestate" joopthecat BOOK: Professional Android Application Development ISBN: 978-0-470-34471-2 3 May 3rd, 2009 03:09 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.