Solved Menus on a Form

Cliff67

Cliff67

Registered User.
Local time
Today, 09:48
Joined
Oct 16, 2018
Messages
175
Hello All,

So my boss has decided to give me some layout advice (poke his nose into a support database) and wants to bring things in line with the latest thinking of MSDN before roll out.

To that end I've had to remove the majority of the buttons used for normal database things and add them to a menu system.

At the top of my form now is this set up

1678110417303.png


I have 3 buttons that are visible at all times (CmdFile, CmdSearch and CmdActions). the light grey ones are not visible when the form opens up and become visible when the corresponding button is clicked. So CmdFile makes visible CmdFile1, CmdFile2 and CmdFile3 while making sure all others buttons are not visible. as you work you way across the buttons, each set is hidden or exposed as you click on them.

Next to the Navigate Records button there are 4 other buttons (Last, first, previous and next) that are unhidden when the Navigate Records button is clicked.

All seems to work well except that when these buttons become visible they do not navigate the records. They work if they are visible at all times but not if hidden then unhidden.

Any one got any ideas what could be the issue here? I suspect Access does not like the fact that they are not visible then are made visible and is not loading or recognising their code

Many thanks in advance

Cliff
 
You may have to show us the code to be able to help you figure out what's happening.
 
F
Code: 
unction showFile()
On Error GoTo ErrHand

    Me.CmdFile1.Visible = True
    Me.CmdFile2.Visible = True
    Me.CmdFile3.Visible = True
On Error GoTo 0
ErrHand:
    Err_Hand ("showFile")
    Err.Clear

End Function
Function hideFile()
On Error GoTo ErrHand
    Me.CmdFile1.Visible = False
    Me.CmdFile2.Visible = False
    Me.CmdFile3.Visible = False
On Error GoTo 0
ErrHand:
    Err_Hand ("hideFile")
    Err.Clear
End Function
Function showSearch()
On Error GoTo ErrHand
    Me.CmdSearch1.Visible = True
    Me.CmdSearch2.Visible = True
On Error GoTo 0
ErrHand:
    Err_Hand ("showSearch")
    Err.Clear
End Function
Function hideSearch()
On Error GoTo ErrHand
    Me.CmdSearch1.Visible = False
    Me.CmdSearch2.Visible = False
On Error GoTo 0
ErrHand:
    Err_Hand ("hideSearch")
    Err.Clear
End Function
Function showActions()
On Error GoTo ErrHand
    Me.CmdAction1.Visible = True
    Me.CmdAction2.Visible = True
    Me.CmdAction3.Visible = True
    Me.CmdAction4.Visible = True
    Me.CmdAction5.Visible = True
    Me.CmdAction6.Visible = True
On Error GoTo 0
ErrHand:
    Err_Hand ("showActions")
    Err.Clear
End Function

Function hideActions()
On Error GoTo ErrHand
    Me.CmdAction1.Visible = False
    Me.CmdAction2.Visible = False
    Me.CmdAction3.Visible = False
    Me.CmdAction4.Visible = False
    Me.CmdAction5.Visible = False
    Me.CmdAction6.Visible = False
On Error GoTo 0
ErrHand:
    Err_Hand ("hideActions")
    Err.Clear
End Function

Function hideNav()
On Error GoTo ErrHand
    Me.CmdLast.Visible = False
    Me.CmdNext.Visible = False
    Me.CmdPrev.Visible = False
    Me.CmdFirst.Visible = False
On Error GoTo 0
ErrHand:
    Err_Hand ("hideNav")
    Err.Clear
 
End Function
Function showNav()
On Error GoTo ErrHand
    Me.CmdLast.Visible = True
    Me.CmdNext.Visible = True
    Me.CmdPrev.Visible = True
    Me.CmdFirst.Visible = True
On Error GoTo 0

ErrHand:
    Err_Hand ("showNav")
    Err.Clear

End Function

On the first Command Button
Private Sub CmdFile_Click()
showFile
hideSearch
hideActions

End Sub

Private Sub CmdFile1_Click()
On Error GoTo ErrHand
TSFlag = True
FlagStart = "TS"
If Me.Dirty Then
    Me.LastModDate = Now()
    Me.LastModUser = MyUserName
End If
Me.CmdFile.SetFocus
hideFile
    DoCmd.OpenForm "Frm_LocationGen", acNormal, "", "", , acNormal

On Error GoTo 0
Exit Sub
ErrHand:
    Err_Hand ("CmdFile1_Click")
    Err.Clear
Exit Sub
End Sub

Private Sub CmdAction5_Click()
On Error GoTo ErrHand
    showNav

On Error GoTo 0
Exit Sub

ErrHand:
    Err_Hand ("CmdAction5_Click")
    Err.Clear
Exit Sub
End Sub

when I select the next button

Private Sub CmdSearches_Click()
     hideFile
     showSearch
      hideActions
End Sub
 
Last edited by a moderator:
bugger the code toggle didn't work one second
 
So here goes

Function showFile()

Me.CmdFile1.Visible = True
Me.CmdFile2.Visible = True
Me.CmdFile3.Visible = True

End Function

Function hideFile()

Me.CmdFile1.Visible = False
Me.CmdFile2.Visible = False
Me.CmdFile3.Visible = False

End Function

Function showSearch()

Me.CmdSearch1.Visible = True
Me.CmdSearch2.Visible = True

End Function
Function hideSearch()

Me.CmdSearch1.Visible = False
Me.CmdSearch2.Visible = False

End Function

Function showActions()

Me.CmdAction1.Visible = True
Me.CmdAction2.Visible = True
Me.CmdAction3.Visible = True
Me.CmdAction4.Visible = True
Me.CmdAction5.Visible = True
Me.CmdAction6.Visible = True

End Function

Function hideActions()

Me.CmdAction1.Visible = False
Me.CmdAction2.Visible = False
Me.CmdAction3.Visible = False
Me.CmdAction4.Visible = False
Me.CmdAction5.Visible = False
Me.CmdAction6.Visible = False

End Function

Function hideNav()

Me.CmdLast.Visible = False
Me.CmdNext.Visible = False
Me.CmdPrev.Visible = False
Me.CmdFirst.Visible = False

End Function

Function showNav()

Me.CmdLast.Visible = True
Me.CmdNext.Visible = True
Me.CmdPrev.Visible = True
Me.CmdFirst.Visible = True


End Function
 
So these get called from the various button such as:

Private Sub CmdSearches_Click()
hideFile
showSearch
hideActions
End Sub

For the navigation button:

Private Sub CmdAction5_Click()
On Error GoTo ErrHand
showNav
On Error GoTo 0
Exit Sub

ErrHand:
Err_Hand ("CmdAction5_Click")
Err.Clear
Exit Sub
End Sub
 
@Cliff67
Please use the </> button in the toolbar to place all the above code in code tags to improve readability & retain formatting
 
Sorry isladogs

Code: 
Function showFile()
On Error GoTo ErrHand

    Me.CmdFile1.Visible = True
    Me.CmdFile2.Visible = True
    Me.CmdFile3.Visible = True
    hideNav
On Error GoTo 0
ErrHand:
    Err_Hand ("showFile")
    Err.Clear

End Function
Function hideFile()
On Error GoTo ErrHand
    Me.CmdFile1.Visible = False
    Me.CmdFile2.Visible = False
    Me.CmdFile3.Visible = False
On Error GoTo 0
ErrHand:
    Err_Hand ("hideFile")
    Err.Clear
End Function
Function showSearch()
On Error GoTo ErrHand
    Me.CmdSearch1.Visible = True
    Me.CmdSearch2.Visible = True
    hideNav
On Error GoTo 0
ErrHand:
    Err_Hand ("showSearch")
    Err.Clear
End Function
Function hideSearch()
On Error GoTo ErrHand
    Me.CmdSearch1.Visible = False
    Me.CmdSearch2.Visible = False
On Error GoTo 0
ErrHand:
    Err_Hand ("hideSearch")
    Err.Clear
End Function
Function showActions()
On Error GoTo ErrHand
    Me.CmdAction1.Visible = True
    Me.CmdAction2.Visible = True
    Me.CmdAction3.Visible = True
    Me.CmdAction4.Visible = True
    Me.CmdAction5.Visible = True
    Me.CmdAction6.Visible = True
On Error GoTo 0
ErrHand:
    Err_Hand ("showActions")
    Err.Clear
End Function
Function hideActions()
On Error GoTo ErrHand
    Me.CmdAction1.Visible = False
    Me.CmdAction2.Visible = False
    Me.CmdAction3.Visible = False
    Me.CmdAction4.Visible = False
    Me.CmdAction5.Visible = False
    Me.CmdAction6.Visible = False
On Error GoTo 0
ErrHand:
If Err.Number = 2165 Then
    Me.CmdActions.SetFocus
    Err.Clear
    Resume
Else
    Err_Hand ("hideActions")
    Err.Clear
End If
End Function

Function hideNav()
On Error GoTo ErrHand
    Me.CmdLast.Visible = False
    Me.CmdNext.Visible = False
    Me.CmdPrev.Visible = False
    Me.CmdFirst.Visible = False
On Error GoTo 0
ErrHand:
    Err_Hand ("hideNav")
    Err.Clear
    
End Function
Function showNav()
On Error GoTo ErrHand
    Me.CmdLast.Visible = True
    Me.CmdNext.Visible = True
    Me.CmdPrev.Visible = True
    Me.CmdFirst.Visible = True
On Error GoTo 0

ErrHand:
    Err_Hand ("showNav")
    Err.Clear

End Function

these are called from the buttons such as:

Code: 
Private Sub CmdActions_Click()
    hideFile
    hideSearch
    showActions
End Sub

I did have a bit of code on the lost focus event that hid the Nav buttons but that seems to have caused the issue, but they periodically appear, and I'm trying to isolate the issue
 
Thanks. Was that really all the code from the earlier posts?
I haven't time to look at the code as I'm about to go out for the evening. Hopefully, someone else will do so.
 
I don't like the built-in error handling:
Code: 
Function showFile()
On Error GoTo ErrHand

    Me.CmdFile1.Visible = True
    Me.CmdFile2.Visible = True
    Me.CmdFile3.Visible = True
    hideNav

On Error GoTo 0 '<--- brings nothing more at this point
Exit function   '<--- do not run into Err_hand

ErrHand:
    Err_Hand ("showFile")  '<--- Call Err_Hand("showFile")  |  or:  Err_Hand "showFile"
    Err.Clear

End Function

Code: 
Function hideNav()
On Error GoTo ErrHand
    Me.CmdLast.Visible = False
    Me.CmdNext.Visible = False
    Me.CmdPrev.Visible = False
    Me.CmdFirst.Visible = False
On Error GoTo 0 '<---
ErrHand:
    Err_Hand ("hideNav")
    Err.Clear   '<---

End Function
If an error occurs in hideNav, it is simply cleared after handling and the code that called hideNav continues to run normally. Is this how it is supposed to be?
 
Last edited:
Hi @Josef P.

Josef P. said:
I don't like the built-in error handling:
Code: 
Function showFile()
On Error GoTo ErrHand

    Me.CmdFile1.Visible = True
    Me.CmdFile2.Visible = True
    Me.CmdFile3.Visible = True
    hideNav

On Error GoTo 0 '<--- brings nothing more at this point
Exit function   '<--- do not run into Err_hand

ErrHand:
    Err_Hand ("showFile")  '<--- Call Err_Hand("showFile")  |  or:  Err_Hand "showFile"
    Err.Clear

End Function

Code: 
Function hideNav()
On Error GoTo ErrHand
    Me.CmdLast.Visible = False
    Me.CmdNext.Visible = False
    Me.CmdPrev.Visible = False
    Me.CmdFirst.Visible = False
On Error GoTo 0 '<---
ErrHand:
    Err_Hand ("hideNav")
    Err.Clear   '<---

End Function
If an error occurs in hideNav, it is simply cleared after handling and the code that called hideNav continues to run normally. Is this how it is supposed to be?
Click to expand...
in reply, to you questions,
1. I only need to clear the error 2165 which is the fact that the control has the focus and you can't hide a control with the focus
2. the Err_Hand is a procedure that logs errors (on an Error log table) that I have not accounted for by the error number, description, originating form, the user, time of event and the procedure that is it called from hence Err_Hand("hideNav") there are defensive functions written behind this that checks the validity of the strings and integers, nulls etc. There is a whole module devoted to error handling in this database
3. On Error Goto 0 actually turns the error handling off (this is a common practice with other programming languages) if you don't have this VBA will continue to process the rest of the code and run the error handling
4. this bit above means that if there are no errors then you exit the function. If you do get an error you will goto the Err_Hand part
5. Err.Clear stops the error being handled again
the over all process is:
On error - arms the error handling
code goes here
on error goto 0 - switch off the error handling
exit the function/sub if no errors
ErrHandling
Logs it via other procedures and returns back
End the function/sub

Hope that clears up my error handling. A lot of the ideas were taken from a book called "Access database design and programming" by Steven Roman (circa 2002 - the book with the Aardvark on the front cover) and I've used this extensively in most of my Access FE databases.

Cliff
 
Last edited:
@isladogs I've managed to find the original issue, I had one of the hide functions on the buttons LostFocus event. As soon as you lost focus it would try to hide the buttons so were not really active and not really hidden. Once removed The buttons worked correctly.

I then found that I would get an unhandled error that would leave the buttons unhidden if they were the one with the focus a quick "resume next" works well now.

Thank you for looking into it for me

Cliff
 
Hi Cliff!
3. On Error Goto 0 actually turns the error handling off (this is a common practice with other programming languages) if you don't have this VBA will continue to process the rest of the code and run the error handling
Click to expand...
... in the current procedure. If is no error occurs you should exit before "ErrHand:" (missing Exit Function in your code.)
So On error goto 0 has no effect.

4. this bit above means that if there are no errors then you exit the function
Click to expand...
This is wrong. "ErrHand:" is not the syntax for start of error handling.

Simple example:
Code: 
Function TestMe()
On Error GoTo ErrHand
    Msgbox "normal code"
On Error GoTo 0 '<---
ErrHand:
    Msgbox "Error handler" '<--- You expect this MsgBox not to be displayed?

End Function

/edit:
I started a new thread for topic "error handler in VBA": https://www.access-programmers.co.uk/forums/threads/error-handling-in-vba-how-to-do-it-wrong.326910/
 
Last edited:
You can consolidate your show/hide code into three procedures or 1 that takes a group option by using a loop instead of individual sets. You can even send an on/off indicator so you only need ONE loop to set all the controls affected instead of separate show and hide code. Add a value to the tag property of the buttons.
Here's a code sample of how to use the tag property. I posted it just to show you how the loop will work but you would need a case within the If ctl.Tag = "R" statement to limit the action to only one group at a time. You would need two additional arguments. One for the group and a true or false to use to set the visible property. This sample looks at several types of controls, yours should look only at the acCommandButton type
Code: 
Public Function EnsureNotEmpty(frm As Form) As Boolean
Dim ctl As Control

    For Each ctl In frm.Controls
       Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionGroup
                If ctl.Tag = "R" Then
                    If ctl & "" = "" Then
                        ctl.SetFocus
                        EnsureNotEmpty = False
                        MsgBox ctl.Name & " is required.", vbOKOnly
                        Exit Function
                    End If
                End If
            Case Else
        End Select
    Next ctl
    
    EnsureNotEmpty = True
End Function
 
Pat Hartman said:
You can consolidate your show/hide code into three procedures or 1 that takes a group option by using a loop instead of individual sets. You can even send an on/off indicator so you only need ONE loop to set all the controls affected instead of separate show and hide code. Add a value to the tag property of the buttons.
Here's a code sample of how to use the tag property. I posted it just to show you how the loop will work but you would need a case within the If ctl.Tag = "R" statement to limit the action to only one group at a time. You would need two additional arguments. One for the group and a true or false to use to set the visible property. This sample looks at several types of controls, yours should look only at the acCommandButton type
Code: 
Public Function EnsureNotEmpty(frm As Form) As Boolean
Dim ctl As Control

    For Each ctl In frm.Controls
       Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionGroup
                If ctl.Tag = "R" Then
                    If ctl & "" = "" Then
                        ctl.SetFocus
                        EnsureNotEmpty = False
                        MsgBox ctl.Name & " is required.", vbOKOnly
                        Exit Function
                    End If
                End If
            Case Else
        End Select
    Next ctl
   
    EnsureNotEmpty = True
End Function
Click to expand...
@Pat Hartman Many thanks for the code snippet. I'll give it a try, it is always useful to have another approach in the arsenal.
 
Hello All,

a quick update, Having gone through the code I found that I was not managing the focus of a control correctly. once I found this it all works ok.

Thank you all for your advice
 
Glad you found it but you should still refactor the code to use a loop instead of hardcoding the control names.
 
@Pat Hartman Yes, I will create looping system before deployment, I just wanted to make sure it did want I wanted it to do. Now I've proved the idea an refine it. It will be worth it as I've got other forms that need this style of menus.

once again thank you all for your help
 
Last edited:
You're welcome. Notice how the code I posted uses a reference to a form. That means the code will work for any form. If you add your menu data to tables, you won't even need to create multiple forms. You can refine your logic and use only a single form and a single code base.
 
You must log in or register to reply here.

Similar threads

brucemc777
Show/Hide a control based on whether or not 4 other controls have contents
Replies
4
Views
139
brucemc777
brucemc777
J
Label Visibility
Replies
4
Views
183
DHookom
DHookom
D
Solved Use two buttons to print the same report where one prints the detail section ... Freezing my application
2
Replies
24
Views
464
Gasman
Gasman
M
Strange behaviour with form
Replies
9
Views
337
Pat Hartman
P
I
Navigation Button Active
Replies
14
Views
1,238
Edgar_
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom