Switchboard Problems

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!!!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

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.
clanglAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Leigh PurvisDatabase DeveloperCommented:
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. ;-)
clanglAuthor Commented:
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...
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?)


Leigh PurvisDatabase DeveloperCommented:
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.
clanglAuthor Commented:
This is my first experience with using TS.  Where would I drop the Es to ensure each TS user has their own FE?
clanglAuthor Commented:
FEs not E's
clanglAuthor Commented:
danny.... I hope it is not. LOL
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.

clanglAuthor Commented:
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?
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.