By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,663 Members | 1,791 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,663 IT Pros & Developers. It's quick & easy.

Help with Button Automation Code

P: 34
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.
------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. Private Sub AS1_Form_Re_send_Welcome_E_Mail_Only_Button_Click()
  2. ' 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.
  3. ' Used GetObject method rather than CreateItemFromTemplate method for simplicity.
  4. ' Namespace/MAPI commands not used as some employees (i.e. Animation) are on mail client other than Outlook.
  5. ' 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...
  6.  
  7. DoCmd.RunCommand acCmdSaveRecord
  8.  
  9. ' Now, the automation begins. Declaration statement(s)...
  10. Dim objOutlookMsg As Object
  11. ' This next line opens Outlook by retrieving employee welcome e-mail template...
  12. ' ,Class needed in pathname or not?
  13.  
  14. objOutlookMsg = GetObject("J:\Special Projects\Database Work\AS1 Tracking DB & Related\AS1 Form Button Automation Email\Employee AS1 Welcome Outlook Template.oft")
  15.  
  16. ' 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.
  17.  
  18. objOutlookMsg.To = Replace("<<Known As>>", "<<Known As>>", "[AS1 Onboarding Tracking Table]![Known As]")
  19. If IsNull("[AS1 Onboarding Tracking Table]![Known As]") Then
  20. ReplaceNull = ("[AS1 Onboarding Tracking Table]![First Name]")
  21. End If
  22.  
  23. ' This next line populates the mail's "From" line from data in the "HR EOD Contact Name" form control.
  24.  
  25. objOutlookMsg.From = ("[AS1 Onboarding Tracking Table]![HR EOD Contact Name]")
  26.  
  27. ' These next lines auto-fill the mail subject with boilerplate...
  28.  
  29. objOutlookMsg.Subject = "Congratulations and Welcome To XXXXXXXXX!"
  30.  
  31. ' Body is almost all boilerplate (only HR EOD contact and movie code sections need populating).
  32.  
  33. objOutlookMsg.BodyFormat = olFormatRichText
  34. objOutlookMsg.Body = Replace("<<HR EOD Contact Name>>", "<<HR EOD Contact Name>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Name]")
  35. objOutlookMsg.Body = Replace("<<HR EOD Contact Internal Phone #>>", "<<HR EOD Contact Internal Phone #>>", "[AS1 Onboarding Tracking Table]![HR EOD Contact Internal Phone #]")
  36. 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]")
  37. Exit Sub
  38.  
  39. ' These next lines copy the top movie code (sorted ascending) from "Unused Movie Code Table" and populate "Movie Code" control on form.
  40.  
  41. Dim db As Database
  42. Dim rs As Recordset
  43. Set db = CurrentDb
  44. 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")
  45. rs![Movie Code] = Me.[Movie Code]
  46. ‘ Is this line needed in this case?
  47. rs.Update
  48.  
  49. ' This next line then moves (not copies) that movie code from "Unused Movie Code Table" to "Used Movie Code Table".
  50.  
  51. 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"
  52. rs.Close
  53. Set rs = Nothing
  54. Set db = Nothing
  55.  
  56. Exit Sub
  57.  
  58. ' This next line populates <<movie code>> text on template with "Movie Code" control's data on form.
  59.  
  60. objOutlookMsg.Body = Replace("<<movie code>>", "<<movie code>>", "[AS1 Onboarding Tracking Table]![movie code]")
  61. Exit Sub
  62.  
  63. ' This next line sends the e-mail. User can verify it was sent in mail account's "Sent Items" box.
  64.  
  65. objOutlookMsg.Send
  66. Set objOutlookMsg = Nothing
  67. End Sub
  68.  
--------------------------------------
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
Jan 10 '12 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,712
Frank, let me start by saying I think you're going about this the wrong way (in some respects). I'll give you first a couple of links that should help. They also indicate how you should do things on here, but you will also find them very helpful if you read them that way (When Posting (VBA or SQL) Code & Debugging in VBA). I've already changed the title for you to one that's more appropriate for a forum question.

The next thing I would say, and it's related to debugging, is that you are making your life more difficult/complicated by jumping in at such a complicated level. I find the development process works much more smoothly when I start with smaller building blocks and make my way to the more complicated stuff when I know I have some already reliable parts to work with. That way any problems are describable very simply and only one small piece of code is generally required for review.

What you have here would daunt many experts. We're generally happy to help, but I'm sure you will appreciate that the amount of effort involed in something as complex as this would not scale up very well (IE. We would only ever cover a small percentage of questions between us if we allowed so much of our time to be devoted to a single question).

I'm not going to delete this, as you've clearly put a lot of effort into expressing it as well as you can, but I can't imagine too many will want to get so tied up in something as involved as this. You may want to consider a fresh approach - My advice - break-down and simplify - remembering that each thread should really cover a single question/discussion only.
Jan 11 '12 #2

P: 34
Hi, NeoPa,

Thanks for the links to the posting conventions. I read everything and immediately added more Debug.print error stops to my code. Much appreciated.

I wish I could have started writing in VBA at a smaller scale, but I am in a professional situation with a large company. I gave them the 2 Db's they wanted, but they asked for bells/whistles automated e-mail buttons on their main form "if I could". This is a great opportunity to expand my knowledge of Access in a big way. Unfortunately, the situation is like trying to learn how to drive on the Indy 500 track. A true trial by fire.

I didn't think my code was too long, so I decided to put it all up in one place to get feedback on any obvious errors/methods. I will take your advice on the two parts I have that are still problematic and part them out as short questions/posts.

Thanks for the heads up on "using the right bait" on Bytes.com. Appreciated.

Frank
Jan 12 '12 #3

NeoPa
Expert Mod 15k+
P: 31,712
Frank:
Thanks for the heads up on "using the right bait" on Bytes.com. Appreciated.
That's a clever turn of phrase. Well put.

As for the code already being too complex - just break down the bits that are at issue. Play with example code until you understand the concept well, then apply it back into your more complex project. The important thing is to keep the learning process simple. It sounds like you already have quite a good handle on it to be fair :-)
Jan 12 '12 #4

P: 34
Thanks. See you in the E-mergency room.
Jan 12 '12 #5

NeoPa
Expert Mod 15k+
P: 31,712
Lol. That's either a pun on e-everything related to electronic, or it's gone over my head. I suspect one of us probably deserves to withdraw with head hung low and face showing shame - I'm just not sure which at this point :-D

Now I've said that I hope it was just a poor pun.
Jan 12 '12 #6

P: 34
It is just a pun on catastrophic outcomes of using everything and anything electronic. Unfortunately, my distaste for hanging my head in shame and defeat is the exact thing that will put me there. :P
Jan 12 '12 #7

NeoPa
Expert Mod 15k+
P: 31,712
I call that borderline and I certainly missed it, so I think I have to take the hit on that one. Never mind. My skin's thick and calloused by now.
Jan 12 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.