Solved

"New" Switchboard -- need help w/ VBA code

Posted on 2004-10-12
2
784 Views
Last Modified: 2008-03-10
Hello:

I need some help with a "new" Switchboard that I have come across.  Instead of buttons (like on the Northwind.mdb switchboard), this one uses "hover buttons"... it's pretty nice.

Okay, here's what I need some help with:
1. I need to be able to open all forms in "datasheet" view.   How can I change the VBA code so that all (or specific ones) open in datasheet view?

2. All of my forms & reports are maximized when opened.  After I close a (maximized) form or report, the switchboard is also maximized.  How can I make it that the window of the switchboard stays at its original size, regardless of the size of the forms and reports?

3. In the Startup window, I unchecked all boxes (e.g. "Display Database Window", "Display Status Bar", etc).   However, when I now open the databse, the "Database Window" can still be launched (it's only minimized at the bottom left of the screen).   Instead, I want the DB Window complete gone.  [Note: One only should be able to get to the tables when
pressing/holding SHIFT before opening the db (most users won't know that though.]


Below is the code of the "new" switchboard.



Thanks,
Tom




**************************************************************************
Option Compare Database
Option Explicit

Const conNumButtons = 8
Const conFontWeightBold = 700
Const conFontWeightNormal = 400

Private Sub cmdExit_Click()
    CloseCurrentDatabase
End Sub

Private Sub cmdExit_GotFocus()
    Dim intOption As Integer
   
    'If the Exit Button has received the focus, turn off the focus on all the menu options
    For intOption = 1 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
    Next intOption

    ExitLabel.FontUnderline = True
End Sub

Private Sub cmdExit_LostFocus()
    ExitLabel.FontUnderline = False
End Sub

Private Sub cmdExit_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    ExitLabel.FontWeight = conFontWeightBold
End Sub

Private Sub cmdExit_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    ExitLabel.FontWeight = conFontWeightNormal
End Sub

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

On Error GoTo Form_Open_Err

    ' Minimize the database window.
    DoCmd.SelectObject acForm, "Switchboard", True
    DoCmd.Minimize

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

Form_Open_Err:
    MsgBox Err.Description
    Resume Form_Open_Exit
   
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.
   
    Dim dbs As Database
    Dim rst As Recordset
    Dim strSQL 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].Visible = True
    Me![Command1].Enabled = True
    Me![Command1].SetFocus
    With Me![OptionLabel1]
        .Visible = True
        .FontWeight = conFontWeightBold
    End With
    For intOption = 2 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("OptionLabel" & intOption).Visible = False
        Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
        Me("Command" & intOption).Enabled = False
    Next intOption
   
    ' Open the table of Switchboard Items, and find
    ' the first item for this Switchboard Page.
    Set dbs = CurrentDb()
    strSQL = "SELECT * FROM [Switchboard Items]"
    strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
    strSQL = strSQL & " ORDER BY [ItemNumber];"
    Set rst = dbs.OpenRecordset(strSQL)
   
    ' If there are no options for this Switchboard Page,
    ' display a message.  Otherwise, fill the page with the items.
    If (rst.EOF) Then
        Me![OptionLabel1].Caption = "There are no items for this switchboard page"
    Else
        While (Not (rst.EOF))
            Me("OptionLabel" & rst![ItemNumber]).Visible = True
            Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
            Me("Command" & rst![ItemNumber]).Enabled = True
            rst.MoveNext
        Wend
    End If

    ' Close the recordset and the database.
    rst.Close
    dbs.Close

End Sub

Private Function HandleFocus(intBtn As Integer)
' This function is called when a menu option receives the focus.
' intBtn indicates which button was clicked.

    Dim intOption As Integer

On Error GoTo HandleMouseOver_Err

    For intOption = 1 To conNumButtons
        'Show that this menu option has the focus...
        If intOption = intBtn Then
            Me("Option" & intOption).Visible = True
            Me("OptionLabel" & intOption).FontWeight = conFontWeightBold
            Me("command" & intBtn).SetFocus
        '... and turn off the focus on the other options
        Else
            Me("Option" & intOption).Visible = False
            Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
        End If
    Next intOption

HandleMouseOver_Exit:
    Exit Function

HandleMouseOver_Err:
    MsgBox "There was an error executing the command.", vbCritical
    Resume HandleMouseOver_Exit

End Function

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

    ' An error that is special cased.
    Const conErrDoCmdCancelled = 2501
   
    Dim dbs As Database
    Dim rst As Recordset

On Error GoTo HandleButtonClick_Err

    ' Find the item in the Switchboard Items table
    ' that corresponds to the button that was clicked.
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset)
    rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
   
    ' If no item matches, report the error and exit the function.
    If (rst.NoMatch) Then
        MsgBox "There was an error reading the Switchboard Items table."
        rst.Close
        dbs.Close
        Exit Function
    End If
   
    Select Case rst![Command]
       
        ' Go to another switchboard.
        Case conCmdGotoSwitchboard
            Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst![Argument]
           
        ' Open a form in Add mode.
        Case conCmdOpenFormAdd
            DoCmd.OpenForm rst![Argument], , , , acAdd

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

        ' Open a report.
        Case conCmdOpenReport
            DoCmd.OpenReport rst![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 "The Switchboard Manager is not installed. To install this feature, on the Tools menu, point to Database Utilities, and then click Switchboard Manager."
            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 rst![Argument]

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

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

    ' Close the recordset and the database.
    rst.Close
    dbs.Close
   
HandleButtonClick_Exit:
    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

**************************************************************************
0
Comment
Question by:TomBock2004
2 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
1) Select Case rst![Command]
       
        ' Go to another switchboard.
        Case conCmdGotoSwitchboard
            Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst![Argument]
           
        ' Open a form in Add mode.
        Case conCmdOpenFormAdd
            DoCmd.OpenForm rst![Argument], acFormDS, , , acAdd
--------------------------------------------------^^^^^^------------------add this - forces the form to open in Datasheet mode
        ' Open a form.
        Case conCmdOpenFormBrowse
            DoCmd.OpenForm rst![Argument]


2) Add a DoCmd.Restore in the Switchboards Activate event ... this usually works although I've found that sometimes Access "forgets" whether the form is min or max
3) This code is probably causing that:
   ' Minimize the database window.
    DoCmd.SelectObject acForm, "Switchboard", True
    DoCmd.Minimize
 
Is there really any need to minimize the Switchboard? Generally speaking you'd want this to be the first form shown ...

Also, in the Startup Options, make sure Use Access Special Keys is unchecked, and then disable the ShiftKey bypass. This link will show you how:

http://www.mvps.org/access/modules/mdl0011.htm

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
Some other alternatives:

1. Can you not just change their "Default View" property to datasheet view? This will cause them to be opened in DS view regardless of how or where they are opened from.

2. Change the Switchboard form's Border Style to "Thin" and its "Min Max buttons" property to "None" - this will prevent it from being resized.

3. See LSM's post :-)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now