New Multiple Switchboards in an older large db.

[Mackabee]

Hello!

I'm a relatively new 'grammer in Access/VBA and I'm taking over management of a database. It was requested that I add a login control to the database to ensure that everyone logged in properly, so that we can track who makes what changes through the forms.

I added different access levels -- based on what is set in the Employee table in the access_level field. Each access level opens a different menu. My problem is that each is named differently (Menu, Menu1, etc.) There are a LOT of forms that are triggered through this menu -- and they depend on the ComboEmployee box on the menu. I changed this to be set by the login screen and then disabled it. This way the name of the currently logged in user is visible on the menu, but not changeable unless they log out and back in.

The ComboEmployee is then used by all of the triggered forms to track who makes which changes, etc. Unfortunately, when the triggered forms are closed, they are looking to set the focus back to Menu (Forms![Menu].SetFocus). I'm not sure what to do if it should actually be setting the focus back to Menu1 or Menu2...as the case may be.

How do I find out which Menu triggered the form and have it be the object of the .SetFocus command?

Thanks for your help and/or suggestions!

[Peter99]

Why not set up a couple of Public variables at logon time and grab the Logon Id and which menu the logon triggered. You will have this info available until the user logs out and you can use this info anywhere you need to create an audit trail of changes. Remember - keep it simple

[Mackabee]

The reason I didn't do that (it was actually one of my first few thoughts) was because when I started building SQL queries, I could not find a way to access the public variable from the query builder.

[jaxjagfan]

The reason I didn't do that (it was actually one of my first few thoughts) was because when I started building SQL queries, I could not find a way to access the public variable from the query builder.

If you create an unbound textbox on the form and set the value of this unbound textbox to be the variable value you will be able to reference it using the QBE grid and query builder. If you don't want it to show but still reference the textbox change the colors (if form is white - set its background and border to transparent and forecolor to white - it's invisible in forrm view and still able to reference it in code and queries.

[ADezii]

Hello!

I'm a relatively new 'grammer in Access/VBA and I'm taking over management of a database. It was requested that I add a login control to the database to ensure that everyone logged in properly, so that we can track who makes what changes through the forms.

I added different access levels -- based on what is set in the Employee table in the access_level field. Each access level opens a different menu. My problem is that each is named differently (Menu, Menu1, etc.) There are a LOT of forms that are triggered through this menu -- and they depend on the ComboEmployee box on the menu. I changed this to be set by the login screen and then disabled it. This way the name of the currently logged in user is visible on the menu, but not changeable unless they log out and back in.

The ComboEmployee is then used by all of the triggered forms to track who makes which changes, etc. Unfortunately, when the triggered forms are closed, they are looking to set the focus back to Menu (Forms![Menu].SetFocus). I'm not sure what to do if it should actually be setting the focus back to Menu1 or Menu2...as the case may be.

How do I find out which Menu triggered the form and have it be the object of the .SetFocus command?

Thanks for your help and/or suggestions!

1. Are these 'Menus' Custom Menus or the standard Access Switchboards?
2. Are Forms being opened using the OpenForm() Method or through the Switchboard interface?
3. If you are using Custom Menus, are the Forms being opened within the Click() Event of Command Buttons? If not from where?
4. Post some sample code illustrating your point.

[Mackabee]

1. These menus are custom menus. All pre-existing from the original programmer.

2. These forms are being opened using the OpenForm() method:

Private Sub Lbl1_Click()
Dim stDocName As String
Dim stLinkCriteria As String
[formnom] = "FrmClients"
stDocName = "DLTech_Secure"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

3. And, as you can see from the sample code above, the original programmer did not use buttons to launch the new windows. Labels were coded to respond to the Click() event. (he put blue images beneath as you can see from the following code):

Private Sub Lbl1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
lbl1.ForeColor = "16737843"
End Sub

Private Sub lbl1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Img1.Visible = True
Img1.Left = 94.122
Img1.Top = 1038.744
End Sub

These Click() methods are all launched from the custom menu MenuPrincipal. I created MenuPrincipal1 as a separate menu for a different access level. However when each of the new menus launched through the Click() method close, they use the following code to return to MenuPrincipal:

Private Sub Form_Close()
On Error Resume Next
[trackit] = 0
Forms![MenuPrincipal].SetFocus
End Sub


I'm not sure how to return to MenuPrincipal1 if that is the menu that launched the form... or when other menus are added, how to return to them. What is the difference between custom menus and Access Switchboards? Would it be better if I created Access Switchboards instead of using the pre-existing menu and creating MenuPrincipal1 and 2 and so on?

[ADezii]

1. These menus are custom menus. All pre-existing from the original programmer.

2. These forms are being opened using the OpenForm() method:

Private Sub Lbl1_Click()
Dim stDocName As String
Dim stLinkCriteria As String
[formnom] = "FrmClients"
stDocName = "DLTech_Secure"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

3. And, as you can see from the sample code above, the original programmer did not use buttons to launch the new windows. Labels were coded to respond to the Click() event. (he put blue images beneath as you can see from the following code):

Private Sub Lbl1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
lbl1.ForeColor = "16737843"
End Sub

Private Sub lbl1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Img1.Visible = True
Img1.Left = 94.122
Img1.Top = 1038.744
End Sub

These Click() methods are all launched from the custom menu MenuPrincipal. I created MenuPrincipal1 as a separate menu for a different access level. However when each of the new menus launched through the Click() method close, they use the following code to return to MenuPrincipal:

Private Sub Form_Close()
On Error Resume Next
[trackit] = 0
Forms![MenuPrincipal].SetFocus
End Sub


I'm not sure how to return to MenuPrincipal1 if that is the menu that launched the form... or when other menus are added, how to return to them. What is the difference between custom menus and Access Switchboards? Would it be better if I created Access Switchboards instead of using the pre-existing menu and creating MenuPrincipal1 and 2 and so on?

1. The last Argument of the OpenForm() Method is OpenArgs, and you can place any String Expression here. In your case, place the name of the Menu which is opening the Form as in (FrmClients is being opened from Menu1):
   

   DoCmd.OpenForm "FrmClients", , , stLinkCriteria, acFormEdit, acWindowNormal, "Menu1"

2. In the close Event of each Form, test that Form's OpenArgs Property, and return Focus to the proper Form:
   

   Private Sub FrmClients_Close()
     Select Case Me.OpenArgs
       Case "Menu1"
         Forms![Menu1].SetFocus
       Case "Menu2"
         Forms![Menu2].SetFocus
       Case "Menu3"
         Forms![Menu3].SetFocus
       Case etc.
         'I think you have the idea by now!
     End Select
   End Sub

3. Any questions, feel free to ask.

[Mackabee]

Thank you for your time and help. Very much appreciated. :-)

[ADezii]

Thank you for your time and help. Very much appreciated. :-)

You are quite welcome, let me know how you make out.