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

 

[Joe4]

Rather than having multiple IF statements, since you are only checking one field ("Department"), try using a "Case" statement instead.
See: MS Access: Case Statement

How I usually do these is have code at the top of my code that first hides ALL sheets.
Then, have the "Case" statement unhide whatever sheets need to be unhidden, based on the value.

 

[arcaidius]

I am sorry, I am a complete noob, and cannot figure out how to write the code. could you give me an example of the beginning? I tried like this and keep getting errors.

Select Case test_expression

Case condition_1
    [Department] = "QC"
       result_1
        Page1.Visible = True
        Page2.Visible = True
        Page3.Visible = True
        Page4.Visible = True
Case condition_2
    Department = "QC R/F"
        result_2
        Page5.Visible = True

End Select

 

[Joe4]

Try this:

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

End Select

 

[arcaidius]

And this should be on (General) (Declarations) ?

 

[Joe4]

And this should be on (General) (Declarations) ?

I am sorry, I do not understand what you are asking with this.

 

[arcaidius]

I was asking if the VBA should be on the form directly as it is, at the top in visual basic I have the two tabs selected as General and declarations, where i could change it to form and current or something.

Also is there a hide all pages code, I am searching but its not working. If I put each page .visible = false I get an error on the second page.

 

[Joe4]

The code needs to be contained within a procedure, like you had previously.
Then you need to determine how/when it should run.

Note that you may have to keep at least one page of your forum open (or maybe at least the page that this Department field appears on).

 

[arcaidius]

The Department field is in the Header.

I now have this code on the current form and get an error. If I change the code to only hide one page then there is no errors, but then the rest of the pages are visible.
I think I just need a code like Allpages.visible = False. Is there something like that?

Private Sub Form_Current()

'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

 

[Joe4]

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