Access CheckBox VB Code Help

[Chris Ahmsi]

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...

Private Sub Notify_Click()
On Error GoTo Err_handler
Perm = chkbox
If chkPerm = False Then
 DoCmd.SendObject acTable, "Skills", "HTML(*.html)", "SMTP:CMTDialerTeam@ROOT", "", "", "Skill Change Request", "Please update this associates skills.  ", False, ""
    DoCmd.SetWarnings False
    DoCmd.GoToRecord acForm, "Skill Change Request Form", acNewRec
    Beep
    MsgBox "Your e-mail was sent. You will be notified upon completion.", vbOKOnly, "Successful"
    DoCmd.RunMacro "Make Back-Up", , ""

       If iResponse = vbOKOnly Then
DoCmd.Quit
    DoCmd.SetWarnings True
Else
End If
End If
If chkPerm = True Then
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, ""
    DoCmd.SetWarnings False
    DoCmd.GoToRecord acForm, "Skill Change Request Form", acNewRec
    Beep
    MsgBox "Your e-mail was sent. You will be notified upon completion.", vbOKOnly, "Successful"
    DoCmd.RunMacro "Make Back-Up", , ""
      If iResponse = vbOKOnly Then
DoCmd.Quit
    DoCmd.SetWarnings True
    End If
End If

'error handler
Err_handler:
 
 If Err.Number = 2293 Then
   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")
   'MsgBox "Skill change request not sent"
    If iResponse2 = vbNo Then
    Beep
    MsgBox "Your changes were not saved, and Database will close automatically.", vbOKOnly, "Unsuccessful"
    DoCmd.Quit
    End If
    If iResponse2 = vbYes Then
    If chkPerm = False Then
 DoCmd.SendObject acTable, "Skills", "HTML(*.html)", "SMTP:CMTDialerTeam@ROOT", "", "", "Skill Change Request", "Please update this associates skills.  ", False, ""
    DoCmd.SetWarnings False
    DoCmd.GoToRecord acForm, "Skill Change Request Form", acNewRec
    Beep
    MsgBox "Your e-mail was sent. You will be notified upon completion.", vbOKOnly, "Successful"
    DoCmd.RunMacro "Make Back-Up", , ""

       If iResponse = vbOKOnly Then
DoCmd.Quit
    DoCmd.SetWarnings True
Else
End If
End If
If chkPerm = True Then
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, ""
    DoCmd.SetWarnings False
    DoCmd.GoToRecord acForm, "Skill Change Request Form", acNewRec
    Beep
    MsgBox "Your e-mail was sent. You will be notified upon completion.", vbOKOnly, "Successful"
    DoCmd.RunMacro "Make Back-Up", , ""
      If iResponse = vbOKOnly Then
DoCmd.Quit
    DoCmd.SetWarnings True
    End If
End If
    Beep
    MsgBox "Your e-mail was sent. You will be notified upon completion.", vbOKOnly, "Successful"
        Else
    
    DoCmd.Quit
        End If
 End If


End Sub

[beacon]

Hi Chris,

I could be wrong, but can you not run another DoCmd.SendObjec t 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:

if permCheck = true then
docmd.
end if

if permCheck = false then
docmd.
end if

Perhaps combining them into

if permCheck = false then
'do stuff
elseif permCheck = true then   'or this could just be else since the checkbox can only be T/F
'do same stuff, but also do some extra

Hope this helps...

[Chris Ahmsi]

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???

[DonRayner]

Check out Line 3 in your code. You have

perm = chkbox

but you are using chkperm in the remainder of your code.

[JustJim]

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:

[CODE=vb]Dim strMessageText as String

If Me.chkPerm then
strMessageText = "Text for message if check box is ticked"
DoCmd.SendObjec t acTable '** rest of arguments to include CC address
'** and message text above
else
strMessageText = "Text for message if check box is not ticked"
DoCmd.SendObjec t acTable '** rest of arguments to exclude CC address
'** and message text above
EndIf
[/CODE]

Let us know how you get along

Jim

[DonRayner]

It's not causing your problem but you also have 2 non required Else statements, 1 on line 15, the other on line 54

[Chris Ahmsi]

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!

[JustJim]

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!

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

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

[JustJim]

Oh and by the way, welcome to Bytes!

[NeoPa]

(Actually it's a good idea to have this as the default, but we'll go into that later).

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 :

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.