How to Increase number of menu items in a switchboard? 5

[khwaja]

Switchboard shipped with templates in Access are wonderful tools for creating menu driven systems. I tried adopting one ane but unfortunately I could not increase the number of menu items in any given screen more than 8. I tried modifying the following code (Const conNumbButons = 8) which inderlies the switchboard but to no avail.

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

' The number of buttons on the form.

Const conNumButtons = 8
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].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 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("Option" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
rst.MoveNext
Wend
End If

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

End Sub

Could someone kindly enlighten me how to increase number of menu items?

Cheers

AK

 

[montrose]

AK, If you use the built-in switchboard, you are limited to eight (8) items. You can, however, make multiple switch boards that you can drill down to from you main, default switchbaord. if you want more than eight items, you can create your own form with whatever number of command buttons you need and set it as the default switch booard. HTH, Montrose Learn what you can and share what you know.

 

[khwaja]

Thanks. Yes I was aware of the fact that I can add another switchboard but it just did not make sense to have similar grouping of menu items being accessed using two different buttons.

Your proposition of creating my own form is interesting. What does this involve? Can I use the same code to apply to a new form? I am newbie and may need a little bit of hand holding.

Cheers

 

[montrose]

AK, To create your own switchboard form, just create a new form in design view. Do not have it bound to any record source (no query or table). Add as many command buttons as you need to open any forms, reports, or queries (no code is needed, the Wizard will prompt you through all of it). I'm not sure which version of Access you are using, but go into the Switchboard manager (in Access 2000 it's Tools, StartUp) and which it asks you which form to display, pick out your newly designed Switchboard form (whatever you have named it). That's it-pretty simple and straight forward. Don't think you'll need a bit of hand holding! HTH, Montrose Learn what you can and share what you know.

 

[khwaja]

Thanks. I am using MS Access 97 and selecting Switch Board Manager actually creates one for you instead of asking if it can use a different form designed by me. So no resolution!!

Cheers

 

[montrose]

In '97, Tools, Add-Ins, Switchboard Manager. When the Switchboard Manager comes up, pick your default page. Click edit. Edit Switchboard Page, click 'New'. Text=what you want to call it, 'Command' what you want it to do, and 'Switchboard' pick whatever form you designed to use as your Sw3itchboard. If you need assistance to work through it, go into Help, type in Switchboard, and your second option to look at will be "Create a ne switchboard that you can open from the opening switchboard". Learn what you can and share what you know.

 

[khwaja]

Thanks. This still does not work. Even though you are creating a new switchboard, it still dictates limit of 8 items on the new form. I tried adding more than 8. There must be some way through the code?

Regards

 

[montrose]

Go into the OnCurrent event for the default Switchboard you've set up with the Wizard. See this line of code:

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

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

Change 'Const conNumButtons = 8' to 9, 10, 11, 12, whatever. Add how many buttons you need. HTH
Learn what you can and share what you know.

 

[khwaja]

Thanks I tried changing the number to 10 in the statement and then adding two more buttons. Following error message came up:

Run Time Error: 2465
Microsoft Cannot find the Field 'Option 9' in your expression

And then it offered to debug.

AK

 

[khwaja]

I think I have figured out the reason for the error. I should have changed the name of the new labels and buttons using the same convention as others annd it worked.

Much appreciate your help.

Cheers

 

[khwaja]

I guess i jumped the gun. I managed to reflect two more buttons and change in the code. But when it came to adding additional itemsu (more than 8), it still did not let you add and came up with the message 'Sorry only eight items are allowed per switchboard page".

I suspect there might be some other code that needs to be amended.

Cheers

 

[montrose]

AK,
1. You have to COPY ONE OF THE ACTUAL CONTROLS as many times as you need it (sorry if I didn't say that the first time!-otherwise it will keep telling only 8 is allowed)
2. You've already figured out the naming conventions part for your buttons/labels and make sure you've modified the "on click" properties e.g, for Option10, OnClick, =HandleButtonClick(10)
3. Make sure to go to the Access Tables tab, and go into your Switchboard table. Add your new items. Follow the structure that Access has used in this table; switchboard page, item position in menu, item name, event it triggers, etc.. Make sure in the Command field that you use the basic values
GotoSwitchboard = 1;
OpenFormAdd = 2;
OpenFormBrowse = 3;
OpenReport = 4;
Customise Switchboard = 5;
ExitApplication = 6;
RunMacro = 7;
RunCode = 8;

OTHERWISE! You said, "Even though you are creating a new switchboard, it still dictates limit of 8 items on the new form". Don't use the wizard form/wizard. Unless you modify the wizard switchboard form as above (including the Switchboard table) you'll indeed be limited to eight buttons. Instead,
1. Create your OWN form in the Forms tab, calling it what you want with as many buttons to do whatever you need.
2. Then go into Tools, Startup. Display Form (listbox on right) shows Swithcboard as default, but if you arrow down, you'll see you can actuall use ANY form for your display form. That is where you'd indicate to use your own custom made "Switchbaord" form.

Sorry you've had such a tough time with this and that my responses were unclear to you at times. I hope you finally get this to work. Montrose



Learn what you can and share what you know.

 

[RobPotts]

Hi Montrose

Thanks for the help.

I thought I was going to have to create a big pile of forms for my own switchboard, but this is exactly what I was after.

Thanks again and have a star, for some 1 year old advise

Rob!

 

[montrose]

RobPotts, You're welcome! Glad you found it and that you were able to use it. montrose (ps-thanks for the star!) Learn what you can and share what you know.

 

[janetlyn]

Hey, Montrose, guess you need to publish this. It helped me also. Pink star for you. JL

 

[montrose]

janetlyn, Catching up on tek-tips after being away for a while. Presuming you found this thread doing a search-glad it was helpful! montrose

Learn what you can and share what you know.