Calling Subroutines across Forms (non-module)

[isoquin]

Really, i've looked for the answer, but I must be missing some syntax somewhere...

I have a popup calendar which is to be used across several forms. Each form will need the calendar to automatically enter the selected date into the designated textbox (this part works beautifully already) and then update the filter on a continuous form to include that date range.

Now, I have several forms which will be in need of this popup calendar, so I'm trying to make it as general as possible so it can handle all of them. I set things up with a module, allowing for the following global variables:

Public target As TextBox
Public formName As Form

In the calendar form, I have the following:

Private Sub OkCal_Click()
        
    target = Me.Calendar9.Value
    target.SetFocus
    DoCmd.Close , , acSaveNo
    ' [what goes here?]

End Sub

So this clearly isn't right, but hopefully it will give you a sense of what I want to do in the [what goes here] space:
Call formName.afterC alendarEvents()
also doesn't work: Call formName.Form.a fterCalendarEve nts()

Each of several forms that will be using this popup calendar will need to have different "afterCalendarE vents" actions, and so I don't feel I can put it as a module. I'd like to try to access individual "afterCalendarE vents" subs in each individual form.

Does this make sense? What am I missing here? Any help is greatly appreciated. Thank you in advance.

[FishVal]

Hi, isoquin.

Here is one possible solution. When user dblclicks on a textbox it is being filled with current date when it is empty or otherwise calendar popup is opened .

• "frmCalenda r" - form where calendar control named "axcCalenda r" is placed.
  
• Procedure below is placed in public module.
  

  Public Sub OpenCalendar(ByRef strSQL As String, ByRef txtDate As TextBox)
  
      Dim frmCalendar As Form
      
      If IsNull(txtDate) Then
          txtDate.Value = Date
      Else
          DoCmd.OpenForm "frmCalendar"
          Set frmCalendar = Forms!frmCalendar
          With frmCalendar
              .RecordSource = strSQL
              .Controls!axcCalendar.ControlSource = txtDate.ControlSource
          End With
          Set frmCalendar = Nothing
      End If
  
  End Sub

• Now in a form.
  • Form.RecordSour ce = tblTable
  • dteDate.Control Source = tblTable.dteDat e
  • keyID is tblTable PK(Numeric)
  
  First argument passed into "OpenCalend ar" function is SQL statement retrieving single record (filtered by PK) with one single field.
  

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

[isoquin]

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.

[FishVal]

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.

Hi, there.

The most natural and commom way to do it, to my mind, is to write Class that raises events.
Then to instantiate in form module object of the class and handle it's events.

Hope you have some sence of object oriented programming. If no, start to learn it with this rather simple class.

Good luck.

P.S. I'll be glad to help you with coding.

[isoquin]

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. =(

[FishVal]

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. =(

Hi, isoquin.

Maybe I'm missing something.
You can call public function/sub in any module as well as form module too using the following syntax:

Call ModuleName.Proc Name ....
or simply
ModuleName.Proc Name ....

But you say that you want a universal code.
I guess this means you want that a procedure specific for a particular Textfield will be invoked as soon as the Textfield will be updated with Calendar form.

To my mind the most natural way to do it is to write some class implementing at least

Properties:
Textfield - target textfield r/w

Methods:
Show - opens calendar form

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

Let us say it's name is "CalendarField" .

Now in form module you can write something like this

'Global object of class CalendarField declared with events
Dim WithEvents objDate As CalendarField

Private Sub Form_Open(Cancel As Integer)
       Set objDate = New CalendarField
       Set objDate.TextField = Me.dteDate
End Sub

Private Sub Form_Close()
       Set objDate = Nothing
End Sub

'let us say the calendar form is opened at DblClick on a date textbox
'BTW the following proc is actually not needed as soon as CalendarField class
'can itself catch events of the textbox passed to it via TextField property
Private Sub dteDate_DblClick
       objDate.Show
End Sub

Private Sub objDate_AfterUpdate
       'here you can place any code you like
End Sub

Does this make a sense?

[isoquin]

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

Private Sub objDate_AfterUpdate
       'here you can place any code you like
End Sub

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?

[FishVal]

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?

Hi, isoquin.

Not sure we are talking about the same, but let this RIP. ;)
Since my last post I've figured simple and nice solution, just was waiting for you answer to make sure you are still interested.

Let us say you have some form (let us say [frmSomeForm]) which opens calendar form (let us say [frmCalendar]).

[frmCalendar] module:

Event DateSubmitted(ByVal dteInput As Date)

Private Sub btnOk_Click()
    RaiseEvent DateSubmitted(Me.axcCalendar.Value)
    DoCmd.Close
End Sub

[frmSomeForm] module:

Dim WithEvents frmCal As Form_frmCalendar

Private Sub btnOpenCalendar_Click()
    DoCmd.OpenForm "frmCalendar"
    Set frmCal = Forms!frmCalendar
    frmCal.Modal = True
End Sub

Private Sub frmCal_DateSubmitted(ByVal dteInput As Date)
    MsgBox dteInput
    Set frmCal = Nothing
End Sub

Good luck.

[isoquin]

I don't think I'm following how that will trigger the AfterUpdate function. Sorry for being a bit slow, but could you please explain the code? If it helps at all, I can provide my own:


frmSomeForm has textBox named "SomeText" where
onClick: =linkCal([SomeText],Forms!frmSomeF orm,"Start Date")

Complementary, I have a module "getDate" with the following:

Option Compare Database
Option Explicit

Public target As TextBox
Public formName As Form

Public Function linkCal(place As TextBox, inForm As Form, Optional identify As String)
    Set target = place
    Set formName = inForm
    DoCmd.OpenForm "Calendar", windowmode:=acDialog, OpenArgs:=identify
End Function

Then in the calendar form itself:

Private Sub OkCal_Click()
    target = Me.Calendar9.Value
    
    target.SetFocus
    DoCmd.Close , , acSaveNo
End Sub

Why isn't this tripping AfterUpdate after it fills in a date?

[FishVal]

Hi, isoquin.

I'll try to explain how does the code works.

• 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

    ? typename(Forms!frmCalendar)

  • you will receive Form_frmCalenda r
• 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:
  [code=vb]
  ' declare event just like procedure
  Event DateSubmitted(B yVal dteInput As Date)
  
  Private Sub btnOk_Click()
  ' fire event when [Ok] button was clicked
  RaiseEvent DateSubmitted(M e.axcCalendar.V alue)
  DoCmd.Close
  End Sub
  [/code]
  
• now you can open ObjectBrowser and see that Form_frmCalenda r class has event DateSubmitted
  
  
  [frmSomeForm] module:
  [code=vb]
  ' to handle the Form_frmCalenda r event we need to declare global variable
  ' in the following manner
  Dim WithEvents frmCal As Form_frmCalenda r
  ' now friendly VBA IDE allows you to choose frmCal from combobox
  ' where all other event sources (Form, controls) are located
  
  Private Sub btnOpenCalendar _Click()
  'opens calendar form in normal window mode to let the code execution to proceed
  DoCmd.OpenForm "frmCalenda r"
  ' set reference to opened form, from now frmCal will trap events fired in frmCalendar
  Set frmCal = Forms!frmCalend ar
  ' set frmCalendar window mode to Dialog
  frmCal.Modal = True
  End Sub
  
  ' this is the handler of DateSubmitted event
  ' you can write here your useful code
  Private Sub frmCal_DateSubm itted(ByVal dteInput As Date)
  MsgBox dteInput
  Set frmCal = Nothing
  End Sub
  [/code]

Hope this makes sence.

[isoquin]

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 = "myStartDateTex tBox"
and then on endDate_Click() have
txtPlace = "myEndDateTextB ox"
?

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

[FishVal]

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 = "myStartDateTex tBox"
and then on endDate_Click() have
txtPlace = "myEndDateTextB ox"
?

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

Hi, isoquin.

This is the question of programming style.
Personally I would prefer to avoid global variable. Despite it seems to be the most straightforward solution it can strike back in a pretty unexpected manner. ;)

So I will proceed in terms of OOP. :)
Instead of interrogation through global variable lets pass reference to a target TextBox to frmCalendar via property.
Code looks like this

[frmCalendar] module:

Private txbTargetTextBox As Access.TextBox

Event DateSubmitted(ByVal dteInput As Date)

Private Sub btnOk_Click()
    Me.TargetTextBox.Value = Me.axcCalendar.Value
    RaiseEvent DateSubmitted(Me.axcCalendar.Value)
    DoCmd.Close
End Sub

Public Property Get TargetTextBox() As Access.TextBox
    Set TargetTextBox = txbTargetTextBox
End Property

Public Property Set TargetTextBox(ByRef txbNewValue As Access.TextBox)
    Set txbTargetTextBox = txbNewValue
End Property

Private Sub Form_Close()
    Set txbTargetTextBox = Nothing
End Sub

[frmSomeForm] module:
NB: note frmCal object is declared as private unlike the previous version to avoid accidental referencing from other module

Private WithEvents frmCal As Form_frmCalendar

Private Sub dteDate1_DblClick(Cancel As Integer)
    Set frmCal = ShowCalendar(Me.dteDate1)
End Sub

Private Sub dteDate2_DblClick(Cancel As Integer)
    Set frmCal = ShowCalendar(Me.dteDate2)
End Sub

Private Sub frmCal_DateSubmitted(ByVal dteInput As Date)
    MsgBox "Date put to " & frmCal.TargetTextBox.Name
    Set frmCal = Nothing
End Sub

Some public module (let us say [mdlCalendar]):

Public Function ShowCalendar(ByRef txbTargetTextBox As Access.TextBox) _
    As Form_frmCalendar

    DoCmd.OpenForm "frmCalendar"
    Set ShowCalendar = Forms!frmCalendar
    With ShowCalendar
        Set .TargetTextBox = txbTargetTextBox
        .Modal = True
    End With
    
End Function

I hope this will cover all your needs.