Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    public variable in textbox? (2002)

    My database includes a login form that creates a public variable strUser. The public variable seems to have been created in that "debug.print strUser" gives the correct value anywhere in the code except when an error occurs.

    I would like to display "strUser" in a text box on several forms. How do I display a public variable (in this case strUser) in an unbound text box on a form??

    Bob Holmstr

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

    Re: public variable in textbox? (2002)

    You can't do it directly, but you can either set the value in code or create a function that returns the value of the public variable and then set that function as the controlsource for your control. The function would look something like this:

    <code>Public Static Function MY_VAR(Optional strNewValue As String) As String
    dim strValue As String

    If strNewValue <> "" Then
    strValue = strNewValue
    End If

    MY_VAR = strValue
    End Function</code>

    Passing the function an argument sets its value. Calling it without the argument returns the value stored in the static function.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: public variable in textbox? (2002)

    Thank you Charlotte!

    I created the function and it works as you said it would. ( note to other beginners who happen on this exchange - when Charlotte says to "set that function as the control source for your control - in this case it means that in the control source area of the properties for the text box control should be set to =MY_VAR() )

    Two problems remain:

    1. The text box doesn't change until the form is reloaded - what is the correct way to reload the form from the VBA code that selects the content for the text box?

    2. If an error occurs anywhere in the code - MY_VAR() no longer displays a value - I assume that this is normal behavior - is there any why to avoid it?

    Bob Holmstr

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: public variable in textbox? (2002)

    1. strUser is populated when the database is opened. When you open another form than the logon form, the name should be displayed correctly. It's not going to change while that form is open, is it? So I don't understand what you mean here.

    2. If an error occurs in code, all variables will be cleared. To avoid this, make sure there are no errors in your code <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    But seriously, you can prevent variables from being cleared by setting up error handling in your code, like this:

    Sub SomethingOrOther
    Dim ...

    On Error GoTo ErrHandler

    ' code goes here
    ...

    ExitHandler:
    ' cleaning up instructions go here
    ...
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  5. #5
    Lounger
    Join Date
    Jan 2002
    Location
    Oregon
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: public variable in textbox? (2002)

    Thank you Hans,

    I don't currently have a separate log in form - I am trying to incorporate the log in on the switchboard form - this was probably a very bad idea because I am quickly learning that switchboard forms are not standard forms! I could not make a standard combo box work (nothing happens when you click on an item) - what I am doing is to combine a text box that displays the user with a command button that opens a list box that displays a user list - the code behind the on click event of the list box creates the text displayed using the method suggested by Charlotte. It works but with the problems described above. Beginners create strange things - but that is part of the process of learning! I think I need to go back and create a separate log in form. I also need to work on error handling.

    Bob Holmstr

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: public variable in textbox? (2002)

    You can force a text box with a "formula" =MY_VAR() to update by using an instruction Me.Recalc (this will update all calculated controls on the form).

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: public variable in textbox? (2002)

    Hi Bob,
    You might also want to investigate the use of Access User Security to get a login name - in many respects it's much simpler than what you are doing. You get that feature by simply applying a password to the Admin account, which is the default account that everyone logs into. Then you can use the Access function CurrentUser() to get the User Name anytime you want it. We have a brief tutorial on our web site at User Security Tutorial which includes a number of links to other userful documents. You might also want to look at an excellent but longer paper called User Security paper by <!profile=jacksonmacd>jacksonmacd<!/profile>.
    Wendell

Posting Permissions

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