Run Form Control Event from a Different Form

[Rawb]

The title says it all.



Let's say I have a form (Form1) with a Combo Box (Combo1).

How do I run Form1!Combo1's OnChange Event from Form2? Is this even possible?

[RuralGuy]

Any Procedure can be accessed from outside of the Form if you declare it Public instead of Private but I suspect there is a better way than the one you've chosen.

[Rawb]

Well, I'm trying to set up a menu system for a database that I'm working on.

I have a frmMainMenu Form with three controls on it:

• The first one is a combo box that lists all the main categories (001: Sales Order Management, 002: Scheduling, etc.).
• The second is a list box showing all the menu items within the specified category (for example: 01: Sales Order Management would show 001: View Inter-Company Orders, 002: View Third Party Orders, etc.)
• And the third control is a large text box that shows a description of the item currently selected in the list box.

I want to write a Module that will be able to update the three controls on the main form that I can run from anywhere.

Is there a better way to do this than to trigger the local events of the frmMainMenu Form controls?

The menu information is coming from a ProgramsList Table with the following format:

ProgParentID|ProgID|ProgObjName|ProgTitle|ProgDesc|ProgType
000|000|frmMainMenu|Main Menu|Return to the main menu.|Menu Item
000|001|frmMainMenu|Sales Order Management|View and manage sales orders.|Menu Item
000|002|frmMainMenu|Schedule Management|View, create, and edit the daily production schedule.|Menu Item
001|001|frmShopOrder_I|View and Edit Intercompany Orders|View, create, and edit sales order from intercompany facilities.|Form
001|002|frmShopOrder_C|View and Edit Third-Party Orders|View, create, and edit third-party sales orders.|Form

[RuralGuy]

I applaud your ambition. The Change event occurs for *every* keystroke in a control. Are you sure that is where you want your code?

[Rawb]

Hopefully, that aspect of the OnChange event shoudn't cause a problem. If it does though, I can always change it to AfterUpdate.

The Function I'm writing (trying to anyway) is going to do the following:

• You pass in the ProgID and ProgParentID of the Form/Menu Item you want to open.
• If the ProgType of that Record is Form (well, it's really a Long Integer since these Types are pulled from a ProgramList_Types Table), it opens the Form ProgObjName.
• If the ProgType of that Record is Menu Item, then it changes the combo box value of frmMainMenu to ProgID and then triggers the OnChange event of that combo box.

Assuming that making the OnChange event Public works, I still have a small concern: My current setup only allows for one level of nesting Menus (I can't have any sub-menus).

Do you know of a way to allow for nesting Menus n levels deep?

[RuralGuy]

Sorry, not really.

[Rawb]

OK, I've changed the combo box's OnChange event to Public but I'm still getting the following error:

Run-time error '438':

Object doesn't support this property or method

Here's my code. The error happens on line 18:

Function ListSelect(ProgramID As String, ProgramParentID As String) As Boolean
  'On Error GoTo Error_ListSelect

  Dim db As DAO.Database

  Dim rstProgram As DAO.Recordset

  Set db = CurrentDb()

  Set rstProgram = db.OpenRecordset("SELECT * FROM ProgramList WHERE [ProgID]='" & ProgramID & "' AND [ProgParentID]='" & ProgramParentID & "'", dbOpenForwardOnly)

  If rstProgram.RecordCount = 0 Then
    MsgBox "Program with ID '" & ProgramID & "' could not be found."
  Else
    If rstProgram("ProgType") = 1 Then
      Forms!frmMainMenu!Category_Combo = ProgramParentID

      Forms!frmMainMenu!Category_Combo.OnChange
    ElseIf rstProgram("ProgType") = 2 Then
      DoCmd.OpenQuery rstProgram("ProgObjName")
    ElseIf rstProgram("ProgType") = 3 Then
      DoCmd.OpenForm rstProgram("ProgObjName")
    ElseIf rstProgram("ProgType") = 4 Then
      DoCmd.OpenReport rstProgram("ProgObjName"), acViewPreview
      DoCmd.Maximize
    ElseIf rstProgram("ProgType") = 5 Then
      Shell rstProgram("ProgObjName"), vbNormalFocus
    Else
      MsgBox "Unknown Object Type!"
    End If
  End If

  ListSelect = True

FunctionClosing:
  rstProgram.Close

  Set rstProgram = Nothing

  Set db = Nothing

  Exit Function

Error_ListSelect:
  ListSelect = False

  MsgBox Err.Number & ": " & Err.Description

  Resume FunctionClosing
End Function

[RuralGuy]

Why not have the OnChange event call a Public Procedure and then *you* call that same procedure from outside. Setting a procedure as Public does make that procedure a Method of the form so you can invoke it the way you have without using the Call KeyWord.

[Rawb]

Hooray!

You made me realize that the OnChange event was just forcing the list box to requery. So, after changing the ListSelect Function to just do that itself, everything works!

[RuralGuy]

Beautiful. Learning is a marvelous thing to witness.