• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1512
  • Last Modified:

Converted Switchboard Macro to VBA - now blank switchboard

Hey Experts: I wanted to add a line of code to the switchboard that would hide the Ribbon since in Access 2007 the forms design are smaller than previous versions. However, I converted the switchboard macro to vba and now the switchboard opens blank; I don't have a clue why. So, this is a two part question: 1) how do I make VBA the default when creating a form; and 2) what is wrong with the switchboard code now since it shows up blank? Thanks.
Option Compare Database

'------------------------------------------------------------
' Form_Current
'
'------------------------------------------------------------
Private Sub Form_Current()
On Error GoTo Form_Current_Err

    TempVars.Add "CurrentItemNumber", "[ItemNumber]"


Form_Current_Exit:
    Exit Sub

Form_Current_Err:
    MsgBox Error$
    Resume Form_Current_Exit

End Sub


'------------------------------------------------------------
' Form_Open
'
'------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err

    TempVars.Add "SwitchboardID", "DLookUp(""SwitchboardID"",""Switchboard Items"",""[ItemNumber] = 0 AND [Argument] = 'Default'"")"
    DoCmd.SetProperty "Label1", acPropertyCaption, DLookup("ItemText", "Switchboard Items", "[SwitchboardID] = " & TempVars("SwitchboardID"))
    DoCmd.SetProperty "Label2", acPropertyCaption, DLookup("ItemText", "Switchboard Items", "[SwitchboardID] = " & TempVars("SwitchboardID"))
    DoCmd.Requery ""


Form_Open_Exit:
    Exit Sub

Form_Open_Err:
    MsgBox Error$
    Resume Form_Open_Exit

End Sub


'------------------------------------------------------------
' Option1_Click
'
'------------------------------------------------------------
Private Sub Option1_Click()
On Error GoTo Option1_Click_Err

    On Error GoTo 0
    If (Command = 1) Then
        TempVars.Add "SwitchboardID", "[Argument]"
        DoCmd.SetProperty "Label1", acPropertyCaption, DLookup("ItemText", "Switchboard Items", "[SwitchboardID] = " & TempVars("SwitchboardID"))
        DoCmd.SetProperty "Label2", acPropertyCaption, DLookup("ItemText", "Switchboard Items", "[SwitchboardID] = " & TempVars("SwitchboardID"))
        DoCmd.Requery ""
        Exit Sub
    End If
    If (Command = 2) Then
        DoCmd.OpenForm Argument, acNormal, "", "", acAdd, acNormal
        Exit Sub
    End If
    If (Command = 3) Then
        DoCmd.OpenForm Argument, acNormal, "", "", , acNormal
        Exit Sub
    End If
    If (Command = 4) Then
        DoCmd.OpenReport Argument, acViewReport, "", "", acNormal
        Exit Sub
    End If
    If (Command = 5) Then
        DoCmd.RunCommand acCmdSwitchboardManager
        TempVars.Add "SwitchboardID", "DLookUp(""SwitchboardID"",""Switchboard Items"",""[ItemNumber] = 0 AND [Argument] = 'Default'"")"
        DoCmd.SetProperty "Label1", acPropertyCaption, DLookup("ItemText", "Switchboard Items", "[SwitchboardID] = " & TempVars("SwitchboardID"))
        DoCmd.SetProperty "Label2", acPropertyCaption, DLookup("ItemText", "Switchboard Items", "[SwitchboardID] = " & TempVars("SwitchboardID"))
        DoCmd.Requery ""
        Exit Sub
    End If
    If (Command = 6) Then
        DoCmd.CloseDatabase
        Exit Sub
    End If
    If (Command = 7) Then
        DoCmd.RunMacro Argument, , ""
        Exit Sub
    End If
    If (Command = 8) Then
        Call Argument & "()"
        Exit Sub
    End If
    Beep
    MsgBox "Unknown option.", vbOKOnly, ""


Option1_Click_Exit:
    Exit Sub

Option1_Click_Err:
    MsgBox Error$
    Resume Option1_Click_Exit

End Sub


'------------------------------------------------------------
' OptionLabel1_Click
'
'------------------------------------------------------------
Private Sub OptionLabel1_Click()
On Error GoTo OptionLabel1_Click_Err

    On Error GoTo 0
    If (Command = 1) Then
        TempVars.Add "SwitchboardID", "[Argument]"
        DoCmd.SetProperty "Label1", acPropertyCaption, DLookup("ItemText", "Switchboard Items", "[SwitchboardID] = " & TempVars("SwitchboardID"))
        DoCmd.SetProperty "Label2", acPropertyCaption, DLookup("ItemText", "Switchboard Items", "[SwitchboardID] = " & TempVars("SwitchboardID"))
        DoCmd.Requery ""
        Exit Sub
    End If
    If (Command = 2) Then
        DoCmd.OpenForm Argument, acNormal, "", "", acAdd, acNormal
        Exit Sub
    End If
    If (Command = 3) Then
        DoCmd.OpenForm Argument, acNormal, "", "", , acNormal
        Exit Sub
    End If
    If (Command = 4) Then
        DoCmd.OpenReport Argument, acViewReport, "", "", acNormal
        Exit Sub
    End If
    If (Command = 5) Then
        DoCmd.RunCommand acCmdSwitchboardManager
        TempVars.Add "SwitchboardID", "DLookUp(""SwitchboardID"",""Switchboard Items"",""[ItemNumber] = 0 AND [Argument] = 'Default'"")"
        DoCmd.SetProperty "Label1", acPropertyCaption, DLookup("ItemText", "Switchboard Items", "[SwitchboardID] = " & TempVars("SwitchboardID"))
        DoCmd.SetProperty "Label2", acPropertyCaption, DLookup("ItemText", "Switchboard Items", "[SwitchboardID] = " & TempVars("SwitchboardID"))
        DoCmd.Requery ""
        Exit Sub
    End If
    If (Command = 6) Then
        DoCmd.CloseDatabase
        Exit Sub
    End If
    If (Command = 7) Then
        DoCmd.RunMacro Argument, , ""
        Exit Sub
    End If
    If (Command = 8) Then
        Call Argument & "()"
        Exit Sub
    End If
    Beep
    MsgBox "Unknown option.", vbOKOnly, ""


OptionLabel1_Click_Exit:
    Exit Sub

OptionLabel1_Click_Err:
    MsgBox Error$
    Resume OptionLabel1_Click_Exit

End Sub

Open in new window

Capture.JPG
0
stevensont
Asked:
stevensont
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
1) You can set the VBA Editor Always Use Events: Open the Access Options dialog, click Object Designers and find the Form/REport Design section. Check the box titled "Always Use Event procedures"

2) Are you sure you have items in the Switchboard table where Argument = "Default" and ItemNumber = 0? If not, your switchboard won't show anything.

FWIW, the Switchboard has always been buggy and is steadfastly avoided by most Access developers.
0
 
stevensontAuthor Commented:
The solution to my second question was the switchboard recordsource had [SwitchboardID] with a criteria of [TempVars]![SwitchboardID]. I have no idea why or what its intent was, but once i removed it, the switchboard items appeared and the switchboard functioned correctly.

LSMConsulting: You answered my first question so I'm awarding you half the points. Thanks.
0
 
stevensontAuthor Commented:
Question answered and fix implemented.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now