opening 2 different forms from the same module

[dixxy]

Hello,

I am wondering the following question.

In my database i used the 'default' Access switchboard as start-up form. This switchboard has many buttons and menus. 2 of those open the same form in different state.

example: button 1 opens form Jobs in 'Add Mode'
Button 2 opens form Jobs in 'Edit State'
each button is connected to a different macro, that each run a seperate module that open the same form 'frmJobs' in diferrent states, Add or Edit.

My question is as follow: I would like to be able to use these same modules, but to open a different form the same way has i open frmJobs.

Here are the modules that the 2 different macro's run:

Add mode

Public Function OpenFormAdd()

Dim State As Integer
Dim stDocName As String
stDocName = "frmJobs"

DoCmd.OpenForm stDocName, , , , acFormAdd
'MsgBox "open in add mode"
'Forms!frmJobs.cmdSearch.Visible = False
Forms!frmJobs.Text179.Value = 0
Forms!frmJobs.cmdSearch.Visible = False
End Function

Edit mode

Public Function OpenFormEdit()

Dim State As Integer
Dim stDocName As String
stDocName = "frmJobs"

DoCmd.OpenForm stDocName, , , , acFormEdit
'MsgBox "open in add mode"
Forms!frmJobs.JobNumber.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Forms!frmJobs.cmdNew.Visible = False
Forms!frmJobs.Text179.Value = 1
Forms!frmJobs.Label95.Caption = "Search Form"
Forms!frmJobs.CustomerType.Locked = True
DoCmd.GoToRecord , , acFirst
If Forms!frmJobs.Text179 = 1 Then
Forms!frmJobs.cmdCancel.Enabled = True
End If
End Function

Is there a way to find out where the click event took place from the Access switchboard to be able to change dinamically the 'stDocName'?

Thanks,

Sylvain

 

[TheAceMan1]

How are ya dixxy . . .

What you need is a [blue]common routine[/blue] to open any form. Here's an example.

In a [blue]module[/blue] in the [blue]modules window[/blue], copy/paste the following function:

[blue]Public Function OpenForm(frmName As String, frmMode As String)

   If frmName = "frmJobs" Then
      If frmMode = "Add" Then
         DoCmd.OpenForm stDocName, , , , acFormAdd
         [green]'MsgBox "open in add mode"
         'Forms!frmJobs.cmdSearch.Visible = False[/green]
         Forms!frmJobs.Text179.Value = 0
         Forms!frmJobs.cmdSearch.Visible = False
      ElseIf frmMode = "Edit" Then
         DoCmd.OpenForm stDocName, , , , acFormEdit
         [green]'MsgBox "open in add mode"[/green]
         Forms!frmJobs.JobNumber.SetFocus
         DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
         Forms!frmJobs.cmdNew.Visible = False
         Forms!frmJobs.Text179.Value = 1
         Forms!frmJobs.Label95.Caption = "Search Form"
         Forms!frmJobs.CustomerType.Locked = True
         DoCmd.GoToRecord , , acFirst
         
         If Forms!frmJobs.Text179 = 1 Then
            Forms!frmJobs.cmdCancel.Enabled = True
         End If
   ElseIf frmName = "AnotherFormName" Then
      If frmMode = "ADD" Then
         [green]'add code[/green]
      ElseIf frmMode = "Edit" Then
         [green]'edit code[/green]
      End If
   ElseIf frmName = "AnotherFormName" Then
      [green]'code[/green]
   End If
      
End Function[/blue]

To open your jobs form:

[blue]   Call OpenForm("frmJobs", "Add")[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[dixxy]

sounds really cool, i'll have to play with this and report back....
thanks for the tip...just not sure how Access will know the name of the form that i am trying to open....

Thanks,

Sylvain

 

[TheAceMan1]

dixxy . . .

Sorry! There's an [blue]End If[/blue] missing:

[blue]Public Function OpenForm(frmName As String, frmMode As String)

   If frmName = "frmJobs" Then
      If frmMode = "Add" Then
         DoCmd.OpenForm stDocName, , , , acFormAdd
         'MsgBox "open in add mode"
         'Forms!frmJobs.cmdSearch.Visible = False
         Forms!frmJobs.Text179.Value = 0
         Forms!frmJobs.cmdSearch.Visible = False
      ElseIf frmMode = "Edit" Then
         DoCmd.OpenForm stDocName, , , , acFormEdit
         'MsgBox "open in add mode"
         Forms!frmJobs.JobNumber.SetFocus
         DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
         Forms!frmJobs.cmdNew.Visible = False
         Forms!frmJobs.Text179.Value = 1
         Forms!frmJobs.Label95.Caption = "Search Form"
         Forms!frmJobs.CustomerType.Locked = True
         DoCmd.GoToRecord , , acFirst
         
         If Forms!frmJobs.Text179 = 1 Then
            Forms!frmJobs.cmdCancel.Enabled = True
         End If
      [purple][b]End If[/b][/purple]
   ElseIf frmName = "AnotherFormName" Then
      If frmMode = "ADD" Then
         'add code
      ElseIf frmMode = "Edit" Then
         'edit code
      End If
   ElseIf frmName = "AnotherFormName" Then
      'code
   End If
      
End Function[/blue]

See Ya! . . . . . .

 

[dixxy]

ok thank again but you missed my second question...

sounds really cool, i'll have to play with this and report back....
thanks for the tip...just not sure how Access will know the name of the form that i am trying to open?

Thanks,

Sylvain

 

[TheAceMan1]

[blue] . . . just not sure how Access will know the name of the form that i am trying to open?[/blue]

In the tables window you should have a table [blue]SwitchBoard Items[/blue]. It will show you the forms that are selected . . .

See Ya! . . . . . .

 

[dixxy]

ok, trying this out, i am having trouble with it, where do i put the code

Call OpenForm("frmJobs", "Add")

in the swithboard? Do i set it to 'Run Code' and put that in the 'function name'? cause that gives me an error message 'there was an error executing command'...

tried with 'call' and without, same thing.

before i had the switchboard set to run a macro, do i have to do the same again?

i'm confused....

Thanks,

Sylvain

 

[TheAceMan1]

dixxy . . .

Sorry to get back so late.

I'm getting the impression I've misunderstood what your after:

[blue]In my database i used the 'default' Access switchboard as start-up form . . .

. . . button 1 opens form Jobs in 'Add Mode'
Button 2 opens form Jobs in 'Edit State'
each button is connected to a different macro, that each run a seperate module that open the same form 'frmJobs' in diferrent states, Add or Edit . . .

. . . [purple]I would like to be able to use these same modules, but to open a different form the same way has i open frmJobs.[/purple][/blue]

Be as specific as you can about this.

In the meantime from the code module of the switchboard post back all the code for :

[blue]Private Function HandleButtonClick(intBtn As Integer)[/blue]

See Ya! . . . . . .

 

[dixxy]

it's ok, now it's my turn to take a bit of time to reply, thanks for your interest and your help. It's greatly appriciated.

ok, here we go,

I used the Swithboard Manager to create my 'Startup' form.
So this has many button that open different 'pages' or menus, all from the same SwitchBoard Manager (Tools-Databse Utilities-SwitchBoard Manager).

On one of the menus, i have 2 buttons set up that each run a macro. Macro 1 'FormEdit', and Macro 2 'FormAdd'.

These macros are set up this way, they both 'Run Code', and the code being each a different 'Module'. Module 'OpenFormAdd' Here is the code:

Public Function OpenFormAdd()

Dim State As Integer
Dim stDocName As String
stDocName = "frmJobs"

DoCmd.OpenForm stDocName, , , , acFormAdd
'MsgBox "open in add mode"
'Forms!frmJobs.cmdSearch.Visible = False
Forms!frmJobs.Text179.Value = 0
Forms!frmJobs.cmdSearch.Visible = False
End Function

and the second module (from Macro 'FormEdit')

Public Function OpenFormEdit()

Dim State As Integer
Dim stDocName As String
stDocName = "frmJobs"

DoCmd.OpenForm stDocName, , , , acFormEdit
'MsgBox "open in add mode"
Forms!frmJobs.JobNumber.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Forms!frmJobs.cmdNew.Visible = False
Forms!frmJobs.Text179.Value = 1
Forms!frmJobs.Label95.Caption = "Search Form"
Forms!frmJobs.CustomerType.Locked = True
DoCmd.GoToRecord , , acFirst
If Forms!frmJobs.Text179 = 1 Then
Forms!frmJobs.cmdCancel.Enabled = True
End If
End Function

My question is the following.
I would like to be able to use these modules, macros, 'Techincs' to open other forms in the same fasion,(Add or Edit mode), without having to copy these macros, modules and rename them for the 'other' forms that i want to open. Still from the switchboard (but from different buttons of course).

You asked for the code that's behind the HandleButtonClick Event from that switchboard, here it is:

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

    ' 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]

        ' Open a report.
        Case conCmdOpenReport
            DoCmd.OpenReport rs![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

Thanks,

Sylvain

 

[TheAceMan1]

[blue]Roger That dixxy! . . .[/blue]

Glad to hear I was on the right track! . . .

The key to your secnario is [blue]capturing your form names of interest and redirecting program flow to your modules.[/blue]

What you need to do, is for those forms that will use the modules, go back and change the button functionality from [blue]macro[/blue] to [blue]open form (add or edit)[/blue]. This will direct opening of the forms to the common sections [green]Open a form in Add mode[/green] and [green]Open a form[/green]. It is in these sections that [blue]we'll capture the form names and redirect program flow as necessary.[/blue] Example using the section [green]Open a form in Add mode[/green]:

[blue]   [green]'Open a form in Add mode.[/green]
   Case conCmdOpenFormAdd
      If rs!Argument = "frmJobs" Or _
         rs!Argument = "FormX" Or _
         rs!Argument = "FormY" Then
         Call [purple][b]OpenFormAdd(rs!Argument)[/b][/purple]
      Else
         DoCmd.OpenForm rs![Argument], , , , acAdd [green]'All other add mode forms[/green]
      End If[/blue]

Where [blue]rs!Argument[/blue] contains the form name.

In the meantime delete the code I gave ya and copy/paste the new global functions below to the same module:

[blue]Public Function OpenFormAdd([purple][b][i]frmName[/i][/b][/purple] As String)
   Dim frm As Form
   
   DoCmd.OpenForm [purple][b][i]frmName[/i][/b][/purple], , , , acFormAdd
   DoEvents
   Set frm = Forms([purple][b][i]frmName[/i][/b][/purple])
   
   If frmName = "[purple][b]frmJobs[/b][/purple]" Then
      frm.Text179.Value = 0
      frm.cmdSearch.Visible = False
   ElseIf frmName = "[purple][b]FormX[/b][/purple]" Then
      [green]'Setup for FormX[/green]
      frm.TextboxName = 22
      frm.LabelName.Caption = "TheAceMan1"
   ElseIf frmName = "[purple][b]FormY[/b][/purple]" Then
      [green]'Setup for FormY[/green]
   End If
   
   Set frm = Nothing
   
End Function

Public Function OpenFormEdit([purple][b][i]frmName[/i][/b][/purple] As String)
   Dim frm As Form
   
   DoCmd.OpenForm [purple][b][i]frmName[/i][/b][/purple], , , , acFormEdit
   DoEvents
   Set frm = Forms([purple][b][i]frmName[/i][/b][/purple])
   
   If frmName = "[purple][b]frmJobs[/b][/purple]" Then
      frm.JobNumber.SetFocus
      DoCmd.RunCommand acCmdFind
      frm.cmdNew.Visible = False
      frm.Text179.Value = 1
      frm.Label95.Caption = "Search Form"
      frm.CustomerType.Locked = True
      DoCmd.GoToRecord , , acFirst
      If frm.Text179 = 1 Then frm.cmdCancel.Enabled = True
   ElseIf frmName = "[purple][b]FormX[/b][/purple]" Then
      [green]'Setup for FormX[/green]
      frm.TextboxName.Value = 19
   ElseIf frmName = "[purple][b]FormY[/b][/purple]" Then
      [green]'Setup for FormY[/green]
      frm.TextboxName.Value = 101
   End If
   
   Set frm = Nothing
   
End Function[/blue]

I setup the functions with two additional dummies [blue]FormX & FormY[/blue] to give you the premise under which you add additional forms. I believe its apparent.

The only thing left uncovered is getting the names of those forms for detection. You indicated earlier a problem with this. If you don't know the names let me know. We'll have to dig them out. If you do know them, post the same back.

BTW: after you change the functionality of those buttons to Open Form, post back the new [blue]HandleButtonClick[/blue] function as well.

We'll continue from here! . . .

See Ya! . . . . . .

 

[dixxy]

Hi again,

Thanks for your code, but i am having trouble.

1. under the Public Function OpenFormAdd [/] am i supposed to substitute something for the "frmName"?

2. In the Switchboard Manager I have change the 'Command' from 'Run Macro' to 'Open Form in Add Mode', and set the form name to frmJobs

3. I have pasted your code into a new module named 'OpenAnyForm', and i have also pasted the ' 'Open a form in Add mode.' in place of the code in the 'Switchboard Module'.

Now here is the problem. After doing all of this, when i click on the buttons in my switchboard, nothing happenes.

What did i do wrong?



Thanks,

Sylvain

 

[TheAceMan1]

Hi dixxy . . .

1) No . . . the [blue]frmName[/blue] is supplied thru lookup by the function [blue]HandleButtonClick[/blue]. The hilighting in the code was just for clarity.

Post back this function and the names of the forms that will use the module code. (I believe this is what I asked for!).

See Ya! . . . . . .

 

[dixxy]

sorry....here is the code:

new Module named OpenAnyForm:

Option Compare Database

Public Function OpenFormAdd(frmName As String)
   Dim frm As Form
   
   DoCmd.OpenForm frmName, , , , acFormAdd
   DoEvents
   Set frm = Forms(frmName)
   
   If frmName = "frmJobs" Then
      frm.Text179.Value = 0
      frm.cmdSearch.Visible = False
   ElseIf frmName = "frmAddNewInventory" Then
      'Setup for FormX
      frm.TextboxName = 22
      frm.LabelName.Caption = "TheAceMan1"
'   ElseIf frmName = "FormY" Then  'will use later
      'Setup for FormY
   End If
   
   Set frm = Nothing
   
End Function

Public Function OpenFormEdit(frmName As String)
   Dim frm As Form
   
   DoCmd.OpenForm frmName, , , , acFormEdit
   DoEvents
   Set frm = Forms(frmName)
   
   If frmName = "frmJobs" Then
      frm.JobNumber.SetFocus
      DoCmd.RunCommand acCmdFind
      frm.cmdNew.Visible = False
      frm.Text179.Value = 1
      frm.Label95.Caption = "Search Form"
      frm.CustomerType.Locked = True
      DoCmd.GoToRecord , , acFirst
      If frm.Text179 = 1 Then frm.cmdCancel.Enabled = True
   ElseIf frmName = "frmAddNewInventory" Then
      'Setup for FormX
      frm.TextboxName.Value = 19
'   ElseIf frmName = "FormY" Then  'will use later
      'Setup for FormY
'      frm.TextboxName.Value = 101
   End If
   
   Set frm = Nothing
   
End Function

and the HandleButtonClick Event of the switchboard module.

' 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

    ' 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
      If rs!Argument = "frmJobs" Or _
         rs!Argument = "frmAddNewInventory" Then ' Or _
'         rs!Argument = "FormY" Then
         Call OpenFormAdd(rs!Argument)
      Else
         DoCmd.OpenForm rs![Argument], , , , acAdd 'All other add mode forms
      End If
            
'        ' Open a form in Add mode.
'          original code
'        Case conCmdOpenFormAdd
'            DoCmd.OpenForm rs![Argument], , , , acAdd
'          original code ends

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

        ' Open a report.
        Case conCmdOpenReport
            DoCmd.OpenReport rs![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

From my previous post, did i set up the Switchboard Manager properly #2?

Thanks,

Sylvain

 

[TheAceMan1]

OK dixxy . . .

Code looks fine as you've posted (BTW: I've simulated this with no problem).

Did you remove your origional two modules (can't have two functions with the same name)? If not make it so and test again.

In the meantime paste the following in the same module as the code (for testing):

[blue]Public Sub SwitchPrint()
   Dim db As DAO.Database, rst As DAO.Recordset, DQ As String
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("Switchboard Items", dbOpenDynaset)
   DQ = """"
   
   Do Until rst.EOF
      Debug.Print DQ & rst!SwitchboardID & DQ & "   " & _
                  DQ & rst!ItemNumber & DQ & "   " & _
                  DQ & rst!ItemText & DQ & "   " & _
                  DQ & rst!Command & DQ & "   " & _
                  DQ & rst!Argument & DQ
      rst.MoveNext
   Loop
   
   Set rst = Nothing
   Set db = Nothing
   
End Sub[/blue]

Open the immediate window

, enter [blue]Call SwitchPrint[/blue] and hit enter. Postback whats printed . . .

See Ya! . . . . . .

 

[dixxy]

Hi Ace,

no i hadn't taken out the other two modules, now i did.

Here is the result form the Immediate window:

Call SwitchPrint 
"1"   "0"   "Main Switchboard"   ""   "Default"
"1"   "1"   "Jobs/Estimates"   "1"   "8"
"1"   "2"   "Schedules"   "1"   "11"
"1"   "3"   "Production"   "1"   "12"
"1"   "4"   "Inventory"   "1"   "13"
"1"   "5"   "Sales Reports"   "1"   "2"
"1"   "6"   "Control Panel"   "1"   "9"
"1"   "7"   "What's New"   "4"   "rptlDataBaseVersion"
"1"   "8"   "Exit"   "7"   "mcrQuit"
"2"   "0"   "Reports"   "0"   ""
"2"   "1"   "Preview Sales Report"   "3"   "frmReportSelection"
"2"   "2"   "Return to Previous Menu"   "1"   "1"
"3"   "0"   "Door Inventory"   "0"   ""
"3"   "1"   "Open Door Inventory"   "3"   "frmDoors"
"3"   "2"   "Return to Previous Menu"   "1"   "1"
"8"   "0"   "Jobs"   "0"   ""
"8"   "1"   "Search Jobs"   "3"   "frmJobs"
"8"   "2"   "Enter New Job"   "2"   "frmJobs"
"8"   "3"   "Create/View An Estimate"   "3"   "frmEstimates"
"8"   "4"   "Return to Previous Menu"   "1"   "1"
"9"   "0"   "Control Panel"   "0"   ""
"9"   "1"   "View/Edit Customers"   "3"   "frmEditClients"
"9"   "2"   "View/Edit Prospects"   "3"   "frmEditProspects"
"9"   "3"   "View/Edit Sales Personnel"   "3"   "frmEditSalesMan"
"9"   "4"   "View/Edit Terms"   "3"   "frmEditTerms"
"9"   "5"   "View Back Order/Extras Summary Reports"   "3"   "frmExtraBOSummary"
"9"   "6"   "Manufacturing Standards"   "1"   "10"
"9"   "7"   "Return To Previous Menu"   "1"   "1"
"10"   "0"   "Manufacturing Standards"   "0"   ""
"10"   "1"   "View/Edit Handles"   "3"   "frmEditHandles"
"10"   "2"   "View/Edit Finish/Factors"   "3"   "frmEditFinish"
"10"   "3"   "View/Edit CounterTops Styles"   "3"   "frmEditCounter"
"10"   "4"   "View/Edit CounterTop Colors"   "3"   "frmEditCounterColor"
"10"   "5"   "View/Edit Door Model/Materials Pricing"   "3"   "frmEditModelNew"
"10"   "6"   "View/Edit Cabinets - Pricing"   "3"   "frmEditSheet1"
"10"   "7"   "View/Edit Accessories - Pricing"   "3"   "frmEditStockAcc"
"10"   "8"   "Return to Previous Menu"   "1"   "9"
"11"   "0"   "Schedules"   "0"   ""
"11"   "1"   "Production"   "2"   "frmSchedule"
"11"   "2"   "Back Order/Extras"   "3"   "frmSearchExtras"
"11"   "3"   "Return to Previous Menu"   "1"   "1"
"12"   "0"   "Production"   "0"   ""
"12"   "1"   "Print Door order"   "3"   "frmDoorOrderSelection"
"12"   "2"   "Parts Reports"   "3"   "frmCabinet Selection"
"12"   "3"   "Offcut Manager"   "3"   "frmOffCutManager"
"12"   "4"   "Job Sheet Count"   "4"   "NNqreMatSum"
"12"   "5"   "Return to Previous Menu"   "1"   "1"
"13"   "0"   "Inventory"   "0"   ""
"13"   "1"   "CounterTops"   "3"   "frmStockTops"
"13"   "2"   "Mouldings"   "3"   "frmStockMouldings"
"13"   "3"   "Return to Previous Menu"   "1"   "1"

Thanks,

Sylvain

 

[dixxy]

VICTORY!!!!

It's working now!!! TheAceMan1 you the MAN!! .

For the sack of conversation here is the code working.
For the OpenAnyForm Module

Option Compare Database

Public Function OpenFormAdd(frmName As String)
   Dim frm As Form
   
   DoCmd.OpenForm frmName, , , , acFormAdd
   DoEvents
   Set frm = Forms(frmName)
   
   If frmName = "frmJobs" Then
   MsgBox "add mode"
      frm.Text179.Value = 0
      frm.cmdSearch.Visible = False
   ElseIf frmName = "frmAddNewInventory" Then
      'Setup for FormX
'      frm.TextboxName = 22
'      frm.LabelName.Caption = "TheAceMan1"
      MsgBox "Inventory in ADD Mode"
   ElseIf frmName = "FormY" Then  'will use later
      'Setup for FormY
   End If
   
   Set frm = Nothing
   
End Function

Public Function OpenFormEdit(frmName As String)
   Dim frm As Form
   
   DoCmd.OpenForm frmName, , , , acFormEdit
   DoEvents
   Set frm = Forms(frmName)
   
   If frmName = "frmJobs" Then
   MsgBox "edit Mode"
      frm.JobNumber.SetFocus
      DoCmd.RunCommand acCmdFind
      frm.cmdNew.Visible = False
      frm.Text179.Value = 1
      frm.Label95.Caption = "Search Form"
      frm.CustomerType.Locked = True
      DoCmd.GoToRecord , , acFirst
      If frm.Text179 = 1 Then frm.cmdCancel.Enabled = True
   ElseIf frmName = "frmAddNewInventory" Then
        MsgBox "Inventory in Edit Mode"
      'Setup for FormX
'      frm.TextboxName.Value = 19
   ElseIf frmName = "FormY" Then  'will use later
      'Setup for FormY
      frm.TextboxName.Value = 101
   End If
   
   Set frm = Nothing
   
End Function

Public Sub SwitchPrint()
   Dim db As DAO.Database, rst As DAO.Recordset, DQ As String
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("Switchboard Items", dbOpenDynaset)
   DQ = """"
   
   Do Until rst.EOF
      Debug.Print DQ & rst!SwitchboardID & DQ & "   " & _
                  DQ & rst!ItemNumber & DQ & "   " & _
                  DQ & rst!ItemText & DQ & "   " & _
                  DQ & rst!Command & DQ & "   " & _
                  DQ & rst!Argument & DQ
      rst.MoveNext
   Loop
   
   Set rst = Nothing
   Set db = Nothing
   
End Sub

And here is the HandleButtonClick Event of the switchboard Module

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

    ' 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
      If rs!Argument = "frmJobs" Or _
         rs!Argument = "frmAddNewInventory" Then  'Or _
'         rs!Argument = "FormY" Then
         Call OpenFormAdd(rs!Argument)
      Else
         DoCmd.OpenForm rs![Argument], , , , acAdd 'All other add mode forms
      End If
            
'        ' Open a form in Add mode.
'        Case conCmdOpenFormAdd
'            DoCmd.OpenForm rs![Argument], , , , acAdd
'
        ' Open a form.
   Case conCmdOpenFormBrowse
      If rs!Argument = "frmJobs" Or _
         rs!Argument = "frmAddNewInventory" Then  'Or _
'         rs!Argument = "FormY" Then
         Call OpenFormEdit(rs!Argument)
      Else
         DoCmd.OpenForm rs![Argument], , , , acAdd 'All other add mode forms
      End If


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

        ' Open a report.
        Case conCmdOpenReport
            DoCmd.OpenReport rs![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

Thank you very much for your time on this, now i can keep expanding this logic for different forms, it's grest!!!




Thanks,

Sylvain

 

[TheAceMan1]

dixxy . . .

So far all is well. Were gonna have to debug!.
[ol][li]Goto the [blue]'Open a form in Add mode[/blue] section of [blue]HandleButtonClick[/blue] and set a breakpoint on the line:

[blue]Case conCmdOpenFormAdd[/blue]

[/li]
[li]Close out, open the switchboard, navigate to and select the button for [blue]Enter New Job[/blue].[/li]
[li]The code should break where you set it. (Let me know if it doesn't break).[/li]
[li]Hover the mouse over [blue]rs!Argument[/blue]. The Name of the form should pop-up.[/li]
[li]From here single step thru the code using the

toolbar button.
You just want to follow the program flow and see that [blue]OpenFormAdd[/blue] is executed.[/li][/ol]

See Ya! . . . . . .

 

[dixxy]

thank you for hanging in there,

first off, i would like to let you know that i'm on slipery ground here. I am not that deep in VBA, and these procedures your asking me is a first for me.

So i set the break point (there's a red dot beside the line, and the entire line is highlighted in RED, correct?)

I closed the VBS editor, and also closed the switchboard (database still open), the re-open the switchboard, and clicked on the Enter new Job button. The form opens, and i get me MsgBox "Open in ADD Mode" and that's it.

I hope this is the information your looking for.

Let me know if it's not.



Thanks,

Sylvain

 

[dixxy]

TheAceMan,

I hope i didn't scrare you off with my last post...I just meant that I had never done those procedures, didn't say that i couldn't do it.... :-(

Tried it several time, different ways, and the code those not break.

Hope to hear from you soon,



Thanks,

Sylvain

 

[TheAceMan1]

dixxy . . .

I somehow missed your victory post! Id its working . . . Great!

See Ya! . . . . . .