Jeez, how to make a form hidden and to store value?

  • Thread starter Thread starter Alex via AccessMonster.com
  • Start date Start date
A

Alex via AccessMonster.com

I was cracking my head on this one yesterday, I need some examples of how
to make a hidden form and to store a value there and refer to it from a
field in another form. I managed to use AutoExe macro to open form and then
run "WindowHide" command, it works, but the data is lost or nowhere to be
stored.
Thanks for any advice
 
Alex,

Hiding a form just to provide access to a value is not the way to do it.

Declare a Public variable in a standard module. Create a Public function in
the same module to return the value of that variable. Then just use the
function from your form control.

'In the module
Public myVariable As Integer

Public Function GetmyVariable() As Integer
GetmyVariable = myVariable
End Function

'In the control
=GetmyVariable()

'Anywhere else
Me!txtMyTextBox = myVariable

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Hi Graham, thanks for your reply
I followed your instructions, seems like a good idea, but Im getting "0" in
the text box field, when it is supposed to be either 1,2,3 (depends on what
I select from combo box). Its a log in form with cb with the code:
---------
Private Sub Command5_Click()

'Check to see if data is entered into the UserName combo box

If IsNull(Me.user_name) Or Me.user_name = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.user_name.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.user_password) Or Me.user_password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.user_password.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this matches value
chosen in combo box

If Me.user_password.Value = DLookup("user_password", "User", "[user_id]
=" & Me.user_name.Value) Then

Me.user_name.Value = myVariable

'Close logon form and open splash screen
DoCmd.Close acForm, "User", acSaveNo
DoCmd.OpenForm "MainMenu"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.user_password.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact
your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If


End Sub
----------------------
As you can see, I've got Me.user_name.Value = myVariable
user_name is the name of the combo box.

What must I look at to resolve "0" problem? I have no idea myself
God bless me and all those who help people on this forum, this is the last
problem I need to solve before the DB completely finished
 
Alex,

Here is a function that will take care of your situation. I avoid using
public variables, and use something like this. If you pass no value, it will
return the current value of varSaveVal, If you pass a value, it will update
the value of varSaveVal. It will also return the same value passed.

Function MyPubVar(Optional varPubVal As Variant) As Variant
Static varSaveVal As Variant

If Not IsMissing(varPubVal) Then
varSaveVal = varPubVal
End If
MyPubVar = varSaveVal
End Function
 
Alex,

Of course it's going to return 0, because you haven't set the variable to
anything prior to reading it! But I fail to see why you need this variable
anyway. In fact, if all you want to do is allow the user 3 attempts at
logging in, then the following will do it:

'Declarations Section
Private intLogonAttempts As Integer

Private Sub Command5_Click()
Dim rs As DAO.Recordset
Dim sSQL As String

If Len(Trim(Nz(Me.user_name, ""))) = 0 _
Or Len(Trim(Nz(Me.user_password, ""))) = 0 Then

MsgBox "You must enter a Username and Password.", vbOKOnly,
"Required Data"
Me.user_name = ""
Me.user_password = ""
Me.user_name.SetFocus
Else
sSQL = "SELECT user_name, user_password " & _
"FROM [User] " & _
"WHERE user_id = """ & Me.user_name & _
""" AND user_password = """ & Me.user_password & """"

Set rs = DBEngine(0)(0).OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
'Allow the user to login
Else
intLoginAttempts = intLoginAttempts + 1
End If
End If

rs.Close
Set rs = Nothing
If (intLogonAttempts = 3) Then
MsgBox "You do not have access to this database." & vbCrLf & vbCrLf
& _
"Please contact your system administrator.",
vbCritical, "Restricted Access!"

Application.Quit
End If
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Alex via AccessMonster.com said:
Hi Graham, thanks for your reply
I followed your instructions, seems like a good idea, but Im getting "0"
in
the text box field, when it is supposed to be either 1,2,3 (depends on
what
I select from combo box). Its a log in form with cb with the code:
---------
Private Sub Command5_Click()

'Check to see if data is entered into the UserName combo box

If IsNull(Me.user_name) Or Me.user_name = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.user_name.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.user_password) Or Me.user_password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.user_password.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this matches value
chosen in combo box

If Me.user_password.Value = DLookup("user_password", "User", "[user_id]
=" & Me.user_name.Value) Then

Me.user_name.Value = myVariable

'Close logon form and open splash screen
DoCmd.Close acForm, "User", acSaveNo
DoCmd.OpenForm "MainMenu"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.user_password.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact
your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If


End Sub
----------------------
As you can see, I've got Me.user_name.Value = myVariable
user_name is the name of the combo box.

What must I look at to resolve "0" problem? I have no idea myself
God bless me and all those who help people on this forum, this is the last
problem I need to solve before the DB completely finished
Click to expand...
 
Back
Top