visual basic and check boxes

[hoda64]

Hi everyone,

I am working on a database as a student intern and I am gradually learning access and VBA as I go along, so put simply, I am no expert.

Currently, I have a form which displays 5 check boxes. Depending on information from a previous form, some of these check boxes may dissapear because they are not needed as an option. Depending on the user, the visible check boxes can be clicked or unclicked and that will update the information on that previous form.

This is some of my code so far:

If Forms!SchedView!SchedStatus = "Approved" Then 'this is the first form
    If chkPM.Value = 0 Then 'check box on second form is false
        MsgBox " the PM has not approved", vbOKOnly
    
        Forms!SchedView!SchedStatus = "Unapproved-PM" 'update first form
    End If
If chkLB.Value = 0 Then
        MsgBox " the LB has not approved", vbOKOnly
        Forms!SchedView!SchedStatus = "Unapproved-LB"
    End If
    
    If chkSMMB.Value = 0 And chkCSDAB.Value = 1 And chkTCB.Value = 1 Then
        Forms!SchedView!SchedStatus = "Unapproved-SMMB"
    End If
    
    If chkCSDAB.Value = 0 And chkSMMB.Value = 1 And chkTCB.Value = 1 Then
        Forms!SchedView!SchedStatus = "Unapproved-CSDAB"
    End If
    
    If chkTCB.Value = 0 And chkSMMB.Value = 1 And chkCSDAB.Value = 1 Then
        Forms!SchedView!SchedStatus = "Unapproved-TCB"
    End If
'...(more conditional statements that are similar conceptually)
End If

My question is if the check box is no longer visible but is called in a conditional statement, how does VB interpret that?

I will appreciate any guidance, thanks

 

[MaliciousMike]

You could use:

If checkbox.visible Then

This'll mean it will only do something if the checkbox is visible.
The same things works with "enabled" and other things.

 

[hoda64]

Thanks for the quick response.

So if my if statement is currently:

If chkSMMB.Value = 0 And chkCSDAB.Value = 1 And chkTCB.Value = 1 Then

Would I change it to:

If chkSMMB.Value = 0 AND chkSMMB.visible = True And chkCSDAB.Value = 1 And chkCSDAB.Visible = True AND chkTCB.Value = 1 AND chkTCB.Visible = True Then

I have not actually typed that code into VBA yet

 

[MarkK]

The visibility of a control has no impact on its value. The values of non-visible controls may still partcipate in VBA expressions.

Of greater significance in the code you posted: be aware that True <> 1 in VBA. True = -1.

But using boolean values in an if statement doesn't require that you compare each boolean value to another boolean value for instance this...

If chkSMMB.Value = 0 And chkCSDAB.Value = 1 And chkTCB.Value = 1 Then
...might more simply and clearly be written...
If Not chkSMMB And chkCSDAB And chkTCB Then

 

[MarkK]

For readability, if the logic being applied differs based on the visibility of controls, I'd consider having that be the top level if statement in the block, like...

if SomeControl.Visible then
  if chkValue1 and chkValue2 then
    'statements
  elseif ....
    'statements
  else
    'statements
  end if
else
  'statements
end if

This kind of business logic is the most common thing that'll change in code you write, so make sure you, or some future programmer has an easy job.

 

[hoda64]

Thank you for your help.

The only control whose visibility changes are those checkboxes. But that is a more efficient way of coding it.

So this is what I am understanding from the simpler version of the code that you provided: .value is no longer needed because I do not need to compare the boolean values since there is the NOT there, and .visible is not needed because a non-visible control is interpreted as being false?

 

[MarkK]

- .Value is the default property of the control, and therefore need not be expressed in code. Some people argue it should be expressed for clarity. Your call.
- The .Value and .Visible property of a control are not related in any way. If you consume the .Value property of control in an expression, then changing the .Visible property will have no impact on the outcome of the expression.

 

[hoda64]

So here is a chunk of my code:

    If chkCSDAB.Visible = False Then
        If chkSMMB.Value = 0 And chkTCB.Value = -1 Then
            Forms!SchedView!SchedStatus = "Unapproved-SMMB"
        End If
        If chkSMMB.Value = -1 And chkTCB.Value = 0 Then
            Forms!SchedView!SchedStatus = "Unapproved-TCB"
        End If
    End If

I have my top level if statement being the visibility as you suggested. Because I am using the .value, does that mean that the .visible expression is pointless?

Also, I am a little bit confused with the NOT expression. Would I be allowed to subsitute the "chk.value = 0" with "not chk" and the "chk.value = -1" with "chk" in my if statements?

So if something is:

If chkTCB.Value = 0 And chkSMMB.Value = 0 And chkCSDAB.Value = 1 Then

Could I write it as

If Not chkTCB And Not chkSMMB And chkCSDAB Then

Thank you for taking the time to answer my never ending questions

 

[MarkK]

I would write it like this...

If Not chkCSDAB.Visible Then
  If chkTCB And Not chkSMMB Then
    Forms!SchedView!SchedStatus = "Unapproved-SMMB"
  ElseIf chkSMMB And Not chkTCB Then
    Forms!SchedView!SchedStatus = "Unapproved-TCB"
  End If
End If

 

[!Blue]

Or for a one liner you could try...

If Not chkCSDAB.Visible And (chkTCB Xor chkSMMB) Then _
 Forms!SchedView!SchedStatus = IIf(chkSMMB,"Unapproved-TCB","Unapproved-SMMB")