Software

Ask something
Discussion Locked

Global Variables in Access 97?

0 helpful
6 replies

by claymuir

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Here is my problem. I need to have users log in to the database on the opening form and use their log-in name to auto-fill other areas on other forms. The users will log-in by selecting their names from a Combobox.

What I want to do is create a Public Sub and in that Sub I want Public Variables that hold the Log-in (From the Login Combo login!loginCombo.text field) and also Public variables for Date and time. My big problem is I can not figure out how to make those variables work because youmust load Access 97 from a Form not a module and everytime I try to use the varaibles they do not work. I have considered Setfocus but do not understand it that well.

Can anyone get me on the right track here?

Oh, one more thing - I can not create a VB.exe, I must use just Access 97.

6 replies

by d_l

The simplest way to achieve your goal is to create a new Module (let's call it mdlGlobal). Within this new module you can create 2 public variables...

Public strLoginID As String
Public datLoginTime As Date

Now within your opening form, let's say on the Click event of your login button, you can set the variables...

Private Sub cmdLogin_Click()

strLoginID = cboLogin.Text
datLoginTime = Now

End Sub

Hope that gets your started...

by claymuir

Thats my problem. Where that works fine in VB it does not appear to work in VBA. I have tried using the Formats: Forms!Control.Text ;
Me!control.text and nothing works. I am starting to think it has something to do with focus. Problem is I know there must be a easy way to make the MainModule stay open with the Variable set. To be quit honest I have gone through about 3 books, the Applications book in the Value pack and many places online and not one source has a clear answer yet this is one ofthe most basic uses of code when working with DB's.

by burtsa

In one of my Access 97 programs I use the custom database properties.

Function ap_GetDatabaseProp(dbDatabase As Database, strPropertyName As String) As Variant

ap_GetDatabaseProp = dbDatabase.Containers!Databases _
.Documents("UserDefined").Properties(strPropertyName).Value

End Function

Sub ap_SetDatabaseProp(dbDatabase As Database, strPropertyName As String, varValue As Variant)

dbDatabase.Containers!Databases.Documents("UserDefined").Properties(strPropertyName).Value = varValue

End Sub

The you just either get or set the property you want. It basicly becomes a Global Variable.

ap_setdatabaseprop(currentDb,"User Name",cboLogin.Text )
ap_setdatabaseprop(currentDb,"Login Time",now())

To get them back out use:

cbologin.text=ap_getdatabaseprop(CurrentDb,"User Name")

You will need to set these props up in the database properties section.

Hope this helps.
Shawn

by edbutler

I think you are having trouble not wso much with the global variables as with a more subtle problem with the .Text property of the Combo Box. You can't access the .Text property in VBA unless the control has focus. So, to add a line to the previous example:

Public strLoginID As String
Public datLoginTime As Date

Private Sub cmdLogin_Click()

'SetFocus so you can get the .Text:
cboLogin.setFocus
strLoginID = cboLogin.Text
datLoginTime = Now

End Sub

by d_l

The Text property of the combo box cannot be read or set if it doesn't have the focus. The reason for this is that the Text property is used for unsaved data (data being manipulated). What you should use is Value property.

i.e.
strLogin = cboLogin.Value

Hope that helps.

by claymuir

Well, Shawn I have not had time to try it out just yet - but, at first glance I would say you have a great idea there. I will try it tommorrow and post what happens..

Thanks either way for some interesting code.

This discussion is closed to new replies.