Help with Calling User Input Variable in Form

Question

I want to have a button on a form that allows users to enter their name. I have accomplished this using an input box, so that on Form1, I have the following OnClick code:

Private Sub InputBoxTest_Click()

    glbName = InputBox("Enter your name:", "User Name", "Your Name Here")

End Sub

My intent is to store the reponse as a GLOBAL variable so that I can refer to it across multiple forms.

To accomplish this, I have created a module (Module1) with the following:

Option Compare Database

Option Explicit

Public glbDBName As String

I cannot figure out how to refer to this variable in subsequent forms -- to get the forms to bring in whatever the current value of glbName is. This is NOT a temporary value. Once set, it will not change. Capturing the user's name is a one-time deal. I'm just trying to give people a way to "customize" their menus without having to actually open up the back-end program or displaying the navigation pane. This *seems* like it would be relatively simple, but I'm not getting it and would appreciate any help.

Answer 1

If you use a variable or a text box in an unbound form the user will have to enter their name every time they open the database.  You can avoid this in one of the following ways depending on your set-up:

1.  If each user has their own front end, either on their local drive, or in a personal location on the network to which only they have permissions, then you can easily avoid their having to re-enter their name each time.

To do this add a table, UserDetails say, to the front end with just a single column, UserName say.  To start with 'seed' the table with one row with the value 'Enter your name here' for instance.  Then create a little form bound to this table with no navigation buttons, and its AllowAdditions and AllowDeletions properties set to False (No).  You can either automatically open the form at start-up or provide some mechanism for the user to open it on demand.

To get the name at any time create a little function in a standard module:

Function GetCurrentUser() As String

    GetCurrentUser= DLookup("UserName ","UserDetails")

End Function

You can then call the GetCurrentUser() function wherever you wish to return the user's name.  You could of course extend this by adding other columns to the UserDetails table for any other information about a user you might wish to use in the database, and write other little functions to get this.

2.  If users don't use their own personal front end you can achieve the same thing by storing all users' names in a single table in the back end, but as well as the UserName column add a LoginName column in which the user's system login name is stored.  Leave the table empty to start with.

Add the following module to the database to get the login name:

' module basGetUser

Option Compare Database

Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _

   lpBuffer As String, nSize As Long) As Long

Public Function GetUser() As String

   Dim strBuffer As String

   Dim lngSize As Long, lngRetVal As Long

   lngSize = 199

   strBuffer = String$(200, 0)

   lngRetVal = GetUserName(strBuffer, lngSize)

   GetUser = Left$(strBuffer, lngSize - 1)

End Function

Then base the form for entering the name on the following query:

SELECT *

FROM UserDetails

WHERE LoginName = GetUser();

To provide a means for a user to enter their name for the first time, put the following in the form's Load event procedure:

    Dim strCriteria As String

    Dim strSQL As String

    strSQL = "INSERT INTO UserDetails(LoginName,UserName) " & _

        "VALUES(""" & GetUser() & """,""Enter name here"")"

    strCriteria = "LoginName = """ & GetUser() & """"

    If IsNull(DLookup("LoginName", "UserDetails", strCriteria)) Then

        CurrentDb.Execute strSQL, dbFailOnError

        Me.Requery

    End If

Answer 2

Thank you, Marshall Barton! I'd like to give myself a great big "DUH!" That was such a simple, elegant solution -- I've spent the better part of this week trying to re-invent the wheel! Your text box idea works PERFECTLY for my particular project!

Answer 3

I think it's simpler to add a text box to your form so users can enter their name in it, instead of using the pop up InputBox function.  With that in place, you can refer to the name in the text box just about anywhere in Access using the syntax:   Forms![name of the form].[name of the text box]

To make that a safe global, instead of closing the form, just make it invisible with a line of code in some appropriate place (e.g. in the code that processes the name):

     Forms![name of the form].Visible = False

If you already have another form that is always open (a common thing in an Access app), then your name input form can copy the value to a (hidden?) text box on that form using:

    Forms![name of always open form].[name of that text box] = Me.[name of the text box]

Just in case the message wasn't clear, using a VBA Global variable is not safe and they should be avoided, so we are presenting the standard alternatives.

---

If for some reason, you can not avoid using a VBA Global variable, the answer to the question you asked is to add a Public function to the module with the variable:

Option Compare Database

Option Explicit

Public glbDBName As String

Public Function GetDBName()

    GetDBName = glbDBName

End Function

Then you can call the function using GetDBName wherever you want to use the value of the variable.

Answer 4

Hi,

instead that a global variable you can use a Tempvars. A tempvars is safer because it is not lost in case of error. You can use a tempvars in a query and in Vba code.

In a form you can crete a textbox control to input a value instead than a InputBox.

Mimmo