Calling Subroutines across Forms (non-module)

• "frmCalendar" - form where calendar control named "axcCalendar" is placed.
  
• Procedure below is placed in public module.
  
  Expand|Select|Wrap|Line Numbers

  1. Public Sub OpenCalendar(ByRef strSQL As String, ByRef txtDate As TextBox)
  2.  
  3.     Dim frmCalendar As Form
  4.  
  5.     If IsNull(txtDate) Then
  6.         txtDate.Value = Date
  7.     Else
  8.         DoCmd.OpenForm "frmCalendar"
  9.         Set frmCalendar = Forms!frmCalendar
  10.         With frmCalendar
  11.             .RecordSource = strSQL
  12.             .Controls!axcCalendar.ControlSource = txtDate.ControlSource
  13.         End With
  14.         Set frmCalendar = Nothing
  15.     End If
  16.  
  17. End Sub
  18.  
• Now in a form.
  • Form.RecordSource = tblTable
  • dteDate.ControlSource = tblTable.dteDate
  • keyID is tblTable PK(Numeric)
  First argument passed into "OpenCalendar" function is SQL statement retrieving single record (filtered by PK) with one single field.
  
  Expand|Select|Wrap|Line Numbers

  1. Private Sub dteDate_DblClick(Cancel As Integer)
  2.     OpenCalendar "SELECT dteDate FROM tblTable WHERE keyID=" _
  3.          & Me.keyID & ";", Me.dteDate
  4. End Sub
  5.  

I don't quite think I explained well enough. I have most of that already. My calendar pops up, can take a date, places the date in the right place. Now, after the date is placed, I need the original form to run a subroutine.

Given the fact that I'm storing the originating form in a global module variable, how do I call the sub I need from that form automatically after the date is placed? There are more things needed past just record selection after this event. Unfortunately, none of the On Event possibilities seem to activate after the date is placed in the textbox.

Sorry, I think I missed something there. What rather simple class? Is there a link I'm missing? So confused...

At the moment, I added another button entitled "Submit" which the user can click to manually call the sub. It is, however, an unecesary extra step, as it should just run after the date is placed. -_-

I'm familiar with object oriented coding from back when I took a java Data Structures course, but VBA seems to make most of my programming knowledge useless. =(

Events:
AfterUpdate - event raising when the TextField was updated, here you can put code you needed

Expand|Select|Wrap|Line Numbers

1. Private Sub objDate_AfterUpdate
2.        'here you can place any code you like
3. End Sub
4.  
5.  

Does this make a sense?

Hey Fishval-

Yes it makes sense. It makes so much sense that it was the first thing I tried, but to no avail. It seems that after my popup calendar places the date I want in the textbox I want, the AfterUpdate event is *not* triggered. Rather annoying, to say the least. So, I"m trying to figure out how to trigger it, either by doing something differently, or by having my calendar module find a way to call the AfterUpdate subroutine directly, given the form of interest (keep in mind this calendar is used across several forms).

ideas?

• First of all I'd like to point out that form modules are actually class modules. You can easily check that opening ObjectBrowser and seeing (in bold BTW ;)) classes like Form_<form name>. BTW object of [Forms] collection are instances of these classes. You can easily check that to:
  • open form e.g. [frmCalendar]
  • in VBA immediate window enter
    Expand|Select|Wrap|Line Numbers

    1. ? typename(Forms!frmCalendar)
  • you will receive Form_frmCalendar
• Having clarified this, we should ask ourselves: why not to take advantage of OOP and to write our own Properties/Methods/Events for this class?
• The code below shows how to declare event for particular form class
  
  [frmCalendar] module:
  
  Expand|Select|Wrap|Line Numbers

  1. ' declare event just like procedure
  2. Event DateSubmitted(ByVal dteInput As Date)
  3.  
  4. Private Sub btnOk_Click()
  5.     ' fire event when [Ok] button was clicked
  6.     RaiseEvent DateSubmitted(Me.axcCalendar.Value)
  7.     DoCmd.Close
  8. End Sub
  9.  
• now you can open ObjectBrowser and see that Form_frmCalendar class has event DateSubmitted
  
  
  [frmSomeForm] module:
  
  Expand|Select|Wrap|Line Numbers

  1. ' to handle the Form_frmCalendar event we need to declare global variable
  2. ' in the following manner
  3. Dim WithEvents frmCal As Form_frmCalendar
  4. ' now friendly VBA IDE allows you to choose frmCal from combobox
  5. ' where all other event sources (Form, controls) are located 
  6.  
  7. Private Sub btnOpenCalendar_Click()
  8.     'opens calendar form in normal window mode to let the code execution to proceed
  9.     DoCmd.OpenForm "frmCalendar"
  10.     ' set reference to opened form, from now frmCal will trap events fired in frmCalendar
  11.     Set frmCal = Forms!frmCalendar
  12.     ' set frmCalendar window mode to Dialog
  13.     frmCal.Modal = True
  14. End Sub
  15.  
  16. ' this is the handler of DateSubmitted event
  17. ' you can write here your useful code
  18. Private Sub frmCal_DateSubmitted(ByVal dteInput As Date)
  19.     MsgBox dteInput
  20.     Set frmCal = Nothing
  21. End Sub
  22.  

ah yes I see! getting much closer, but still a bit off.

so the form can snag the calendar value, AND I have free reign as to events afterwards now. However, I'm still shaky with OOP and getting the value into the right textbox (as there is both a start and end date box).

Can I just set a global variable Dim txtPlace as TextBox, and then just set it to my place of interest when the button for that box is clicked? In other words, in startDate_Click() have:
txtPlace = "myStartDateTextBox"
and then on endDate_Click() have
txtPlace = "myEndDateTextBox"
?

Is there an easier way or does that reasoning sound ok? Thanks for your expertise and all your willingness to help!