Forms Display in Switchboard

[Thorson]

I have created several forms that display as a "datasheet" layout however,
when I click the button to open the form on the switchboard it displays as
"columnar" layout.

Why is this? Can I change it to display it correctly? I am having it show
the form as "add mode" is that the problem?

 

[fredg]

I have created several forms that display as a "datasheet" layout however,
when I click the button to open the form on the switchboard it displays as
"columnar" layout.

Why is this? Can I change it to display it correctly? I am having it show
the form as "add mode" is that the problem?

Regardless of the Default View setting for a form, if you open it
using VBA from a code event you MUST specify Datasheet View:

DoCmd.OpenForm, acFormDS

 

[Thorson]

I used the switchboard manager to create the switchboard, so I actually
didn't write any code using VBA or anything else. I did open the switchboard
and look at the code, it appears to be rather broad, it doesn't have code
such as what you suggested.

Can you give me some more info as to how I would change the code or what
settings I would change for the switchboard? I don't want all the forms to
open in datasheet view, only specific ones.

Thanks

 

[fredg]

I used the switchboard manager to create the switchboard, so I actually
didn't write any code using VBA or anything else. I did open the switchboard
and look at the code, it appears to be rather broad, it doesn't have code
such as what you suggested.

Can you give me some more info as to how I would change the code or what
settings I would change for the switchboard? I don't want all the forms to
open in datasheet view, only specific ones.

Thanks

There are Switchboards and there are Switchboards. Unless you
specifically state you are using the one created by the Switchboard
Manager we have no way of knowing what you have.
The built-in switchboard manager creates a complicated method for a
simple problem.
You will need to make a change in the code behind the Switchboard, and
then change a value in the Switchboard Items table.

Open the Switchboard form's code window.

Find the Function HandleButton_Click(....) function.
Drop down to the const declarations code. Add:
Const conCmdOpenFormDatasheet = 9
to the list.

Further down in the function, within the Select Case statements, add

Case conCmdOpenFormDatasheet
DoCmd.OpenForm rst!Argument, acFormDS

Save the changes.

Then open the Switchboard Items table and change the Command value for
that form you wish to open from its current 2 or 3 to 9.

That should do it.

You will not be able to use the manager to edit this or open other
forms in Datasheet View.
If you need to open a different form in Datasheet View you will have
to change that form's Command value in the Switchboard Items table.

Much better to create your own Switchboard using an unbound form and
command buttons. If you use the Command Button Wizard when adding the
buttons, Access will write most of the code for you. You'll have more
control over it's appearance and maintenance will be simpler.

 

[Thorson]

I did all that, but it is coming up with an error. Does it matter that there
was already something set equal to "9" in the Const Declarations part, I'm
assuming instead of a 9 to put a 10, but I tried that and it also came up
with an error. I did add the select Case statement and I also changed the
command value for the form. This is what I set that part to:

' 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 conCmdOpenPage = 9
Const conCmdOpenFormDatasheet = 9

 

[fredg]

I did all that, but it is coming up with an error. Does it matter that there
was already something set equal to "9" in the Const Declarations part, I'm
assuming instead of a 9 to put a 10, but I tried that and it also came up
with an error. I did add the select Case statement and I also changed the
command value for the form. This is what I set that part to:

' 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 conCmdOpenPage = 9
Const conCmdOpenFormDatasheet = 9

Telling us that it throws an error is not giving us much useful
information. What is the error number? What is the error text?

Sure it matters. You need to have the Constant value be unique.
Change the value of Const conCmdOpenFormDatasheet to 10.
Then change the Command value in the Switchboard Items table for that
form you wish to open to 10.

Don't forget to also add that Select Case statement I included in my
previous reply.

 

[Thorson]

Below is the new code, I changed the value of Const onCmdOpenFormDatasheet to
10 I also changed the command field for the form to 10 in the Switchboard
Items table. An error still comes up when I try to open the form from the
switchboard, the error states "There Was an error executing the command"
there is no error number or anything.


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

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

Dim con As Object
Dim rs As Object
Dim stSql 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 con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rs.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard
page"
Else
While (Not (rs.EOF))
Me("Option" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
rs.MoveNext
Wend
End If

' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing

End Sub

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
Const conCmdOpenPage = 9
Const conCmdOpenFormDatasheet = 10

' An error that is special cased.
Const conErrDoCmdCancelled = 2501

Dim con As Object
Dim rs As Object
Dim stSql As String

On Error GoTo HandleButtonClick_Err

' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
stSql = "SELECT * FROM [Switchboard Items] "
stSql = stSql & "WHERE [SwitchboardID]=" & Me![SwitchboardID] & " AND
[ItemNumber]=" & intBtn
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If no item matches, report the error and exit the function.
If (rs.EOF) Then
MsgBox "There was an error reading the Switchboard Items table."
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Function
End If

Select Case rs![Command]

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" &
rs![Argument]

' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rs![Argument], , , , acAdd

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

Case conCmdOpenFormDatasheet
DoCmd.OpenForm rst!Argument, acFormDS

' 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 "Command not available."
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 rs![Argument]

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

' Open a Data Access Page
Case conCmdOpenPage
DoCmd.OpenDataAccessPage rs![Argument]

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

End Select

' Close the recordset and the database.
rs.Close

HandleButtonClick_Exit:
On Error Resume Next
Set rs = Nothing
Set con = Nothing
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



End Function

 

[fredg]

On Mon, 8 Dec 2008 08:18:01 -0800, Thorson wrote:

*** snipped ***

You have 2 lines which are improperly broken (most likely just due to
e-mail word wrap. It's probably OK in your code.). I've indicted which
2 they are. They must be all on one line.

stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]

also...

Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" &
rs![Argument]

Also I sent you modified code without realizing I had used a rst
object, while the switchboard manager uses a rs object
i.e. Dim rs as Object.

I've indicated with an asterisk which lines they are.

Case conCmdOpenFormDatasheet
DoCmd.OpenForm rst!Argument, acFormDS ' *

' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rst![Argument], acPreview ' *

Change the 2 above lines marked with ' * to
DoCmd.OpenForm rs!Argument, acFormDS
and
DoCmd.OpenReport rs![Argument], acPreview

That should be all you need do.

 

[Thorson]

Works great! Thank you for all of your help.