Hi guys,

Not to follow the crowd :), but I also had difficulty in creating a switchboard. I want to create to create a switchboard for my application, in which that switchboard would behave like a framed website. So everytime a users click the main menu A on the top, the sub menu details would appear below it (in the middle). The same thing when main menu B was clicked, its sub menu details would appear below it ( in the middle), replacing the sub menu details for main menu A. Is it possible for VBA?

The second problem was populating a list of report on the listbox according to certain main menu. I can populate the list of all reports within the listbox...with this VBA coding:

'lstReports was the listbox name
Private Sub Form_Load()
    Dim objAO As AccessObject
    Dim objCP As Object
    Dim strValues As String

    Set objCP = Application.CurrentProject
    lstReports.RowSourceType = "Value List"

    For Each objAO In objCP.AllReports
        Me.lstReports.AddItem (objAO.Name)
    Next objAO
End Sub

Private Sub ProcessReport(intAction As Integer)
    If Not IsNull(lstReports) Then
        DoCmd.OpenReport lstReports, intAction
    End If
End Sub

But it would confused the users as they would see too many reports....So I want to list only certain reports related to the main menu....I tried to just put the reports name on this line:

Me.lstReports.AddItem ("Report A") & ";"
Me.lstReports.AddItem ("Report B")

Unfortunately, it didn't work like what I want.

Anyone can help me with these 2 problems? I am still learning about VBA.

Million thanks in advanced.

Cheers,

Fehrer

Fehrer:

If you have the associated reports with a specific prefix, lets say
rptMenu or rptMlist or whatever to let you identify that a group of reports belongs to the menu, will be easy to select them.
Supose the following names
1.rptMenuList
2.rptMenuTxt
3.rptMenuCbo


good luck

Estuardo

Hi Estuardo,

Thanks for ur advice. I tried to modify my coding based on yours, but it didn't work, message box saying Expected array.

Supposed the available reports on Reports objects are
1.rptMenuList
2.rptMenuTxt
3.rptMenuCbo

The ones that I want to populate within lstReport listbox in frm_CustomersMenu are rptMenuList dan rptMenuCbo.
Here is my VBA coding...

Private Sub Form_Load()
    Dim objAO As AccessObject
    Dim objCP As Object
    Dim intStr As String

    intStr = Array("rptMenuList", "rptMenuCbo")

    Set objCP = Application.CurrentProject
    lstReports.RowSourceType = "Value List"

    For Each objAO In objCP.AllReports
        If intStr(objAO.Name, "rptMenuList") Then
            Me.lstReports.AddItem (objAO.Name)
        End If
        If intStr(objAO.Name, "rptMenuCbo") Then
            Me.lstReports.AddItem (objAO.Name)
        End If
    Next objAO
End Sub

Private Sub ProcessReport(intAction As Integer)
    If Not IsNull(lstReports) Then
        DoCmd.OpenReport lstReports, intAction
    End If
End Sub



Many thanks.

Cheers,

Fehrer

Fehrer, that is possible to do. You would open main form that had a subform on it. Size the subform any way you want. Then create many subforms that take up the exact same size as the space allowed on the main form. When the user clicks any button on the main form then you change the subform's source object to any of the save subforms you created.

For example, on the main form you have three buttons: cmdButtonA, cmdButtonB, and cmdButtonC. You created three subforms: sfrA, sfrB, sfrC. Then on each of the button's On Click event you put this:
Write the same On Click event for buttons B & C, too; however, substitute the A with B and C, respectively.




Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Hi Serano,

Thanks a lot for the advise. Very clear explanation and logical. I have tried it, but I confused with sfrMysubform word, what does it refer to? Is it the main form name, method or properties of the subform? When I click the button for subform A (for example), message box saying "Method or data member not found" comes up. Even after I changed sfrMysubform with my main form name, the same message still appear.

Regards,

Fehrer

Sorry about that... sfrMysubform is just a fake name I used to call your SUBform (not the main form) because I didn't know its real name. Substitute the name of your subform there.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Let me clarify that. You have a main form with a name. I'll make one up for now: frmMyMainForm. Suppose you have three subforms: sfrA, sfrB, and sfrC. On the main form you have a control object where your subform will sit. The name of this control object is (say) sfrMysubform. The source object for sfrMysubform will be sfrA, sfrB, or sfrC depending on which button the user clicks.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Hi Serano,

Thx for your patience. I understand ur point, but I don't know what u mean by control object. Even, I can't find it on the properties. Which one the control object is?

Cheers,

Fehrer

Everything you place on a form is called a control object: a button, a list box, a combo box, a text box, a subform, an option group, etc.

When you place a subform on a form, you're placing a control object on the form. Usually, the wizard will turn on and ask you which form you would like use as the source object for the subform. You can say it's (in my example) sfrA. When the user clicks Button A, B, or C, the On Click event will change the source object to something else (sfrB or sfrC, for example).


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Hi Serano,

Oh my God, :). OK, got it, thanks, very much appreciated. I have done what u said, with this event click for each button,

Public Sub cmdOrdersMenuSB_Click()
    Me.sfrMysubform1.SourceObject = "Orders_SubForm"
End Sub


Public Sub cmdCustomersMenuSB_Click()
    Me.sfrMysubform.SourceObject = "Customers_SubForm"
End Sub

However, when I click both the button, the subform did not appear. It only show the last subform created. Anything that I missed?

Thanks