General discussion

Locked

Global Variables in Access 97?

By claymuir ·
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.

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Simplest way...

by d_l In reply to Global Variables in Acces ...

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...

Collapse -

Well, actually...

by claymuir In reply to Simplest way...

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.

Collapse -

Database Properties.

by burtsa In reply to Well, actually...

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

Collapse -

That looks about right..

by claymuir In reply to Database Properties.

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.

Collapse -

setfocus

by edbutler In reply to Well, actually...

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

Collapse -

Oopsie.

by d_l In reply to Well, actually...

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.

Back to Software Forum
6 total posts (Page 1 of 1)  

Software Forums