Switchboard Problems

Posted on 2006-06-17
Last Modified: 2010-05-18
I have an Access Database that I am working on a database for a client.  They have 8 inhouse users running different versions of access (2002, XP, and 2003).  They also have to remote users that come in and use Terminal Services to work.  This has 2000 on the server. The database is built in 2000.  They used the built in Switchboard Manager to create the switchboard.

I have no problems with the inhouse people. They run fine.  Every once in a while the remote users have a problem  with the Main Menu of the Switchboard being populated.  In opens but none of the buttons get populated.  

Has anyone ever had this happen to them and what did you do to ersolve it???? HELP!!!!
Question by:clangl
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
LVL 84
ID: 16926662
Does the switchboard "look" correct, i.e. all the buttons are drawn, etc etc? If not, perhaps this has to do with the Remote Desktop settings - if the local users don't have any problems with this, it could be simply a refresh thing.

Can you post the code that initially populates your switchboard?

FWIW - the Switchboard Manager is quite lame and has caused problems with every version of Access - throw it out and build your own, it's pretty simply.

LVL 34

Expert Comment

ID: 16927075
I agree with Scott, (LSM)
I never had any luck with switchboards in any of our distributed systems. Create your own. One thing you will notice with terminal server based solutions, colors and fonts may change. Try using a simple font like Arial and standard colors.

Author Comment

ID: 16927347
I agree but with the whole Switchhboard thing... THe problem is that I have been brought on as a consultant to help with the reporting and enhancements.  The original developer that created this database is still around and in order to keep future business with the company I don't want to make too many waves....

Here is the code from the switchboard...
Option Compare Database
Option Explicit

Private Sub Command58_Click()
  HandleButtonClick (19)
End Sub

Private Sub Form_Activate()
End Sub

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], "")
    Me!MenuNm.Caption = Nz(Me![ItemText], "")
    Me!ColumnHeader1.Caption = Nz([ColHdr1], "")
    Me!ColumnHeader2.Caption = Nz([ColHdr2], "")
    Me!ColumnHeader3.Caption = Nz([ColHdr3], "")
End Sub

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

    ' The number of buttons on the form.
    Const conNumButtons = 24
    Dim dbs As DAO.Database
    Dim rsT As DAO.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.
    On Error GoTo Err_Han
    For intOption = 2 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("Option" & intOption).Enabled = False 'jef 980916
        Me("OptionLabel" & intOption).Visible = 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"
        While (Not (rsT.EOF))
          If rsT!ItemText <> "NotUsed" Then  'jef 980916
            Me("Option" & rsT![ItemNumber]).Visible = True
            Me("Option" & rsT![ItemNumber]).Enabled = True
            Me("OptionLabel" & rsT![ItemNumber]).Visible = True
            Me("OptionLabel" & rsT![ItemNumber]).Caption = rsT![ItemText]
          End If  'jef 980916
    End If

    ' Close the recordset and the database.
    Exit Sub
End Sub

Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.
'010410 - add error handling for no permission

    ' 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 conCmdOpenTable = 9 'jef 980916
    Const conCmdOpenQuery = 10 '001020

    ' An error that is special cased.
    Const conErrDoCmdCancelled = 2501
    Dim dbs As DAO.Database
    Dim rsT As DAO.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."
        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
        ' Open a table  jef 980916
        Case conCmdOpenTable
          DoCmd.OpenTable rsT![Argument], acViewNormal

        ' 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 "WZMAIN80.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], "")

        ' Exit the application.
        Case conCmdExitApplication
            DoCmd.Quit acQuitSaveAll
        ' Run a macro.
        Case conCmdRunMacro
            DoCmd.RunMacro rsT![Argument]

        ' Run code.
        Case conCmdRunCode
            Application.Run rsT![Argument]
        Case conCmdOpenQuery
            DoCmd.OpenQuery rsT![Argument]

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

    ' Close the recordset and the database.
    On Error Resume Next
    Set rsT = Nothing
    Set dbs = Nothing
    Exit Function

    ' If the action was cancelled by the user for
    ' some reason, don't display an error message.
    ' Instead, resume on the next line.
    Select Case Err
        Case conErrDoCmdCancelled
            Resume Next
        Case 2603, 3110      'do not have permission/no definitions
            MsgBox "You do not have permission to use this function."
            Resume HandleButtonClick_Exit
        Case Else
            MsgBox Err.Number & " " & Err.Description
            MsgBox "There was an error executing the command.", vbCritical
            Resume HandleButtonClick_Exit
    End Select
End Function

Private Sub Option19_Click()
  HandleButtonClick (19)
End Sub
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 44

Expert Comment

by:Leigh Purvis
ID: 16927451
Well, if there original developer is using switchboards he can't be much use surely :-)

OK - that does look like "tweaked" switchboard code.
(I know - because I opened up a crappy MS wizard mdb and checked ;-)

But if it's not running only for the TS users and isn't just a painting issue (as Scott mentions) then is it just that it can't access the data it needs to run?
Is this a split FE/BE setup?

(If not and you have different users with different versions of Access all opening the same file then hmm.. problems could indeed occur).

Have you tried offering a "temporary" proper menu? lol
Say it's for testing.
To allow users to progress beyond the Switchboard if it fails - and determine if the access to data beyond that is interupted too?

P.S. Waves can be fun. ;-)

Author Comment

ID: 16927953
There is nothing I have to worry about with the fact that the users come in through Terminal Services?

They do not run a front\back end.

Now they are just not able to open the file at all.  I had them go in to the console of the terminal server (access 2000) and open the db exclusively and set a break point in the code, open the form, it breaks, they finished executing the code(F5) , and the switchboard fills.  They close the database and try to go back in and the message states that the file is open already and they can not access it.  I had them restart the server and they were able to open it once and then if they closed the database and go back in they had the same problem.

Then I proceeded to bang my head on the wall...  That did not help either...
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 175 total points
ID: 16929222
After troubleshooting, did they close that session of Access? Troubleshooting code in break mode would certainly lock the database ...

The db needs to be split into frontend/backend - Access includes a Database Splitter Wizard to do this - and each user (even TS users) should get a copy of the frontend, and each of thsoe frontends should connect to a single backend ... this may or may not fix the menu problem.

Also, has the database been compacted lately? Might be a good time to perform a compact and repair AFTER making a copy ...
LVL 26

Assisted Solution

dannywareham earned 150 total points
ID: 16929312
FWIW, I would think that splitting will fix.

I would assume that the switchboard population is not running before the form is "painted".
If you step through the code with the TS guys, it populates because you're doing it nice and slowly.

If you place a local FE on their system, this *should* populate and paint faster.
However, you may still have an issue with fetching the switchboard items from the table data.

Also, if you don't split, you're running the risk of additional problems, like corruption and data integrity probs, too.

On the issue of the "developer", I would think that their are waves already if they've employed an external consultant when they already have an in-house developer experienced with that app... :-)  (it's not my company, is it?)


LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 175 total points
ID: 16929351
lol I agree.  (Not that it's your company Danny)

You're there because you're better presumably.  To give recommendations.  So recommend ;-)

And splitting is unquestionably the first step in any system where the bd isn't already split.

Author Comment

ID: 16929371
This is my first experience with using TS.  Where would I drop the Es to ensure each TS user has their own FE?

Author Comment

ID: 16929373
FEs not E's

Author Comment

ID: 16929377
danny.... I hope it is not. LOL
LVL 26

Expert Comment

ID: 16929421
The front ends need installing on every user's PC - possibly in their start menu, my documents or some other area.

The simplest way is to email it (although Outlook will stop Access attchments if the security level is high).

The preferable way is to use a batch file to add it to the C: drive of their PCs.
You can then use this method in future to replace the front ends, should you need to.


Author Comment

ID: 16929567
danny... I am more concerned about the Terminal Service Users...  Is there a dektop and start menu for those specific users that I can drop a copy in each one?
LVL 26

Expert Comment

ID: 16929574
I've not used TS before (we use Citrix here, which gives us a second "window" screen, with its own start menu) - so I'm not the best person to check this past....

However, the front end is completely local - it can be stored anywhere on the end users' computers, providing the table links are all correct.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

756 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