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

Access CheckBox VB Code Help

P: 3
I have been tasked to create a 'simple' form in Access providing managers to input necessary changes. I have 2 command buttons on the form and a check box. Command button 1 updates my table for multiple entries, and command button 2 e-mails the table in HTML format to my team, appends the data entered to a back-up table, and deletes the entries on the current table.

When the check box on my form is checked (indicating a permanent change) I need command button 2 to run the same events, but also CC another distribution list and reword the email message. I have been looking all over the Internet for help and enlisted the help from other departments with no avail. This was supposed to be a simple form, but after feedback from other users it turned into something way beyond my knowledge of VB. I converted all my macros to VB, and am learning tons about writing code. However I am turning to you all for you assistance. Please help!!!

If chkPerm = True Then
docmd....

If chkPerm = False Then
docmd....

Below is the ACTUAL 'Event Procedure' code for Command button 2 that I thought would work, but after testing realized it didnt... It is a long code for 1 command button, but due to procedures and error handlers I didnt know any other way to write it...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Notify_Click()
  2. On Error GoTo Err_handler
  3. Perm = chkbox
  4. If chkPerm = False Then
  5.  DoCmd.SendObject acTable, "Skills", "HTML(*.html)", "SMTP:CMTDialerTeam@ROOT", "", "", "Skill Change Request", "Please update this associates skills.  ", False, ""
  6.     DoCmd.SetWarnings False
  7.     DoCmd.GoToRecord acForm, "Skill Change Request Form", acNewRec
  8.     Beep
  9.     MsgBox "Your e-mail was sent. You will be notified upon completion.", vbOKOnly, "Successful"
  10.     DoCmd.RunMacro "Make Back-Up", , ""
  11.  
  12.        If iResponse = vbOKOnly Then
  13. DoCmd.Quit
  14.     DoCmd.SetWarnings True
  15. Else
  16. End If
  17. End If
  18. If chkPerm = True Then
  19. DoCmd.SendObject acTable, "Skills", "HTML(*.html)", "SMTP:CMTDialerTeam@ROOT", "CMT_ResourceDesk@oomc.com", "", "Skill Change Request", "Please update this associates skills. CMT_ResourceDesk, pleae update the Voice List and Associate Database.    ", False, ""
  20.     DoCmd.SetWarnings False
  21.     DoCmd.GoToRecord acForm, "Skill Change Request Form", acNewRec
  22.     Beep
  23.     MsgBox "Your e-mail was sent. You will be notified upon completion.", vbOKOnly, "Successful"
  24.     DoCmd.RunMacro "Make Back-Up", , ""
  25.       If iResponse = vbOKOnly Then
  26. DoCmd.Quit
  27.     DoCmd.SetWarnings True
  28.     End If
  29. End If
  30.  
  31. 'error handler
  32. Err_handler:
  33.  
  34.  If Err.Number = 2293 Then
  35.    iResponse2 = MsgBox("Skill Changes were not sent to the CMT Dialer Team.  Do you want to try to send again?", vbYesNo, "E-Mail Not Sent")
  36.    'MsgBox "Skill change request not sent"
  37.     If iResponse2 = vbNo Then
  38.     Beep
  39.     MsgBox "Your changes were not saved, and Database will close automatically.", vbOKOnly, "Unsuccessful"
  40.     DoCmd.Quit
  41.     End If
  42.     If iResponse2 = vbYes Then
  43.     If chkPerm = False Then
  44.  DoCmd.SendObject acTable, "Skills", "HTML(*.html)", "SMTP:CMTDialerTeam@ROOT", "", "", "Skill Change Request", "Please update this associates skills.  ", False, ""
  45.     DoCmd.SetWarnings False
  46.     DoCmd.GoToRecord acForm, "Skill Change Request Form", acNewRec
  47.     Beep
  48.     MsgBox "Your e-mail was sent. You will be notified upon completion.", vbOKOnly, "Successful"
  49.     DoCmd.RunMacro "Make Back-Up", , ""
  50.  
  51.        If iResponse = vbOKOnly Then
  52. DoCmd.Quit
  53.     DoCmd.SetWarnings True
  54. Else
  55. End If
  56. End If
  57. If chkPerm = True Then
  58. DoCmd.SendObject acTable, "Skills", "HTML(*.html)", "SMTP:CMTDialerTeam@ROOT", "CMT_ResourceDesk@oomc.com", "", "Skill Change Request", "Please update this associates skills. CMT_ResourceDesk, pleae update the Voice List and Associate Database.    ", False, ""
  59.     DoCmd.SetWarnings False
  60.     DoCmd.GoToRecord acForm, "Skill Change Request Form", acNewRec
  61.     Beep
  62.     MsgBox "Your e-mail was sent. You will be notified upon completion.", vbOKOnly, "Successful"
  63.     DoCmd.RunMacro "Make Back-Up", , ""
  64.       If iResponse = vbOKOnly Then
  65. DoCmd.Quit
  66.     DoCmd.SetWarnings True
  67.     End If
  68. End If
  69.     Beep
  70.     MsgBox "Your e-mail was sent. You will be notified upon completion.", vbOKOnly, "Successful"
  71.         Else
  72.  
  73.     DoCmd.Quit
  74.         End If
  75.  End If
  76.  
  77.  
  78. End Sub
May 7 '09 #1
Share this Question
Share on Google+
9 Replies


beacon
100+
P: 579
Hi Chris,

I could be wrong, but can you not run another DoCmd.SendObject with the new message and distribution list after checking to see if the checkbox value is true or not?

Basically, Command2 is selected, check to see if checkbox is true/false. If checkbox = false then send to default group with default message. If checkbox = true then send it to a different group with a different message.

Actually, it looks like you have to separate conditional statements that check the status of the checkbox.
I've had code not work the way I expected by setting it up the way you have it:
Expand|Select|Wrap|Line Numbers
  1. if permCheck = true then
  2. docmd.
  3. end if
  4.  
  5. if permCheck = false then
  6. docmd.
  7. end if
  8.  
Perhaps combining them into
Expand|Select|Wrap|Line Numbers
  1. if permCheck = false then
  2. 'do stuff
  3. elseif permCheck = true then   'or this could just be else since the checkbox can only be T/F
  4. 'do same stuff, but also do some extra
  5.  
Hope this helps...
May 7 '09 #2

P: 3
I tried to combine them, but it still didnt work. Do I have to set the values or properties of the check box somewhere that I might have missed???
May 7 '09 #3

DonRayner
Expert 100+
P: 489
Check out Line 3 in your code. You have

Expand|Select|Wrap|Line Numbers
  1. perm = chkbox
but you are using chkperm in the remainder of your code.
May 7 '09 #4

JustJim
Expert 100+
P: 407
Part of the problem is that you have no Dim statements and no Option Explicit statement. Then you have variables such as Perm, chkbox, chkPerm, and in another post, permCheck. With no Option Explicit statement, any typos are simply accepted and Dim'd as type Variant.

Anyway, the sort of method that I think you need is:

Expand|Select|Wrap|Line Numbers
  1. Dim strMessageText as String
  2.  
  3. If Me.chkPerm then
  4.      strMessageText = "Text for message if check box is ticked"
  5.      DoCmd.SendObject acTable '** rest of arguments to include CC address
  6.                               '** and message text above
  7. else
  8.      strMessageText = "Text for message if check box is not ticked"
  9.      DoCmd.SendObject acTable '** rest of arguments to exclude CC address
  10.                               '** and message text above
  11. EndIf
  12.  
Let us know how you get along

Jim
May 7 '09 #5

DonRayner
Expert 100+
P: 489
It's not causing your problem but you also have 2 non required Else statements, 1 on line 15, the other on line 54
May 7 '09 #6

P: 3
Jim,
What your saying makes sense, but could you be a little more specific? Ultimalately I need the command button to do the same function with 1 minor change. If the box is checked I need to add an email address to the CC portion. Where would I put the Dim statement, and what recognizes if the box is checked or not?

Thanks to all of you for your assistance! This is a great site, and I'm glad I found it!
May 7 '09 #7

JustJim
Expert 100+
P: 407
@Chris Ahmsi
The check box is a control on your form and as such it has a name. If you haven't renamed it, it will be something like Check1. To find out its name, open the form in design view and double click on the check box. This should open up the properties sheet for the check box (if not, click on the Properties button on the toolbar, or click on View > Properties, or type alt-Enter). Now that you know it's name you can refer to it in code.

The easiest way is to refer to Me.Check1 (if that is its name). The Me. is a shortcut to refer to the form (in this case).

The line in my code that says
Expand|Select|Wrap|Line Numbers
  1. If Me.chkPerm then
is just a shorter way of saying "if the value of the check box called 'chkPerm' on this form is True, then execute the following code".

The Dim statements are normally put at the top of the Sub, or at least before you use the variable that it Dims.

The first time you go into the code editor, you should type 'Option Explicit' (without the single quotes). This will whooosh on up to the top and be valid for any other code you write. (Actually it's a good idea to have this as the default, but we'll go into that later).

Jim
May 7 '09 #8

JustJim
Expert 100+
P: 407
Oh and by the way, welcome to Bytes!
May 7 '09 #9

NeoPa
Expert Mod 15k+
P: 31,709
@JustJim
Hey, Why wait?

Here's one I made earlier ;)
It is always a good idea to ensure that variable name checking is enabled, AND your code compiles (at least compilation has been attempted), before submitting a question.

This avoids asking questions which are much more easily resolved on your own PC than on a forum.

To ensure variable name checking is enabled for all new modules, go to - Tools / Options / Editor (from the VBA Editor window) and set Require Variable Declaration to True (checked). For existing modules, ensure that the Option lines at the very top include :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
To compile your project, select (again from the VBA Editor window) Debug / Compile Project Name.

We ARE generally happy to help with compilation problems too (If you find an error reported and you can't resolve it, let us know), but we do expect members to have tried compiling before submitting a question. That way we have a better idea of the sort of problem we're looking at.
May 7 '09 #10

Post your reply

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