Help creating variables (1 Viewer)

[bwc]

I have so many if...then statements that I am getting lost in my own code. In my attached file, I have 6 toggle buttons that make subforms visible=true and visible=false when clicked. All buttons work as expected when all toggles are visible, but not when only some toggles are visible. If you look at my code, you will see that I made this work with if...than statements, but I would think that it can be simplified with variables. I don’t know how to create and use variables. Thank you, Bryan

 

[CJ_London]

You would propably be better off putting these on tabs or alternatively

If you want a bit of a formula, trying adapting the following

topofcontrol=previouscontrol.top+previouscontrol.height*abs(previouscontrol.visible)+60 '(60 twips spacer)

which will get rid of 'if visible then else'

 

[bwc]

Thanks for your reply CJ. I am new to VBA, so how would i use your code?
Thanks, Bryan

 

[CJ_London]

I have provided a coding suggestion as a way to cut down your nested 'if' statements

so instead of saying

if control.visible then
    anothercontrol.top=somevalue
    etc.....

you would use

control2.top=control1.top+control1.height*abs(control1.visible)+60 '(60 twips spacer)
control3.top=control2.top+control2.height*abs(control2.visible)+60 '(60 twips spacer)
etc

Where control1 is the top control, control2 is the next control down etc

- I would put your subforms and buttons in the right 'order'

Also, rather than making the subforms not visible, just change the height to 0 - providing they don't have a border they will effectively become not visible

 

[bwc]

Thank you for the reply, CJ. But I am afraid that I am still not getting it. I guessed that "control1" is the top most control, "control2" is is next...etc. So i replaced "control1" with tglLOD, "control2" with tglTemp...etc, but i end up with the controls stacked on one another! What am i doing wrong? Bryan

 

[CJ_London]

I've attached a small db to demonstrate what I mean

 

[bwc]

thanks for your help, CJ. i do learn much easier by example.
i also tried to set the "c" height to '0' to hide the control when my table field is null, but i haven't figured that out yet. right now i am using the visible control to hide -- but the white space is still on the form between "c1" and "c3" when "c2" visible = false

thanks again, Bryan

 

[CJ_London]

Don't use visible - set height to 0 instead

 

[bwc]

would the code be - "If Not IsNull(Me!lodstrSSN) Then Me!tgl1.Height = .3 * 1440 Else Me!tgl1.Height = 0" ? when i use this code i get control overlap. i have attached a screen shot

 

[CJ_London]

what is your full code? The bit you posted will set the height of Me!tgl1 to .3*1440 or 432 - but what about top and all the other control tops and heights?

And I see you are back to using if!

A neater formula would be:

Me!tgl1.Height = .3 * 1440 *abs(not isnull(Me!lodstrSSN))

 

[bwc]

And I see you are back to using if!

the if tests the table field

the code is

Option Explicit
 
Dim  tglht As Double, lftPos As Double
Private Sub Form_Load()
    ' 1440 twips = 1 inch; multiply twips x inch
    tglht = 0.3 * 1440
    lftPos = 0.2 * 1440
Dim i As Integer
    For i = 1 To 6
        Me("s" & i).Height = 0
    Next i
End Sub
Public Sub Form_Current()
Dim i As Integer
Dim CtrlTop As Integer
    CtrlTop = tglht
 
    For i = 1 To 6
 
        Me("tgl" & i).Left = lftPos
        Me("tgl" & i).Top = CtrlTop
        CtrlTop = CtrlTop + Me("tgl" & i).Height + 60
 
        Me("s" & i).Left = lftPos
        Me("s" & i).Top = CtrlTop
        CtrlTop = CtrlTop + Me("s" & i).Height + 60
 
    Next i
 
    'On current event of main form Check  for LOD then show or hide toggle
    If Not IsNull(Me!lodstrSSN) Then Me!tgl1.Height = 0.3 * 1440 Else Me!tgl1.Height = 0
    'On current event of main form Check for TEMP profile then show or hide toggle
    If Not IsNull(Me!tempstrSSN) Then Me!tgl2.Height = 0.3 * 1440 Else Me!tgl2.Height = 0
    'On current event of main form Check for PERM profile then show or hide toggle
    If Not IsNull(Me!permstrSSN) Then Me!tgl3.Height = 0.3 * 1440 Else Me!tgl3.Height = 0
    'On current event of main form Check for MEB then show or hide toggle
    If Not IsNull(Me!mebstrSSN) Then Me!tgl4.Height = 0.3 * 1440 Else Me!tgl4.Height = 0
    'On current event of main form Check for MMRB then show or hide toggle
   If Not IsNull(Me!mmrbstrSSN) Then Me!tgl5.Height = 0.3 * 1440 Else Me!tgl5.Height = 0
    'On current event of main form Check for INCAP then show or hide toggle
    If Not IsNull(Me!incstrSSN) Then Me!tgl6.Height = 0.3 * 1440 Else Me!tgl6.Height = 0
End Sub
Private Sub tgl1_Click() ' lod
    If s1.Height = 0 Then s1.Height = Me!s1.Form.Detail.Height + tglht Else s1.Height = 0
    Form_Current
End Sub
Private Sub tgl2_Click() ' temp
    If s2.Height = 0 Then s2.Height = Me!s2.Form.Detail.Height + tglht Else s2.Height = 0
    Form_Current
End Sub
Private Sub tgl3_Click() ' perm
    If s3.Height = 0 Then s3.Height = Me!s3.Form.Detail.Height + tglht Else s3.Height = 0
    Form_Current
End Sub
Private Sub tgl4_Click() ' meb
    If s4.Height = 0 Then s4.Height = Me!s4.Form.Detail.Height + tglht Else s4.Height = 0
    Form_Current
End Sub
Private Sub tgl5_Click() ' mmrb
    If s5.Height = 0 Then s5.Height = Me!s5.Form.Detail.Height + tglht Else s5.Height = 0
    Form_Current
End Sub
Private Sub tgl6_Click() ' incap
    If s6.Height = 0 Then s6.Height = Me!s6.Form.Detail.Height + tglht Else s6.Height = 0
    Form_Current
End Sub

and then i have an unbound combo box (cboSoldierName) that calls this function on change;

Public Function hideSub()
' close forms when search for new Soldier
    Forms!frmOSStracker.tgl1 = 0
    Forms!frmOSStracker.s1.Height = 0
    Forms!frmOSStracker.tgl2 = 0
    Forms!frmOSStracker.s2.Height = 0
    Forms!frmOSStracker.tgl3 = 0
    Forms!frmOSStracker.s3.Height = 0
    Forms!frmOSStracker.tgl4 = 0
    Forms!frmOSStracker.s4.Height = 0
    Forms!frmOSStracker.tgl5 = 0
    Forms!frmOSStracker.s5.Height = 0
    Forms!frmOSStracker.tgl6 = 0
    Forms!frmOSStracker.s6.Height = 0
End Function

 

[CJ_London]

I think you need to swap this code around - like this

'On current event of main form Check  for LOD then show or hide toggle
    If Not IsNull(Me!lodstrSSN) Then Me!tgl1.Height = 0.3 * 1440 Else Me!tgl1.Height = 0
    'On current event of main form Check for TEMP profile then show or hide toggle
    If Not IsNull(Me!tempstrSSN) Then Me!tgl2.Height = 0.3 * 1440 Else Me!tgl2.Height = 0
    'On current event of main form Check for PERM profile then show or hide toggle
    If Not IsNull(Me!permstrSSN) Then Me!tgl3.Height = 0.3 * 1440 Else Me!tgl3.Height = 0
    'On current event of main form Check for MEB then show or hide toggle
    If Not IsNull(Me!mebstrSSN) Then Me!tgl4.Height = 0.3 * 1440 Else Me!tgl4.Height = 0
    'On current event of main form Check for MMRB then show or hide toggle
   If Not IsNull(Me!mmrbstrSSN) Then Me!tgl5.Height = 0.3 * 1440 Else Me!tgl5.Height = 0
    'On current event of main form Check for INCAP then show or hide toggle
    If Not IsNull(Me!incstrSSN) Then Me!tgl6.Height = 0.3 * 1440 Else Me!tgl6.Height = 0

 
For i = 1 To 6
 
        Me("tgl" & i).Left = lftPos
        Me("tgl" & i).Top = CtrlTop
        CtrlTop = CtrlTop + Me("tgl" & i).Height + 60
 
        Me("s" & i).Left = lftPos
        Me("s" & i).Top = CtrlTop
        CtrlTop = CtrlTop + Me("s" & i).Height + 60
 
    Next i

 

[bwc]

thanks CJ. this project is looking match cleaner with your help. i also replaced the if statements with abs. Bryan