A Puzzler About Calling a Form's Public Function to Requery Subforms

[Doug Maier]

I'm stumped by this problem and hoping someone can help.



Is there a simple reason that a form's public procedure behaves differently when called from within the form's module vs when it's called from another module?

I have a master form (with account info) with an embedded subform showing related financial transactions. I've created a 'Refresh' button on the master form that calls the RefreshAll() procedure which does the following:
1) Memorizes the Account ID
2) Requeries the master to include possible changes/additions from other users
3) Uses a cloned copy of the recordset to 're-find' the account we started with

This also has the bonus effect of requerying the embedded subform to display any additional ledger entries. All this works when I call RefreshAll() from the form's module. (cmdRefresh_Click()).

Here's where the puzzle begins:
I also have other processes run from a button bar that need to perform the same steps at the end so the user can see the additions/changes, especially in the ledger subform.

But calling the RefreshAll() procedure from anywhere outside of he form's module doesn't show changes to the ledger subform. And I cannot figure out why.

Any ideas why the same code behave differently when called from the form module vs. called from an outside module?

Any hints, tips, solutions would be most welcome ,

-- Doug
PS: Here's the code for RefreshAll()

Public Function RefreshAll() As Boolean
On Error GoTo PROC_ERR:
   Dim ctlPrev       As Control            ' Which controll were we on before we started this?
   Dim fCanSetFocus  As Boolean            ' Can that control support the .SetFocus method?
   Dim rst           As DAO.Recordset      ' Clone of the form's recordset
   Dim strAcctID     As String             ' Account ID'
   Dim strMsg        As String             ' Message text

   Dim strActiveControlName   As String    ' What's the name of the active control (for testing purposes)
   Dim strPreviousControlName As String    ' Name of the previous control

   ' Error raised when the form doesn't have a previous control
   ' NOTE: This happens when the focus hasn't yet moved from one control to another.
   Const cErrObjectClosed As Long = 2467

   ' Memorize the Account's ID
   strAcctID = Me.AcctID.Value

' Memorize the name of the active control (for testing purposes)
strActiveControlName = Me.ActiveControl.NAME


   ' We'd like to put the cursor back where we found it when we're all done.
   ' Let's find out if we can do that.


   ' Try to read the name of the previous control, if we can.
   On Error Resume Next
   strPreviousControlName = Screen.PreviousControl.NAME

   ' Check to see if reading the control name triggered any errors.
   Select Case Err.Number
      Case cErrObjectClosed:
         ' There isn't a previous control because we haven't moved
         ' from one control to another yet.
         ' Memorize the 'active' control instead.
         strPreviousControlName = Me.ActiveControl.NAME
         Set ctlPrev = Me.ActiveControl

      Case 0:
         ' Screen.PreviousControl is valid.
         ' Memorize which control we came from.
         Set ctlPrev = Screen.PreviousControl

      Case Else:
         ' Unexpected error.  
         ' Hand it off to the main error handling section
         GoTo PROC_ERR:
    End Select

   ' Reset the error handling
   On Error GoTo PROC_ERR:


   ' Determine if the previous control has the .SetFocus method
   Select Case TypeName(ctlPrev)
      Case "TextBox", "ComboBox", "CheckBox", "Subform":
         ' Only certain types of controls have the .SetFocus method.
         fCanSetFocus = True

      Case Else:
         ' The other controls types do not have it.
         fCanSetFocus = False
   End Select


   ' Freeze the screen to hide the magic.
   DoCmd.Hourglass True
   DoCmd.Echo False

   ' Requery the form in case other users have added new Customers
   Me.Requery

   ' Clone the form's recordset so we can perform a search
   Set rst = Me.Recordset.Clone

   With rst
      '' NOTE: The MoveFirst, MoveLast were an attempt to 
      '' encourage Access to reload the subform.
      '' Didn't work.
      '' Move the form to the last record
      'rst.MoveLast
      'Me.Bookmark = .Bookmark

      '' The DoEvents was an attempt to allow Windows some extra time to breath.
      '' They didn't help.
      'DoEvents

      '' Move the back to the first record prior to the search.
      '' NOTE: The MoveFirst, MoveLast were an attempt to 
      '' encourage Access to reload the subform.
      '' Didn't work.
      '' Move the form to the last record      
      'rst.MoveFirst
      'Me.Bookmark = .Bookmark

      '' The DoEvents was an attempt to allow Windows some extra time to breath.
      '' They didn't help.
      'DoEvents

      ' Search through the cloned recordset for our original AcctID
      .FindFirst "[AcctID] = '" & strAcctID & "'"

      ' If we've found a matching record, set the form's bookmark match
      ' the cloned recordset's bookmark.
      If Not .EOF Then
         Me.Bookmark = .Bookmark
      Else
         ' This shouldn't happen.
         ' Turn the hourglass off
         DoCmd.Hourglass False
         strMsg = "Cannot find the original Account: " & strAcctID
         MsgBox strMsg, vbOKOnly + vbInformation
      End If

      ' Close the cloned recordset
      .Close
   End With

   ' Put the cursor back where we found it, if we can.
   If fCanSetFocus Then
      ctlPrev.SetFocus
   End If

   ' Set the return value
   RefreshAll = True

' Testing purposes only!
strMsg = "Yes, you've done it!" & vbNewLine & vbNewLine & _
         "Active Control: " & vbTab & strActiveControlName & vbNewLine & _
         "Previous Control: " & vbTab & strPreviousControlName
MsgBox strMsg, vbOKOnly + vbInformation, "Refresh All!"


PROC_CLEANUP:
   ' Release the object variables
   If Not rst Is Nothing Then
      Set rst = Nothing
   End If
   If Not ctlPrev Is Nothing Then
      Set ctlPrev = Nothing
   End If

PROC_EXIT:
   ' Unfreeze the screen
   DoCmd.Echo True

   ' Turn the hourglass off
   DoCmd.Hourglass False

   ' Pop the procedure name off the stack
   ERH_PopStack_TSB
   Exit Function

PROC_ERR:
   ' Unfreeze the screen
   DoCmd.Echo True

   ' Turn the hourglass off
   DoCmd.Hourglass False

   ' Set the return value
   RefreshAll = False
   ' Call the error handler to display information to the user.
   ERH_Handler_TSB
   Resume PROC_EXIT:

End Function

Here's how I call RefreshAll() from the form's Refresh button:

   ' Refresh the main Customer form and the Ledger/Order subforms.
   Call RefreshAll

Here's how I call RefreshAll() from the other code modules:

   ' Refresh the Customer form and requery the subforms
   Call Forms(gcCustomer_Form).RefreshAll

[ssanfu]

Have you single-stepped through the code to see what is happening?

I have not checked, but I see where you use

            Set ctlPrev = Me.ActiveControl

If you are calling this procedure from a different module (form), what would "Me.ActiveControl" refer to?
I would think it would be the active control on the ACTIVE form, not the form where the RefreshAll() code resides.
Remember, ME is shorthand for the current active form.

Would have to single-step through the code to see the values.


---------------------------------------------------------
Other things:
Why are there colons after some of the case statements? The colons shouldn't be there

        Case cErrObjectClosed:

The only time there should be colon (that I know of) is to create a line label for the error handler.
In old versions of BASIC, the colon was used to have multiple commands on one line to save space (bytes). This usage has been depreciated.


----------

 <SNIP>
        ' Search through the cloned recordset for our original AcctID
        .FindFirst "[AcctID] = '" & strAcctID & "'"
<SNIP>

From HELP:
Always check the value of the NoMatch property to determine whether the Find operation has succeeded. If the search succeeds, NoMatch is False. If it fails, NoMatch is True and the current record isn't defined. In this case, you must position the current record pointer back to a valid record.

ie If FIND doesn't find a match (NoMatch = TRUE), it will NOT be at EOF, but it will not be at a valid record either.


-----------
Good job commenting the code!!

[Doug Maier]

Hi ssnafu,

If you are calling this procedure from a different module (form), what would "Me.ActiveControl" refer to?
I would think it would be the active control on the ACTIVE form, not the form where the RefreshAll() code resides.
Remember, ME is shorthand for the current active form.

Would have to single-step through the code to see the values.

I have stepped through the RefreshAll() code when it's called from the form's button click event and when it's called from a procedure stored in another module. The code executes in the same manner, but the results are different for some reason that I cannot detect.

The Me.ActiveControl is run inside the RefreshAll() procedure stored in the form's module, and is returning the expected values in the MsgBox at the end of the procedure. In any case, the Me.ActiveControl and Screen.PreviousControl calls are only used to try to return the cursor to the same control that had focus when the process was initiated. I commented out all the code related to setting the focus and it didn't have any effect on the subform getting updated properly.

From HELP:
Always check the value of the NoMatch property to determine whether the Find operation has succeeded. If the search succeeds, NoMatch is False. If it fails, NoMatch is True and the current record isn't defined. In this case, you must position the current record pointer back to a valid record.

You're right about the help file example shows .NoMatch instead of EOF. (Perhaps checking .EOF was an older way to determine the results? I've been using Access since version 2.0, so there may be some old habits that need refreshing.) I still get the same outcome when I rewrote the code to use .NoMatch instead of .EOF.

If .NoMatch Then
   ' We couldn't find the original account for some reason.
   ' This should never happen.
   ' Turn the hourglass off
   DoCmd.Hourglass False
   strMsg = "Cannot find the original Account: " & strAcctID
   MsgBox strMsg, vbOKOnly + vbInformation
Else
   ' If we've found it, set the form's bookmark to match
   ' the cloned recordset's bookmark.
   Me.Bookmark = .Bookmark
End If

The only time there should be colon (that I know of) is to create a line label for the error handler.
In old versions of BASIC, the colon was used to have multiple commands on one line to save space (bytes). This usage has been depreciated.

As far as using colons in the Case statements, that may be another hold over from an older version of VBA (I should retrain myself to drop it and save myself one keystroke per case, but it doesn't affect the results of the Select/Case.)



Based on your suggestions, I ran through 4 variations of the RefreshAll() and got the same results each time.

Version 1 (Return to previous control, Use .EOF) [Original version]
Called from the form: .......Works (New Ledger subform record appears.)
Called from other module: Doesn't Work (New Ledger subform record does not appear.)

Version 2 (Return to previous control, Use .NoMatch)
Called from the form: .......Works
Called from other module: Doesn't Work

Version 3 (Do Not Return to previous control, Use .EOF)
Called from the form: .......Works
Called from other module: Doesn't Work

Version 4 (Do Not Return to previous control, Use .NoMatch)
Called from the form: .......Works
Called from other module: Doesn't Work



To sum it up, I'm still searching for a reason why the behavior changes based on where the code is called from.

Thanks for your help,


-- Doug

[Doug Maier]

Here's another angle to consider...

Delays due to using a different data connection?
The other processes create new ledger entries using a class object that contains it's own connection to the data file. Could there be a delay before the form's connection to the table recognizes that another connection added a record to the ledger?

I would have thought that requerying the main form would force it to become aware of any changes made by other users and other VBA connections, too.

Hopefully this will spark an idea for someone out there,


-- Doug
PS: The class object is closed and released prior to calling the RefreshAll() procedure.

If Not objCustomer Is Nothing Then
   Set objCustomer = Nothing
End If