Logical Statement on a SwitchBoard Menu.

I

iholder

Is it possible to use a logical statement before open an switchboard menu.

Example: Check if user is authorized to access that switchboard menu.

Thank
Ileana
 
A

Arvin Meyer [MVP]

Yes but you would have to do that as part of the switchboard form not any
part of the switchboard itself.
 
I

iholder

Thank you, I access 2003. I will study the coding. I think this will address
what I want to do. If any problems I will post again.
Ileana


KenSheridan via AccessMonster.com said:
It may differ in Access 2007, which I don't have, but in earlier versions you
need to amend the code in the switchboard form's module. For example
amending this code in the HandleButtonClick event procedure:

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs!
[Argument]

to this:

' Go to another switchboard.
Case conCmdGotoSwitchboard
If rs![Argument] = 2 Then
If GetUser() = "Ken" Then
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs!
[Argument]
End If
Else
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs!
[Argument]
End If

allows only the user logged in as Ken to open page 2 of a switchboard but all
users to open other pages. To bar Ken from opening page 2, but allow all
other users to do so you'd use GetUser() = "Ken".

In this case it calls a function GetUser to identify the currently logged in
user. For this the following module should be added to the database:

''''module basGetUser''''
''''module starts''''
Option Compare Database
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
lpBuffer As String, nSize As Long) As Long


Public Function GetUser() As String

Dim strBuffer As String
Dim lngSize As Long, lngRetVal As Long

lngSize = 199
strBuffer = String$(200, 0)

lngRetVal = GetUserName(strBuffer, lngSize)

GetUser = Left$(strBuffer, lngSize - 1)

End Function
''''module ends''''

Ken Sheridan
Stafford, England
An example of how this is done.
Thank you
Yes but you would have to do that as part of the switchboard form not any
part of the switchboard itself.
[quoted text clipped - 6 lines]
 
I

iholder

My code HandleButtonClick event code in Access 2003

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.

' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True

End Sub

Private Sub Form_Current()
' Update the caption and fill in the list of options.

Me.Caption = Nz(Me![ItemText], "")
FillOptions

End Sub

Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.
Const conNumButtons = 8

Dim con As Object
Dim rs As Object
Dim stSql As String
Dim intOption As Integer

' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rs.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard
page"
Else
While (Not (rs.EOF))
Me("Option" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
rs.MoveNext
Wend
End If

' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing

End Sub

Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.

' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9

' An error that is special cased.
Const conErrDoCmdCancelled = 2501

Dim con As Object
Dim rs As Object
Dim stSql As String

On Error GoTo HandleButtonClick_Err

' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
stSql = "SELECT * FROM [Switchboard Items] "
stSql = stSql & "WHERE [SwitchboardID]=" & Me![SwitchboardID] & " AND
[ItemNumber]=" & intBtn
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If no item matches, report the error and exit the function.
If (rs.EOF) Then
MsgBox "There was an error reading the Switchboard Items table."
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Function
End If

Select Case rs![Command]

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" &
rs![Argument]

' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rs![Argument], , , , acAdd

' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rs![Argument]

' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rs![Argument], acPreview

' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "ACWZMAIN.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions

' Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase

' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rs![Argument]

' Run code.
Case conCmdRunCode
Application.Run rs![Argument]

' Open a Data Access Page
Case conCmdOpenPage
DoCmd.OpenDataAccessPage rs![Argument]

' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."

End Select

' Close the recordset and the database.
rs.Close

HandleButtonClick_Exit:
On Error Resume Next
Set rs = Nothing
Set con = Nothing
Exit Function

HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If

End Function

What part should I modify. Little confuse.

Thank you
Ileana


KenSheridan via AccessMonster.com said:
It may differ in Access 2007, which I don't have, but in earlier versions you
need to amend the code in the switchboard form's module. For example
amending this code in the HandleButtonClick event procedure:

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs!
[Argument]

to this:

' Go to another switchboard.
Case conCmdGotoSwitchboard
If rs![Argument] = 2 Then
If GetUser() = "Ken" Then
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs!
[Argument]
End If
Else
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs!
[Argument]
End If

allows only the user logged in as Ken to open page 2 of a switchboard but all
users to open other pages. To bar Ken from opening page 2, but allow all
other users to do so you'd use GetUser() = "Ken".

In this case it calls a function GetUser to identify the currently logged in
user. For this the following module should be added to the database:

''''module basGetUser''''
''''module starts''''
Option Compare Database
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
lpBuffer As String, nSize As Long) As Long


Public Function GetUser() As String

Dim strBuffer As String
Dim lngSize As Long, lngRetVal As Long

lngSize = 199
strBuffer = String$(200, 0)

lngRetVal = GetUserName(strBuffer, lngSize)

GetUser = Left$(strBuffer, lngSize - 1)

End Function
''''module ends''''

Ken Sheridan
Stafford, England
An example of how this is done.
Thank you
Yes but you would have to do that as part of the switchboard form not any
part of the switchboard itself.
[quoted text clipped - 6 lines]
 
J

James A. Fortune

Its worth pointing out that I, and I think most experienced developers, would
not normally use the built in switchboard designer in an application but
would design our own opening form, adding buttons and the necessary code to
carry out the tasks involved. To make some action conditional on some
criterion is then simply a case of including the relevant code in an If….End
If construct where necessary. This is much simpler than having to hack
around inside a wizard generated switchboard's module. I have used a
standard format for most applications' opening forms over the years which
reflects my own 'house style'.

Take Ken's words to heart. I'd surely fail an Access 101 exam. I've
never used the switchboard wizard even once! I have billed
approximately 20,000 hours of Access database programming.

James A. Fortune
(e-mail address removed)
 
A

Arvin Meyer [MVP]

Its worth pointing out that I, and I think most experienced developers,
would
not normally use the built in switchboard designer in an application

Take Ken's words to heart. I'd surely fail an Access 101 exam. I've
never used the switchboard wizard even once! I have billed
approximately 20,000 hours of Access database programming.

James A. Fortune
(e-mail address removed)

I'd have to echo that. I've been working in Access since it came out in
1992, and I've only used switchboard type forms in 2 applications. Both of
those were custom forms and the only similarity to the wizard driven form
was that the report names were stored in a table. That allowed users to add
their own reports without having to program anything. I used a listbox to
read the table and start the reports.

I have worked both by myself and with teams of up to 9 developers, and have
never seen a switchboard used in 17 years since Access first came out.
 
G

Guest

iholder said:
Is it possible to use a logical statement before open an switchboard menu.

Example: Check if user is authorized to access that switchboard menu.

Thank
Ileana
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top