Up For Reviewing Complex-ish Button Automation Code? Here Ya Go...

[Soule]

Hello, Fellow Users,
I need a brave soul (or 5) to peruse this de-sensitized button code for any obvious or unobvious errors. In the VB editor, there are no red “error” lines of text...but I haven’t run the compiler yet. I tried to keep it as simple as possible given the scope. As you’ll see, there are some questions tagged in text within the code itself. The Db in question has one form and 3 tables. The button is on the form and is currently source controlled to only one table. The one form control that uses a SELECT/FROM statement to populate the form with a movie code is the only one that’s unbound. This version has back-to-back commands referring to one recordset, though I’d like to try another sequence - this is explained at the bottom of this post. The steps the button is to execute with back-to-back same recordset commands are as follows:

1. Button is clicked, record is saved to recordset and table.
2. Access opens Outlook using GetObject on an .oft template.
3. Populates the “To:” in .oft template.
4. Populates the “From:” in .oft template.
5. Populates the “Subject:” in .oft template.
6. Populates contact information in .oft template.
Note: In steps 7 & 8, I have the two commands that deal with the recordset/”Unused” table one-following-the-other so as to not have to close the recordset and then re-open it.
7. Opens recordset/“Unused” table, copies top code off “Movie Code” column, populates form control of same name with it.
8. While recordset/”Unused” table is still “active”, Access again selects top code off “Movie Code” column and moves (not copys) it to same named column in “Used” table. Recordset is closed.
9. Access populates the “movie code” text in .oft template.
10. Outlook .oft template is send as message. End Sub.
------------------------------------------------------

Private Sub AS1_Form_Re_send_Welcome_E_Mail_Only_Button_Click( )
' Re-send only employee Outlook e-mail populated with Access data using Outlook .oft template by clicking button. Button code will draw on data in controls of RECORD SHOWING.
' Used GetObject method rather than CreateItemFromTemplate method for simplicity.
' Namespace/MAPI commands not used as some employees (i.e. Animation) are on mail client other than Outlook.
' On button click but before automation begins, record will be saved to table but REMAIN ONSCREEN (not go to new, blank record). This first line of code does that...

DoCmd.RunCommand acCmdSaveRecord

' Now, the automation begins. Declaration statement(s)...
Dim objOutlookMsg As Object

' This next line opens Outlook by retrieving employee welcome e-mail template...
' ,Class needed in pathname or not?

objOutlookMsg = GetObject("J:\Special Projects\Database Work\AS1 Tracking DB & Related\AS1 Form Button Automation Email\Employee AS1 Welcome Outlook Template.oft")

' These next lines check the "Known As" data in the record, and if it's not null, populate the "To:" field in email; if it's null, "First Name" data in record should populate "To:" field instead.

objOutlookMsg.To = Replace("<<Known As>>", "<<Known As>>", "[AS1 Onboarding Tracking Table]![Known As]")
If IsNull("[AS1 Onboarding Tracking Table]![Known As]") Then
ReplaceNull = ("[AS1 Onboarding Tracking Table]![First Name]")
End If

' This next line populates the mail's "From" line from data in the "HR EOD Contact Name" form control.

objOutlookMsg.From = ("[AS1 Onboarding Tracking Table]![HR EOD Contact Name]")

' These next lines auto-fill the mail subject with boilerplate...
objOutlookMsg.Subject = "Congratulations and Welcome To XXXXXXXXX!"

' Body is almost all boilerplate (only HR EOD contact and movie code sections need populating).

objOutlookMsg.BodyFormat = olFormatRichText
objOutlookMsg.Body = Replace("<<HR EOD Contact Name>>", "<<HR EOD Contact Name>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Name]")


objOutlookMsg.Body = Replace("<<HR EOD Contact Internal Phone #>>", "<<HR EOD Contact Internal Phone #>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Internal Phone #]")
objOutlookMsg.Body = Replace("<<HR EOD Contact Internal E-Mail>>", "<<HR EOD Contact Internal E-Mail>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Internal E-Mail]")
Exit Sub

' These next lines copy the top movie code (sorted ascending) from "Unused Movie Code Table" and populate "Movie Code" control on form.

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT TOP 1 [Unused Movie Code Table].Movie Code" & "FROM [Unused Movie Code Table]" & "WHERE [Movie Code] = Me.[Movie Code]" & "ORDER BY Movie Code ASC")
rs![Movie Code] = Me.[Movie Code]
‘ Is this line needed in this case?
rs.Update

' This next line then moves (not copies) that movie code from "Unused Movie Code Table" to "Used Movie Code Table".

CurrentDb.Execute "INSERT INTO [Used Movie Code Table].Movie Code" & "SELECT TOP 1 [Unused Movie Code Table].Movie Code" & "FROM [Unused Movie Code Table]" & "ORDER BY Movie Code ASC"
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub

' This next line populates <<movie code>> text on template with "Movie Code" control's data on form.
objOutlookMsg.Body = Replace("<<movie code>>", "<<movie code>>", "[AS1 Onboarding Tracking Table]![movie code]")
Exit Sub

' This next line re-sends the welcome e-mail only. User can verify it was sent in mail account's "Sent Items" box.

objOutlookMsg.Send
Set objOutlookMsg = Nothing
End Sub
---------------------------------
Aesthetically, I would like to do the last population on the Outlook template and get it sent off before I move the code I populated the form and template with from the “Unused” table to the “Used” table. Moving this code last would also help the user avoid any manual digging in the tables for the movie code should the code freeze and fail to populate the form control anytime after the automation button is clicked. Here is the sequence I really, really want. (Basically steps 9 & 10 in the above configuration are put between steps 7 & 8). Is it a bad idea to close the recordset, send the mail off, then open the recordset again to move the used code? Is it even possible? In coding terms, would that be too messy/buggy?

1. Button is clicked, record is saved to recordset and table.
2. Access opens Outlook using GetObject on an .oft template.
3. Populates the “To:” in .oft template.
4. Populates the “From:” in .oft template.
5. Populates the “Subject:” in .oft template.
6. Populates contact information in .oft template.
7. Opens recordset/“Unused” table, copies top code off “Movie Code” column, populates form control of same name with it. Recordset is closed.
8. Access populates the “movie code” text in .oft template from form control data.
9. Outlook .oft template is send as message.
10. Recordset/”Unused” table is re-opened with db.OpenRecordset function, Access again selects top code off “Movie Code” column and moves (not copys) it to same named column in “Used” table. Recordset is closed a second time. End Sub.

Thanks so much for your time in reviewing this. Any sure-thing advice would be appreciated, especially from intermediate and expert VB forum users. I want the code to be as simple as possible. Feel free to highlight/notate the heck out of it.

Frank

[June7]

Looks good enough to me. Time to Compile, Test, Debug.

[Soule]

Looks good enough to me. Time to Compile, Test, Debug.

May you live to be a thousand years old, Ma'am.

[ssanfu]

What I see......

-You shouldn't use spaces and special characters in object names. It is a pain in the neck, only two feet lower!
No one but the programmer should see the {code/variables/field names/control names}, so English with spaces doesn't matter. See

http://access.mvps.org/access/tencommandments.htm

.....especially rules 2 & 3


-You missed brackets around "Movie Code" in one SQL string.

- The first two lines in the code should be:
Option Compare Database
Option Explicit

- Must have a reference set to Microsoft DAO 3.6 Object Library

- I changed and commented your code. I did not and could not compile your code.. just used my "good eye" (haha I wear glasses'')

Private Sub AS1_Form_Re_send_Welcome_E_Mail_Only_Button_Click()
' Re-send only employee Outlook e-mail populated with Access data using Outlook .oft template by clicking button. Button code will draw on data in controls of RECORD SHOWING.
' Used GetObject method rather than CreateItemFromTemplate method for simplicity.
' Namespace/MAPI commands not used as some employees (i.e. Animation) are on mail client other than Outlook.
' On button click but before automation begins, record will be saved to table but REMAIN ONSCREEN (not go to new, blank record). This first line of code does that...

'DoCmd.RunCommand acCmdSaveRecord
   ' save record - New way!
   If Me.Dirty Then
      Me.Dirty = False
   End If

   ' Now, the automation begins. Declaration statement(s)...
   Dim objOutlookMsg As Object

   ' This next line opens Outlook by retrieving employee welcome e-mail template...
   ' ,Class needed in pathname or not?
   objOutlookMsg = GetObject("J:\Special Projects\Database Work\AS1 Tracking DB & Related\AS1 Form Button Automation Email\Employee AS1 Welcome Outlook Template.oft")

   ' These next lines check the "Known As" data in the record, and if it's not null, populate the "To:" field in email; if it's null, "First Name" data in record should populate "To:" field instead.
   objOutlookMsg.To = Replace("<<Known As>>", "<<Known As>>", "[AS1 Onboarding Tracking Table]![Known As]")
   If IsNull("[AS1 Onboarding Tracking Table]![Known As]") Then
      ReplaceNull = ("[AS1 Onboarding Tracking Table]![First Name]")
   End If

   ' This next line populates the mail's "From" line from data in the "HR EOD Contact Name" form control.
   objOutlookMsg.FROM = ("[AS1 Onboarding Tracking Table]![HR EOD Contact Name]")

   ' These next lines auto-fill the mail subject with boilerplate...
   objOutlookMsg.Subject = "Congratulations and Welcome To XXXXXXXXX!"

   ' Body is almost all boilerplate (only HR EOD contact and movie code sections need populating).

   objOutlookMsg.BodyFormat = olFormatRichText
   objOutlookMsg.Body = Replace("<<HR EOD Contact Name>>", "<<HR EOD Contact Name>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Name]")
   objOutlookMsg.Body = Replace("<<HR EOD Contact Internal Phone #>>", "<<HR EOD Contact Internal Phone #>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Internal Phone #]")
   objOutlookMsg.Body = Replace("<<HR EOD Contact Internal E-Mail>>", "<<HR EOD Contact Internal E-Mail>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Internal E-Mail]")
   Exit Sub

   ' These next lines copy the top movie code (sorted ascending) from "Unused Movie Code Table" and populate "Movie Code" control on form.

   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim sSQL As String

   Set db = CurrentDb
   
   sSQL = "SELECT TOP 1 [Unused Movie Code Table].[Movie Code]"
   sSQL = sSQL & " FROM [Unused Movie Code Table]"
   sSQL = sSQL & " WHERE [Movie Code] = '" & Me.[Movie Code] & "'"
   sSQL = sSQL & " ORDER BY Movie Code ASC"
   
   Set rs = db.OpenRecordset(sSQL)
   
   '--- you should check to see if the record set has records ---
   rs.Edit
   rs![Movie Code] = Me.[Movie Code]
   ' Is this line needed in this case?  YES , if you use DAO
   rs.Update
   rs.Close
   Set rs = Nothing
   Set db = Nothing

   ' This next line then moves (not copies) that movie code from "Unused Movie Code Table" to "Used Movie Code Table".
   '---No, this only copies, does not delete.---
   ' and I'm not sure that you will be inserting the same record as selected on the form (emailed).
   sSQL = "INSERT INTO [Used Movie Code Table].Movie Code"
   sSQL = sSQL & " SELECT TOP 1 [Unused Movie Code Table].Movie Code "
   sSQL = sSQL & " FROM [Unused Movie Code Table]"
   sSQL = sSQL & " ORDER BY Movie Code ASC"
   'debug.print sSQL

   CurrentDb.Execute sSQL, dbFailOnError
   '---  Exit Sub   DO YOU REALLY WANT TO EXIT THE SUB HERE??

   ' This next line populates <<movie code>> text on template with "Movie Code" control's data on form.
   objOutlookMsg.Body = Replace("<<movie code>>", "<<movie code>>", "[AS1 Onboarding Tracking Table]![movie code]")
   '---  Exit Sub  DO YOU REALLY WANT TO EXIT THE SUB HERE??

   ' This next line re-sends the welcome e-mail only. User can verify it was sent in mail account's "Sent Items" box.

   objOutlookMsg.Send
   Set objOutlookMsg = Nothing
End Sub

[Soule]

Thanks, ssanfu, for looking at ALL my code and replying1 Very appreciated. Going through the list of your suggestions:

I did a renaming convention throughout my whole Db and got rid of object names with spaces and special characters. Good advice.

I moved my button code from a standard module into the correct object in the main form class module. I then took the brackets off the "Me.ControlName" control statements where they weren't needed anymore, but kept them in my SELECT/FROM/WHERE/ORDER BY statement. Right?

My Db is an .accdb file, so I'm not using a reference to Microsoft DAO 3.6 Object Library. Correct?

I'm not going to do an "If Dirty Then" save record command because my users will always be running this code automation button after they have either entered a new record or revisited one. A straight save in either case is all that is needed. Is that wise?

In the section where I want to copy the top-in-a-table-field movie code and populate a form control with it, do I really need to "Dim sSQL As String" if I'm not using DAO?

And assuming I get the correct code to completely move (not just copy) my "movie code" data from the "Unused" table to the "Used" table, do I need to use that semi-structured query language/sSQL layout there and the "Set rs = db.OpenRecordset(sSQL)" command? What is the advantage?

My recordset currently has records and won't ever be empty, so I don't think I'll need to check with the "rs.Edit" statement. ??

I deleted those two "Exit Sub" commands you tagged.

Thank you soooo much for taking the time to look at my code. This is my first major automation button job. It's like I'm learning how to drive while on the Indy 500 track. Any further ideas are much welcome. Thanks.

[ssanfu]

I did a renaming convention throughout my whole Db and got rid of object names with spaces and special characters. Good advice.

Does every code page have these two lines at the top:

Option Compare Database
Option Explicit

If not, they should be!

I moved my button code from a standard module into the correct object in the main form class module. I then took the brackets off the "Me.ControlName" control statements where they weren't needed anymore, but kept them in my SELECT/FROM/WHERE/ORDER BY statement. Right?

Yes, but you could have left the brackets.....

My Db is an .accdb file, so I'm not using a reference to Microsoft DAO 3.6 Object Library. Correct?

MDB or ACCDB are just versions of Access file type.
ADO and DAO are how you access (no pun intended) the data in the file.
I use DAO; it was designed to utilize (control?) Jet, the database engine. So, yes, a reference to Microsoft DAO 3.x Object Library is necessary. (Note: the "x" is the sub version)

I'm not going to do an "If Dirty Then" save record command because my users will always be running this code automation button after they have either entered a new record or revisited one. A straight save in either case is all that is needed. Is that wise?

DoCmd.RunCommand acCmdSaveRecord is, for lack of a better term, is the "old" way to save a record.
If you want it down to one line, Me.Dirty = False will also work. Using "Me.Dirty...." is a better way to save a record.

In the section where I want to copy the top-in-a-table-field movie code and populate a form control with it, do I really need to "Dim sSQL As String" if I'm not using DAO?

Has nothing to do with DAO. I use "Dim sSQL As String" to create the SQL string so I can use the "Debug.Print" to trouble shoot what the SQL statement looks like with parameters. And it is easier (for me) to create the SQL because I can break the lines at each clause.
I also use "Dim sSQL As String" when I create action queries in code.

And assuming I get the correct code to completely move (not just copy) my "movie code" data from the "Unused" table to the "Used" table, do I need to use that semi-structured query language/sSQL layout there and the "Set rs = db.OpenRecordset(sSQL)" command? What is the advantage?

You need to delete the record from the unused table.
Actually, I would just have one table, with a field to indicate if the "movie code" is "Unused" or "Used". Less bloat, duplication and easier to code.

The advantage to "that semi-structured query language/sSQL layout" is it is cleaner (easier to read) code.

My recordset currently has records and won't ever be empty, so I don't think I'll need to check with the "rs.Edit" statement. ??

Would you ever cross a street without looking, even if you "knew" there were no cars coming?
It is better programming practice to check if records exist, rather than ASSUME there will *always* be records. Your choice....

[Soule]

ssanfu,

Thank you so much for answering my questions in detail again. I'm going to take everyone's advice and not attempt to move code between an "unused" and "used" table. I see now it's really not needed and could cause lost codes and $$. I am also probably going to go with the sSQL layout use an rs.Edit statement to verify data. Better to be safe. One thing about DAO...when I tried to add the library reference to my DAO Object Library last week, I got an error message. I'll post it here soon to see if it's something you've encountered also. Thank you.

[Soule]

Problem solved. Simplified code. Got library references vs. object methods sorted out. Thanks all.

Frank