Solved How to re-declare a public variable? (1 Viewer)

KitaYama

Active member
Local time
Tomorrow, 01:09
Joined
Jan 6, 2022
Messages
454
I have the following public variable declared in a module:
Code:
Option Explicit
Public OrdersRibbon As IRibbonUI

Then I have a function that runs from AutoExec and creates a xml statement and sets it as the default ribbon for the database:
Code:
Public Function LoadRibbons()
    .....
    ' create the xml
    .....
    Application.LoadCustomUI "OrdersRibbon", strXML
    .....
End Function

Then every now and then I use the following to re-assign the ribbon, hide/show/enable/disable buttons & controls:
Code:
OrderRibbon.Invalidate
OrderRibbon.InvalidateControl (MyControlID)

This is working, not even a single problem.

But if an unhandled error occurs, the public variable is destroyed (if it's the correct terminology) and the next time the code tries to invalidate the ribbon, it returns the following error:
Object variable or With block variable not set

Now my question:
  1. Is there any other way than a public variable to use in above case? I mostly use tempVars. But I've never set an object to a tempVar nd I'm not sure how to use it in above case.
  2. Is there any way to re assign a public variable once it's been destroyed? (at present we have to close and re-open the database)

Any kind of insight is much appreciated.
 
Last edited:
Solution
You can also save the memory address of the ribbon, and if it breaks, re-reference it from that address, because obviously the ribbon is not destroyed, just the variable that points to it. Consider...
Code:
Private ui_ As IRibbonUI

Function RibbonLoad(ui As IRibbonUI)
'   callback function from ribbon when it loads
    Set ui_ = ui               ' save to local variable
    TempVars!ui = ObjPtr(ui)   ' save address to temp var as backup
End Function

Property Get MainRibbon() As IRibbonUI
'   global property that automatically rebuilds
'   the ribbon if the ui_ variable is Nothing
    If ui_ Is Nothing And Not IsNull(TempVars!ui) Then Set ui_ = GetObjectFromPointer(TempVars!ui)
    Set MainRibbon = ui_
End Property

And the...

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:09
Joined
May 7, 2009
Messages
16,504
maybe add an Error Handler and on the Error Handler, Call LoadRibbon again.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:09
Joined
May 7, 2009
Messages
16,504
if you'll see the XML of your custom ribbon, OnRibbonLoad() sub is called
whenever you load the ribbon.
OnRibbonLoad sub in turn, assign the Ribbon you your Public variable.
 

KitaYama

Active member
Local time
Tomorrow, 01:09
Joined
Jan 6, 2022
Messages
454
Unfortunately I couldn't make it work.
here's a sample database we were working on.
If you click the button, an error occurs and LoadRibbon is called. But still it shows the error.
 

Attachments

  • TestRibbon.accdb
    1.2 MB · Views: 134

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:09
Joined
May 7, 2009
Messages
16,504
it complains that it is already loaded.
so it is on your hand to prevent such errors in the future.
nothing can be done except to re-start the db.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:09
Joined
May 7, 2009
Messages
16,504
i tried... and failed.
it's weekend, so i must enjoy the day and not to worry (think) a lot.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:09
Joined
May 21, 2018
Messages
6,371
You can wrap in a function whenever you use the object.
Code:
'declare public variable
Public OrdersRibbon As IRibbonUI

Make function
Code:
Public Function GetOrderRibbon() as IRibbonUI
'if Order ribbon goes out of scope it is nothing
if OrderRiibon is Nothing
    'code here to re set the public Variable
end if
   'if not just return the public variable
   set GetOrderRibbon = OrdersRibbon
end function

Change your calls from
Code:
OrderRibbon.Invalidate
OrderRibbon.InvalidateControl (MyControlID)
to
Code:
GetOrderRibbon.Invalidate
GetOrderRibbon.InvalidateControl (MyControlID)
 

KitaYama

Active member
Local time
Tomorrow, 01:09
Joined
Jan 6, 2022
Messages
454
Code:
Public Function GetOrderRibbon() as IRibbonUI
'if Order ribbon goes out of scope it is nothing
if OrdersRibbon is Nothing then
    'code here to re set the public Variable
end if
   'if not just return the public variable
   set GetOrderRibbon = OrdersRibbon
end function
@MajP
'code here to re set the public Variable
Forgive my stupidity. But that is the exact thing I'm trying to achieve and don't know how.
The public variable (OrdersRibbon) is declared out of all functions and subs.
How can I set it in above if statement?

thank you.
 
Last edited:

KitaYama

Active member
Local time
Tomorrow, 01:09
Joined
Jan 6, 2022
Messages
454
You can wrap in a function whenever you use the object.
Code:
'declare public variable
Public OrdersRibbon As IRibbonUI

Make function
Code:
Public Function GetOrderRibbon() as IRibbonUI
'if Order ribbon goes out of scope it is nothing
if OrderRiibon is Nothing
    'code here to re set the public Variable
end if
   'if not just return the public variable
   set GetOrderRibbon = OrdersRibbon
end function

Change your calls from
Code:
OrderRibbon.Invalidate
OrderRibbon.InvalidateControl (MyControlID)
to
Code:
GetOrderRibbon.Invalidate
GetOrderRibbon.InvalidateControl (MyControlID)
The following line gives me the following error : (even before the public variable goes out of scope)
Code:
Set GetOrderRibbon = OrdersRibbon
error:
Object variable or With block variable not set[/code]
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:09
Joined
Oct 29, 2018
Messages
19,019
Hi. I am responding now just to subscribe to this thread and get a reminder later to look in my archives for a solution I came up a while back for the same problem.

Sent from phone...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 28, 2001
Messages
22,882
In general, you cannot set an object using TempVars - that method is limited to strings and numbers. It has no room for objects.

The trick is to ALWAYS trap errors because it isn't the error that does the damage - it is the RESET that occurs because Access's internal error handler does this code reset for you. The RESET wipes out all variables. I believe it does so by doing an internal wipe of something called "the HEAP" and thus it reclaims virtual memory.

You get to the Access internal handler because no other error handler was willing or able to handle the error you encountered. Usually because no such handler was declared.

Once that RESET occurs, all of the objects you may have created are now toast. Oh, you can rebuild the objects easily enough - but you can't so easily reload them to the same state as occurred before the fatal error. Their history is gone and it might be questionable as to whether you would even want to try to reconstitute them. AND, if the objects involved links to external programs like Excel, Word, or Outlook, each with their own SEPARATE memory space, their memory was NOT affected by the error so they are still extant but "dangling" with no control channels - and none can be opened.

If you study the VBA error system, you realize that each frame on the call stack (the "frame" is built by each function or sub call) holds a slot for the error handler, and the ON ERROR GOTO x loads that slot. In a way, this slot is similar to the entry-point slots where you declare entry points for various event routines to be called for their events. Not identical because of stack gyrations that don't occur for "normal" event code.

If you throw an error but don't have an error handler, then your sub/function is now dead. It's frame is popped off the stack and the error is redeclared (technical term: resignaled) in your subroutine's caller's context. The caller's call frame is checked for a handler. Either it has one or it doesn't. This process (called "stack unwinding") continues until you get to the base event code routine, which (if you recall) always has a PUBLIC or PRIVATE SUB. You get to that event code via a sub call made by Access itself. Remember, MSACCESS.EXE is the MAIN routine corresponding to all of your event subs. The event dispatcher routine's event handler (a.k.a. Last-Chance handler) WILL take that error. THAT is the error handler that does the damage.

The solution? Don't get there. Trap the error before it gets that far.

This is DEFINITELY a case where the old phrase holds true: An ounce of prevention is worth a pound of cure.

EDITED by THE_DOC_MAN to correct some minor typos.
 
Last edited:

GPGeorge

Grover Park George
Local time
Today, 09:09
Joined
Nov 25, 2004
Messages
698
In general, you cannot set an object using TempVars - that method is limited to strings and numbers. It has no room for objects.

The trick is to ALWAYS trap errors because it isn't the error that does the damage - it is the RESET that occurs because Access's internal error handler does this code reset for you. The RESET wipes out all variables. I believe it does so by doing an internal wipe of something called "the HEAP" and thus it reclaims virtual memory.

You get to the Access internal handler because no other error handler was willing or able to handle the error you encountered. Usually because no such handler was declared.

Once that RESET occurs, all of the objects you may have created are now toast. Oh, you can rebuild the objects easily enough - but you can't so easily reload them to the same state as occurred before the fatal error. Their history is gone and it might be questionable as to whether you would even want to try to reconstitute them. AND, if the objects involved links to external programs like Excel, Word, or Outlook, their memory was NOT affected by the error so they are still extant but "dangling" with no control channels - and none can be opened.

If you study the VBA error system, you realize that each frame on the call stack (the "frame" is built by each function or sub call) holds a slot for the error handler, and the ON ERROR GOTO x loads that slot. It a way, this slot is similar to the entry-point slots where you declare entry points for various event routines to be called for their events. Not identical because of stack gyrations that don't occur for "normal" event code.

If you don't have an error handler, then your sub/function is now dead. It's frame is popped off the stack and the error is redeclared (technical term: resignaled) in your subroutine's caller's context. The caller's call frame is checked for a handler. Either it has one or it doesn't. This process (called "stack unwinding") continues until you get to the base event code routine, which (if you recall) always has a PUBLIC or PRIVATE SUB. You get to that event code via a sub call made by Access itself. Remember, MSACCESS.EXE is the MAIN routine corresponding to all of your event subs. The event dispatcher routine's event handler (a.k.a. Last-Chance handler) WILL take that error. THAT is the error handler that does the damage.

The solution? Don't get there. Trap the error before it gets that far.

This is DEFINITELY a case where the old phrase holds true: An ounce of prevention is worth a pound of cure.
I don't think I've ever seen a better explanation. Thank you.
 

KitaYama

Active member
Local time
Tomorrow, 01:09
Joined
Jan 6, 2022
Messages
454
The solution? Don't get there. Trap the error before it gets that far.
@The_Doc_Man
Thanks for detailed explanation, but I'm afraid it's impossible.
The database has been written by two developers and has been evolved during a span of 10 years by another 3 ones.
It contains more than 40 forms, 30 reports, several thousands of lines of codes. Our workstations on shop floor are running during 3 shifts, 24 hours a day, 7 days a week. So it's really hard to catch which section of the database is raising an error. Someone tries to do something, an error happens, and a week later another one tries to opens a form and the ribbon can not be invalidated.
Going through all subs and function and adding error handle takes a life time.

I've been asked to work on this database temporary until they find someone better than me.
Some sections are still using accdb. Maybe a better solution for now is converting them to accde.
 

MarkK

bit cruncher
Local time
Today, 09:09
Joined
Mar 17, 2004
Messages
7,914
You can also save the memory address of the ribbon, and if it breaks, re-reference it from that address, because obviously the ribbon is not destroyed, just the variable that points to it. Consider...
Code:
Private ui_ As IRibbonUI

Function RibbonLoad(ui As IRibbonUI)
'   callback function from ribbon when it loads
    Set ui_ = ui               ' save to local variable
    TempVars!ui = ObjPtr(ui)   ' save address to temp var as backup
End Function

Property Get MainRibbon() As IRibbonUI
'   global property that automatically rebuilds
'   the ribbon if the ui_ variable is Nothing
    If ui_ Is Nothing And Not IsNull(TempVars!ui) Then Set ui_ = GetObjectFromPointer(TempVars!ui)
    Set MainRibbon = ui_
End Property

And the GetObjectFromPointer() code looks like this.
Code:
    Private Declare PtrSafe Sub RtlMoveMemory Lib "kernel32" _
        (ByRef Destination As Any, ByRef Source As Any, ByVal length As Long)
    
    Public Function GetObjectFromPointer(ByVal ptr As LongPtr) As Object
        Dim Obj As Object
        RtlMoveMemory Obj, ptr, Len(ptr)
        Set GetObjectFromPointer = Obj
        RtlMoveMemory Obj, ZEROPOINTER, Len(ptr)
    End Function

This is handy in other ways too, for instance you can effectively pass anything to a form by passing in an object address in OpenArgs. Here we create a whole mess of stuff, add it to a collection, and pass the address of that collection to the form...
Code:
    Dim col As New VBA.Collection
    
    col.Add New Scripting.FileSystemObject, "fso"
    col.Add CurrentDb.OpenRecordset("SELECT * FROM table"), "rst"
    col.Add Me, "sender"
    
    DoCmd.OpenForm "fTest", , , , , acDialog, ObjPtr(col)

..and then, when the form opens, we can enumerate the members of that same collection.
Code:
private col_ as VBA.Collection

Private Sub Form_Load()
    Dim var
    Set col_ = Lib.GetObjectFromPointer(Me.OpenArgs)
    For Each var In col_
        Debug.Print TypeName(var)
    Next
End Sub
 
Solution

KitaYama

Active member
Local time
Tomorrow, 01:09
Joined
Jan 6, 2022
Messages
454
@MarkK your solution seems promising.
Unfortunately I don't have access to my PC until Monday when I'm back to office.
I'll test and will report back.

Thank you.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:09
Joined
Feb 28, 2001
Messages
22,882
So it's really hard to catch which section of the database is raising an error.

IF you have the possibility of modifying things, see if you can put a logging routine that makes a timestamp and the name /context of the called routine. When you have the error, you know at least the general path you are taking. If you can't even do that, I'm afraid you have a REAL sticky wicket on your hands. Changing things from ACCDB to ACCDE doesn't change the way the error handler works, so I'm afraid that isn't going to be a solution for you.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:09
Joined
May 7, 2009
Messages
16,504
i am testing it and this variable is not defined: ZEROPOINTER
on initial test, it fails here:

If ui_ Is Nothing And Not IsNull(TempVars!ui) Then Set ui_ = GetObjectFromPointer(TempVars!ui)

(k) when an error occurs, ui_ variable is still intact so the code above is ignored.
but the problem is ui_ (ribbon) is intact but the pointers to public sub (getLabel, getVisible, etc.) are lost.

on another attempt i define ZEROPOINTER AS 0&
and restart the db.

still error (k) exists, so my conclusion is that when error occurs, ui_ (the Ribbon) is not set to Nothing, but is damaged (it's pointer to public subs like getVisible, getLabel, etc. are lost.)

restoring it from GetObjectFromPointer function does not restore it. probably what is restored is the pointer, but pointing to where???

open form frm and add breakpoint to the click event of the button.
 

Attachments

  • TestRibbon.accdb
    1.4 MB · Views: 77
Last edited:

Users who are viewing this thread

Top Bottom