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

 

[arcaidius]

That's when I get the error. It has been throwing an error "The setting value value you entered isn't valid for this property". When I click on the deBug it highlights on the lines before the select Case code.
So I tried saving and closing this time and it says Run-time error 2165: I can't hide a control tab that has the focus. Then did the same as before after I click Debug.

I tried this

Pages(1 - 6).Visible = False

but doesn't work either.

But if I just activate one page as not visible then are no errors.

So in the header is the department and the employee name and a search box to search employees. The pages would be where data is entered.

 

[Joe4]

It has been many years since I have worked with multiple pages/tabs on a Form in Access, so I am not sure how you need to reference it, and I do not have access to Access at the moment, but I came across this thread, which may be helpful: Making tab control pages invisible or disabled

 

[Micron]

Aside from being all commented out, which means your code will do nothing at all, your page reference is akin to writing the street address on an envelope without including the city or country. In other words, the parent objects of the page are not referenced at all. Also, you cannot use a page span as a reference. More like

   Case "QC"
     With Me.NameOfTabControlHere
        .Pages(0).Visible = True
        .Pages(1).Visible = True
        .Pages(2).Visible = True
        .Pages(3).Visible = True
     End With

If you wish to use the actual page names rather than the zero based index, change .Pages(0) to .Pages("Page1") etc.
Note that the reference is now complete, with Me (the form), the name of the tab control (the first born child of the form) and then the pages collection, then the index number or page name - four members in all, in the correct order of their hierarchy. Obviously you must use the name of your tab control, not what I wrote.

 

[arcaidius]

Micron, Hello! I do know about the comment thing LOL, I just pasted the code after messing around with it for a while.

So here is my code now, and it is hiding every page except (Index page# 5 & 6) on load and after I change the value. I also had the Private Sub Form_AfterUpdate() code on the Private Sub Department_AfterUpdate() and it didn't make a flip of difference. I feel like I'm almost there....

Private Sub Form_Load()

With Me.TabCtl28
.Pages(4).Visible = False
.Pages(5).Visible = False
End With

End Sub

Private Sub Form_AfterUpdate()

With Me.TabCtl28
.Pages(0).Visible = False
.Pages(1).Visible = False
.Pages(2).Visible = False
.Pages(3).Visible = False
.Pages(4).Visible = False
.Pages(5).Visible = False
End With


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"
    With Me.TabCtl28
        .Pages(4).Visible = True
    End With
        
    Case "QA"
      With Me.TabCtl28
        .Pages(5).Visible = True
      End With
        
    Case "DC"
      With Me.TabCtl28
        .Pages(5).Visible = True
      End With

End Select
End Sub

 

[Joe4]

Note that this part:

    Case "QA"
      With Me.TabCtl28
        .Pages(5).Visible = True
      End With
        
    Case "DC"
      With Me.TabCtl28
        .Pages(5).Visible = True
      End With

can be combined:

    Case "QA", "DC"
      With Me.TabCtl28
        .Pages(5).Visible = True
      End With

 

[arcaidius]

Thanks for that, so the only part of the code that is working is

Private Sub Form_Load()

With Me.TabCtl28
.Pages(4).Visible = False
.Pages(5).Visible = False
End With

End Sub

The rest is not working at all. The first 4 pages are always visible.

 

[Joe4]

The rest is not working at all. The first 4 pages are always visible.

Is that how you have your code currently written?
There are no conditions are any of that, so it does not tell us much of anything.

Are you sure you have the right field name, and it does not have a numeric value?
What does this return?

MsgBox Me.Department

 

[arcaidius]

Sorry, took me a minute, It says: QC
Which is the current value in Department.
And yes that is the entire code, I am not an expert on macros in access. It does seem like I am not identifying any parameters in the beginning, I'm better at excel, but everything is so alien when switching to access. If you can help I will greatly appreciate it.

 

[Micron]

I have some catching up to do. In the meantime, if you could describe exactly what it is you want to have happen and when and forget about what isn't working, it would really help to solve this. F'rinstance this

it is hiding every page except (Index page# 5 & 6) on load and after I change the value.

doesn't tell anyone IF that is what you want to happen. If you're wanting to hide all the pages on Current or Load events why not just hide the whole control? I suspect the AfterUpdate event of the form is of no help here either, but the lack of clarity (at least for me) prevents a conclusion on that. Current event of the form is probably all that is needed. Also, for one condition only, a With block is overkill.

 

[arcaidius]

OK, what it is:
The form header has employee name, ID# QC# and department. There is 4 different departments. And a search box to search records by Employee name.
There are 6 pages with subforms on them.

What I want it to do is show specific pages based on the department field.
QC should show pages 0-3 (Index#)
QC R/F should show page 4
QA and DC should show page 5

The person using the form shouldn't be changing the value in department field, this changes when you search and select a different employee.