How can i make or not make particular Tabs visible by loading a form in Access with VBA?

Question

Question

Explanation:

I have a Acess Database with 4 forms containing buttons. Today i started to create ribbons to get rid of those in the forms so everything is sorted and easy to overview. I want the Tabs in my Access Database to be unvisible until i open a form from my main form.

Main Form (no tabs showed) --> Switching to another form by clicking a button in my main Form (now i want to show a particular tab after opening the form)

I created my Ribbons with "Ribbon Creator 2019" for Office 2019.

Problem:

I cant solve it ... i tried so many things until i found a excel worksheet having a function by swithing sheets to display particular tabs. Its exactly what i want but i can't get it to work for my access.

By getting the name of the active form i cant get it to work and in my opinion this would be tha fastest way.

My approach:

I set "StartFromScratch" in my XML to 'true' and gave my tabs names like this: "CustomTagValue1:=xstart".

Code for my tabs (Module):

Sub GetVisible(control As IRibbonControl, ByRef visible)
    ' Callbackname in XML File "getVisible"

    ' To set the property "visible" to a Ribbon Control
    ' For further information see: http://www.accessribbon.de/en/index.php?Downloads:12
    ' Setzen der Visible Eigenschaft eines Ribbon Controls
    ' Weitere Informationen: http://www.accessribbon.de/index.php?Downloads:12

    Select Case control.ID

            Case "tab_3"
            ' Tab:   tab_3
            visible = False

            Case "tab0"
            ' Tab:   tab0
            visible = False

            Case "tab1"
            ' Tab:   tab1
            visible = False


        Case Else
            visible = True

    End Select

End Sub

Code from another Module to declare my tabs:

Option Compare Database
Option Explicit

'**************************************************************************
'                      About this Code:
'
' This Code checks if a Formular is in active use by his 'Name'. Simple.
'**************************************************************************


Dim MyTag As String

'Callback for customUI.onLoad
Sub RibbonOnLoad(ribbon As IRibbonUI)
    Set Rib = ribbon
End Sub

Sub GetVisible(control As IRibbonControl, ByRef visible)
    If control.Tag Like MyTag Then
        visible = True
    Else
        visible = False
    End If
End Sub

Sub RefreshRibbon(Tag As String)
    MyTag = Tag
    If Rib Is Nothing Then
        MsgBox "Fehler RBC1018, bitte starten Sie das Programm neu."
    Else
        Rib.Invalidate
    End If
End Sub

Code in the Form onLoad:

Private Sub Form_Load(ByVal Sh As Object)

    Select Case Screen.ActiveForm
    Case "frmVerteiler": Call RefreshRibbon(Tag:="xverteiler")
    Case Else: Call RefreshRibbon(Tag:="")
    End Select


End Sub

Help:

I want it to work as in this excel Dokument: https://www.rondebruin.nl/win/s2/win012.htm

Albert D. Kallal · Accepted Answer

It is far less work and significant more easy to just create a custom ribbon for each form, and that way you don't have to write all kinds of code to hide show tabs on the ribbion. So just specify the correct ribbon for the given form, and no code is required.

I also suggest that you do NOT use call backs in the ribbon. If you adopt this approach, then the ribbon can directly call your EXISTING button code. So the code, buttons you now have can be transferred to the ribbon for that form, and you don't have to setup ribbon call backs, and all of your existing button code can remain "as is" and be called directly from the ribbon - and the ribbon will run the button code that is and remains in the form.

All you need to do is to declare any function you want called as a public function.

You then set the on action as follows:

=MyPublicFunctionName()

Note CAREFULL how we have = and () (you must have these), and they must be under the quotes.

Eg for the xml we have:

onAction="=MyDelete()"

Note how the above is DIFFERENT then a call back for a ribbon (and callbacks use sub, where this is a function). Even better is the above means you do NOT have to place the code in a standard code module, but can place the function it in the current form. So, no macro needed, no callback, and the code can go in the current form (just like it does for a command button). And bottoms to dollar, in 9 out of 10 cases, the code you need for particular form and button belongs in that particular forms code module anyway. In fact it's a very bad programming practice to start taking code that belongs in a form and placing that code in a standard public code module. The reason is for many, but all kinds of issues can crop up if you have multiple instances of that same form which is allowed in access. Furthermore when copying forms between applications, or even making a copy of the form within the same application means that you have outside code dependencies that we normally as access developers do not expect (we assume for the most part is that the code we're using for the form belongs in the forms code module and I 100% agree). I am open to the idea that this does go against the well known concept of moving UI and code apart but this is "HOW" access works. So the access way does go against trends in our industry.

Keep in mind the above function call idea is the same format we can and would have been using since near day one with menu bars in previous editions of access. So, if you are wanting to change menu bar code to ribbon, use the above idea. Also, if you have several buttons that runs code in a given form, then again the above syntax allows one to KEEP the code in the current form and simply declare the button code as a public function (you can thus real easy move buttons from a form to a ribbon if you do this).

If the function name you specified in the menu or ribbon was named as public in the form's code module, then the CURRENT FORM with the CURRENT FOCUS is where the function will be first looked for to execute. This is SIGNIFICANTLY important because it means you can use one custom menu bar for five different forms, but each of the five different forms will run a custom delete routine for example (no messing with screen.Activeform). And, you don't have to use a bunch of messy case statements as you do with a call back. In fact, all of the code stays in the form where it likely was or belongs in the first place, and that is in the forms code module.

So, if you have specific and specialized delete code that might be required for the given form that has the focus, then that's forms function code in the forms module will be run when it is called from the on action in the menu bar, or now ribbon.

This means if you set the on action to =MyDelete()

Then in each form you have, you simply declare a public functions such as

CODE
Public function MyDelete() 
    Code here to delete the record 
End function

However it turns out for probably more then half or even close to 90% of your forms, it's entirely possible that you want a general catchall delete routine that works for all forms that don't need specialized custom deleting code. In this case you simply place the function in a standard code module (and again as public). If the current form does not have that function, then it is run from a standard code module (again, ideal behavior, and again this is how the onAction worked before ribbon).

Also, note that you also pass values directly from the ribbon.

So, ribbon xml might be:

CODE

In the above, I passed the table name, and a prompt text of Staff.

And the public catch all function in a standard code module will be:

CODE
 Public Function MyDelete(strPrompt As String, strTable As String)
  Dim strSql        As String
  Dim f             As Form
  Set f = Screen.ActiveForm
  If MsgBox("Delete this " & strPrompt & " record?", _
                vbQuestion + vbYesNoCancel, "Delete?") = vbYes Then

 etc....

Note again how I passed two parameters to the delete routine (the text must be under single quotes). The prompt part so the msgbox command will say "Delete this staff ?". And, then I also pass the table name. What the above means is that for ten forms, if you don't specify a public function inside of the form, when the menu button is clicked on is selected, the catchall general routine in an standard code module (non forms code module) will run.

And for the few forms that have specialize deleting code, the function inside of the forms code module will run. That code might look like:

CODE
Public Function MyDelete(s1 as string, s2 as string)

' check for history .... lngHistory = Nz(DLookup("ContactId", "NHistory", "ContactID = " & Me.ContactID), 0)

 If lngHistory > 0 Then
    Beep
    MsgBox "You cannot delete a person with past history bookings!" & vbCrLf & vbCrLf & _
              "You should simply check the 'Do NOT INCLUDE in mailings' to remove from" & vbCrLf & _
              " future mailings.", vbExclamation, "Rides"
  Exit Sub

End If

...code here with sql to delete record....

So a few things: I would just create a ribbon for the given form (take your xml for the given tab, and create a whole new ribbon). Now, if you have 2 or 5 forms open, then the ribbon will automatic flip and change for you. If you try and use tabs, then a simple change of focus to another form will require you to hide+show given tabs - it becomes a real mess rather quick.

How can i make or not make particular Tabs visible by loading a form in Access with VBA?

Question

Explanation:

Problem:

My approach:

Answers (1)

Related Questions