Access: Form page visibility from field value (multiple criteria)

[arcaidius]

Good morning,

I have an Access form with multiple tabs or pages, on one field value is basically the department or position of an employee. There are 4 positions. I am trying to make certain pages visible based on the position selected.

I have tried a few different versions of the below code in visual basic for the form. It works if there is only one IF and Else, but once I tried it with 4 different criteria it doesn't work. I am guessing it reads the first few lines and then throws the debug when executing the next IF.

Here is my code:

Private Sub Form_Current()

    'If Department = "QC" Then
        'Page5.Visible = False
        'Page6.Visible = False
    'Else
        'End If
    
   'If Department = "QC R/F" Then
        'Page1.Visible = False
        'Page2.Visible = False
        'Page3.Visible = False
        'Page4.Visible = False
        'Page6.Visible = False
    'Else
        'End If
   
    'If Department = "QA" Then
        'Page1.Visible = False
        'Page2.Visible = False
        'Page3.Visible = False
        'Page4.Visible = False
        'Page5.Visible = False
    'Else
        'End If
        
    'If Department = "DC" Then
        'Page1.Visible = False
        'Page2.Visible = False
        'Page3.Visible = False
        'Page4.Visible = False
        'Page5.Visible = False
        'Else
    'End If
    
End Sub

 

[Micron]

Unfortunately that doesn't cover the when? However, if I assume this is a single record viewing form or even a continuous form, the following may be enough for when navigating through records. I also have to assume that no pages should show when navigating. Also assuming there is navigation going on, but if not, should not matter if using Current event as that will fire when form finishes loading record(s) anyway.

Private Sub Form_Current()
Dim pge As Page

For Each pge in Me.TabCtl28.Pages
  pge.Visible = False
Next

Select Case Me.Department

   Case "QC"
     With Me.TabCtl28
        .Pages(0).Visible = True
        .Pages(1).Visible = True
        .Pages(2).Visible = True
        .Pages(3).Visible = True
     End With
   
   Case "QC R/F"
     Me.TabCtl28.Pages(4).Visible = True
       
    Case "QA", "DC"
      Me.TabCtl28.Pages(5).Visible = True

End Select
End Sub

What's possibly more important is are the Case statement tests evaluating the values you think they are when it doesn't work as expected. You should put a break point on whatever procedure you decide to use, step through the code line by line and when the Case is being evaluated, mouse over Me.Department and see what it holds. If it is the text you expect, it should properly evaluate. If it is a number, then something about the code that opens this form is wrong, or your Case values are wrong.
EDIT - you might have to move focus to a specific control if the form loads with any page having the focus.

 

[arcaidius]

Almost there, It works but page 1 is always visible, do i need a cover page maybe?

 

[arcaidius]

Almost there, It works but page 1 is always visible, do i need a cover page maybe?

sorry was replying to Joe4 latest, slight delay in loading i guess.

 

[arcaidius]

Just for kicks, what happens when you try this:

Private Sub Form_Current()

Page1.Visible = True
Page2.Visible = False
Page3.Visible = False
Page4.Visible = False
Page5.Visible = False
Page6.Visible = False

Select Case Me.Department

   Case "QC"
        Page1.Visible = True
        Page2.Visible = True
        Page3.Visible = True
        Page4.Visible = True
   Case "QC R/F"
        Page5.Visible = True
    Case "QA"
        Page6.Visible = True
    Case "DC"
        Page6.Visible = True

End Select

End Sub

This one works, but page one is always visible. I changed it to false on the first line and got an error. I'm assuming page one has this focus thing going on Micron mentioned?
Will it mess up my form if I change the focus to just a random cover page with no actual data on it?

 

[Micron]

Why not choose another object rather than defeat the problem with an otherwise useless page? Why not set focus to Me.Department or something else?

"but I got an error" is of no help at all. It never occurred to you that you should report the error number and text in order to provide some sort of clue?

 

[arcaidius]

I am sorry lol, I know I must be frustrating. That did the trick so Thank you very much. Both codes work, but since Micro helped me set up the focus I'm giving you credit, Here is both final codes, both work:

Thank you guys, I learned a lot today.

Joe4 code

Private Sub Form_Current()

Me.Department.SetFocus

Page1.Visible = False
Page2.Visible = False
Page3.Visible = False
Page4.Visible = False
Page5.Visible = False
Page6.Visible = False

Select Case Me.Department

   Case "QC"
        Page1.Visible = True
        Page2.Visible = True
        Page3.Visible = True
        Page4.Visible = True
   Case "QC R/F"
        Page5.Visible = True
    Case "QA"
        Page6.Visible = True
    Case "DC"
        Page6.Visible = True

End Select

End Sub

Micron's code:

Private Sub Form_Current()

Me.Department.SetFocus

Dim pge As Page

For Each pge In Me.TabCtl28.Pages
  pge.Visible = False
Next

Select Case Me.Department

   Case "QC"
     With Me.TabCtl28
        .Pages(0).Visible = True
        .Pages(1).Visible = True
        .Pages(2).Visible = True
        .Pages(3).Visible = True
     End With
   
   Case "QC R/F"
     Me.TabCtl28.Pages(4).Visible = True
       
    Case "QA", "DC"
      Me.TabCtl28.Pages(5).Visible = True

End Select
End Sub

 

[Joe4]

You are welcome, glad we could help.
Sorry I was off-line for a while. We had a severe storm that knocked out power here for about 12 hours.