Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variables being cleared (A2K)

    If Form1 opens Form2 and Form2 encounters an unhandled error, why does a private variable in Form1 get wiped out?

    Demo:

    Create a new db.
    Create a form in design view.
    Add one textbox and two command buttons.

    Put the following code in the form's code module:

    --- start code ---

    Option Compare Database
    Option Explicit

    Private mtext As String

    Private Sub Form_Load()
    mtext = InputBox("Enter a value")
    End Sub

    Private Sub Command2_Click()
    Text0.Value = mtext
    End Sub

    Private Sub Command3_Click()
    DoCmd.OpenForm "Form2", acNormal, , , , acWindowNormal
    End Sub

    --- end code ---

    Create a second form in design view.
    Add one textbox and one command button.

    Put the following code in the second form's code module:

    --- start code ---

    Option Compare Database
    Option Explicit

    Private Sub Command2_Click()
    Text0.Value = 1 / Text0.Value
    End Sub

    --- end code ---

    Save the two forms.
    Start Form1.
    Input anything into the InputMessage box.
    Click the first command button. The value you entered will display.
    Click the second command button to open the second form.
    Enter a zero in the textbox on the second form.
    Click the command button on the second form.
    The divide by zero run time error will display.
    Click "End".
    Click on the first form.
    Click on the first command button on the first form.
    The textbox will be blanked, indicating that the variable mtext has been wiped out.

    Question: *** WHY??? ***



    TIA
    Donald

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Variables being cleared (A2K)

    I don't really know. However, I've seen this before with global variables, which is one reason I don't use them.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variables being cleared (A2K)

    Yabut . . .

    This isn't even a global -- it's just a private variable.

    Here is an answer I got on another list:

    "Because there's only one VBA running. When you crash it with the unhandled error, everything is reset."

    What do you think?
    Donald

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Variables being cleared (A2K)

    >>"Because there's only one VBA running. When you crash it with the unhandled error, everything is reset."<<

    Sounds right to me. But whether we agree or disagree, I think this is one of those things you have to accept "because that's the way it is!"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Variables being cleared (A2K)

    There's at least one workaround of sorts, though, if you're comfortable with creating custom database properties. If you create custom properties for your global or even form level values and then populate them instead of variables, the properties will retain their values when an unhandled error trashes your variables. Still another option for globals is to store the values in a special table and use a function with a static variable in it to either lookup the value in the table or return the value of the static variable if it's already been looked up.
    Charlotte

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Variables being cleared (A2K)

    I just have seldom found the need to keep open a global variable. Occasionally I'll use one to pass information, but I don't depend on it to be there "forever". If I want to depend on something to be there, I'll put it as a text control on a hidden form. For example, I have a Company form I keep open (and hidden), that maintains typical Company-level options. This also serves dual purpose of keeping open the backend database.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Variables being cleared (A2K)

    That's another option, but I prefer using database properties and lookup values.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variables being cleared (A2K)

    Charlotte,

    When you say "database properties" are you talking about the business where you use the

    Public Property Get
    Public Property Let
    Public Property Set

    thingies?

    Donald

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom database properties

    No, those are properties of a class object. You can actually create custom properties for the database itself, and that was true in Access 97 as well. For instance, you have to create the database propterty AppTitle before you can add a custom title to your main window's title bar.

    You can find DAO code for that by typing AppTitle into the Answer Wizard in Access2K help. You can also use ADO to create custom properties for objects in the database. With ADO, I do it this way:
    <pre>Public Function AddCustomProperty(ByRef obj As AccessObject, _
    ByVal strPropName As String, _
    ByVal varPropValue As Variant) _
    As Boolean
    ' Add custom properties to the AccessObjectProperties
    ' collection that is associated with an AccessObject object.
    With obj.Properties
    .Add strPropName, varPropValue
    End With
    If CurrentProject.Connection.Errors.Count = 0 Then
    AddCustomProperty = True
    End If
    Set obj = Nothing
    ' With CurrentProject.AllForms(strFormName).Properties
    ' .Add strPropName, varPropValue
    ' End With
    End Function 'AddCustomProperty(byval strObjName as string, _
    ByVal intObjType As Integer, _
    ByVal strPropName As String, _
    ByVal varPropValue As Variant) _
    As Boolean</pre>

    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom database properties

    Thanks, Charlotte.

    I promise to go study the manuals on this, but please allow me one quick question:

    Is the sample code run once at some time during development to add the property to that particular mdb, or does the code have to be run each time the mdb is run? (And what about mde's?).

    Thanks.
    Donald

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom database properties

    Once you add a property to a database, the property remains. The value assigned to it will need to be set each time you open the database but you only have to create the property itself once. You shouldn't be able to add database properties to an MDE, since that's a design change, but I have to admit I never tried it. You *should* be able to populate the properties you created before turning the database into an MDE, however.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •