Macro to VBA

dbalorenzini · Jan 10, 2009

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login DialogNew", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.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

But I lose the filter for the user records when I do this. Any ideas on how
to set up the SetTempVar variable in a procedure?

Dymondjack · Jan 11, 2009

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &

Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

dbalorenzini said:
I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login DialogNew", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.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

But I lose the filter for the user records when I do this. Any ideas on how
to set up the SetTempVar variable in a procedure?
--
Thanks,
Art
Database Administrator
Yankton, SD

dbalorenzini · Jan 11, 2009

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:
Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &

Me.cboEmployee.Value)

Click to expand...

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

dbalorenzini said:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login DialogNew", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.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

But I lose the filter for the user records when I do this. Any ideas on how
to set up the SetTempVar variable in a procedure?
--
Thanks,
Art
Database Administrator
Yankton, SD

Click to expand...

Dymondjack · Jan 11, 2009

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

dbalorenzini said:
Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &

Me.cboEmployee.Value)

Click to expand...

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

dbalorenzini said:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login DialogNew", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.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

But I lose the filter for the user records when I do this. Any ideas on how
to set up the SetTempVar variable in a procedure?
--
Thanks,
Art
Database Administrator
Yankton, SD

Click to expand...

Click to expand...

dbalorenzini · Jan 12, 2009

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:
I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

dbalorenzini said:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login DialogNew", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.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

But I lose the filter for the user records when I do this. Any ideas on how
to set up the SetTempVar variable in a procedure?
--
Thanks,
Art
Database Administrator
Yankton, SD

Click to expand...

Click to expand...

Dymondjack · Jan 12, 2009

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

dbalorenzini said:
Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

dbalorenzini said:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login DialogNew", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.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

But I lose the filter for the user records when I do this. Any ideas on how
to set up the SetTempVar variable in a procedure?
--
Thanks,
Art
Database Administrator
Yankton, SD

Click to expand...

Click to expand...

dbalorenzini · Jan 13, 2009

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:
In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

dbalorenzini said:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login DialogNew", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.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

But I lose the filter for the user records when I do this. Any ideas on how
to set up the SetTempVar variable in a procedure?
--
Thanks,
Art
Database Administrator
Yankton, SD

Click to expand...

Click to expand...

Dymondjack · Jan 13, 2009

You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

dbalorenzini said:
I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

dbalorenzini said:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login DialogNew", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.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

But I lose the filter for the user records when I do this. Any ideas on how
to set up the SetTempVar variable in a procedure?
--
Thanks,
Art
Database Administrator
Yankton, SD

Click to expand...

Click to expand...

dbalorenzini · Jan 13, 2009

Is this the user name or ID? WHen I try [ID] I get a datatype mismatch be
cause sUser is string and ID is a autonumber.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:
You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

dbalorenzini said:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login DialogNew", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.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

Click to expand...

Click to expand...

dbalorenzini · Jan 13, 2009

I am now getting the following error:

Syntax error (missing operator) in query expression: 'Staff Name = '8"

and when I change 'Staff Name' to ID I get the following error:

Datatype mismatch in criteria expression.

suser is returning a string of "8"
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:
You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

dbalorenzini said:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login DialogNew", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.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

Click to expand...

Click to expand...

Dymondjack · Jan 13, 2009

I had assumed your primary key was the User name (mine is). If the User name
is not your PK, you need a way to get the Autonumber from the Username. If
you have duplicate user names, this could(will) cause an issue.

So, essentially, what you need is code where you can input the User name and
return the corresponding Autonumber value. Generally, this would would look
something like this:

Dlookup("AutoNumberField", "UserTable", "UserNameField = '" & sUser & "'")

This may or may not work depending on your table. Luckily, there's a few
different ways to go about this. Post some info on your user table. We need
to know what field(s) are your Primary Keys, and if your User field will ever
contain duplicates.

If you user field does not have duplicates in the table, this is an easy
fix. If there are duplicate user names, things get a little trickier.

Let me know how the table is structured and we'll take it from there.

dbalorenzini said:
Is this the user name or ID? WHen I try [ID] I get a datatype mismatch be
cause sUser is string and ID is a autonumber.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

dbalorenzini said:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login DialogNew", acSaveNo
DoCmd.OpenForm "Home"

Click to expand...

Click to expand...

dbalorenzini · Jan 13, 2009

The source is based on the following query:

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First
Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First
Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First
Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last
Name])) AS [Staff Name], tblStaff.*
FROM tblStaff
ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First
Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First
Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First
Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last
Name]));

The ID column is the primary key and Staff Name will never be duplicated.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:
I had assumed your primary key was the User name (mine is). If the User name
is not your PK, you need a way to get the Autonumber from the Username. If
you have duplicate user names, this could(will) cause an issue.

So, essentially, what you need is code where you can input the User name and
return the corresponding Autonumber value. Generally, this would would look
something like this:

Dlookup("AutoNumberField", "UserTable", "UserNameField = '" & sUser & "'")

This may or may not work depending on your table. Luckily, there's a few
different ways to go about this. Post some info on your user table. We need
to know what field(s) are your Primary Keys, and if your User field will ever
contain duplicates.

If you user field does not have duplicates in the table, this is an easy
fix. If there are duplicate user names, things get a little trickier.

Let me know how the table is structured and we'll take it from there.

dbalorenzini said:

Is this the user name or ID? WHen I try [ID] I get a datatype mismatch be
cause sUser is string and ID is a autonumber.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

Click to expand...

Click to expand...

dbalorenzini · Jan 13, 2009

OK this is what I did. I added a UserID field (text) to the tblStaff table.
The ID column is the Primary key.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:
I had assumed your primary key was the User name (mine is). If the User name
is not your PK, you need a way to get the Autonumber from the Username. If
you have duplicate user names, this could(will) cause an issue.

So, essentially, what you need is code where you can input the User name and
return the corresponding Autonumber value. Generally, this would would look
something like this:

Dlookup("AutoNumberField", "UserTable", "UserNameField = '" & sUser & "'")

This may or may not work depending on your table. Luckily, there's a few
different ways to go about this. Post some info on your user table. We need
to know what field(s) are your Primary Keys, and if your User field will ever
contain duplicates.

If you user field does not have duplicates in the table, this is an easy
fix. If there are duplicate user names, things get a little trickier.

Let me know how the table is structured and we'll take it from there.

dbalorenzini said:

Is this the user name or ID? WHen I try [ID] I get a datatype mismatch be
cause sUser is string and ID is a autonumber.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

Click to expand...

Click to expand...

dbalorenzini · Jan 13, 2009

OK. I think I getting a little closer. After I made the changes to the
tblStaff (added the userid and adjusted the cboEmploy combo box to look at
it. On the Password text box the input mask is set to Password and when this
line of code interpets as a set of astericks instead of it real value.

If (InStr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then

It is showing that sStoredPass = 'edward' and sPass ='******' thus they are
unequal. If I remove the input mask it works. But I ca't have that.

--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:
I had assumed your primary key was the User name (mine is). If the User name
is not your PK, you need a way to get the Autonumber from the Username. If
you have duplicate user names, this could(will) cause an issue.

So, essentially, what you need is code where you can input the User name and
return the corresponding Autonumber value. Generally, this would would look
something like this:

Dlookup("AutoNumberField", "UserTable", "UserNameField = '" & sUser & "'")

This may or may not work depending on your table. Luckily, there's a few
different ways to go about this. Post some info on your user table. We need
to know what field(s) are your Primary Keys, and if your User field will ever
contain duplicates.

If you user field does not have duplicates in the table, this is an easy
fix. If there are duplicate user names, things get a little trickier.

Let me know how the table is structured and we'll take it from there.

dbalorenzini said:

Is this the user name or ID? WHen I try [ID] I get a datatype mismatch be
cause sUser is string and ID is a autonumber.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

:

I have a bit of a quandry. I have a maco that uses SetTempVar to set the
CurrentUserID so I can filter on records for that user. This is the macro:
Condition Action Arguments
Not IsNull([cboCurrentStaff]) SetTempVar CurrentUserID, [cboCurrentStaff]
Close ,,Prompt
OpenForm Home, Form, , , , Normal
StopMacro
MsgBox You must first select a staff member., Yes, None,

The specification now calls for checking for passwords and other
validations. I created the following procedure:
Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box

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

Click to expand...

Click to expand...

Dymondjack · Jan 13, 2009

Assuming that "Staff Name" is a calculated field based on the First and Last
name, and assuming that the 'User Table' that we have been previously
referring to is in fact a table, and assuming that you are POSITIVE you will
never end up with two "Staff Name"s that are identical, here's what you need
to do:

1) Dimension a variable to hold the User ID (Autonumber) towards the
beginning of the function:

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Dim lngUser as Long 'ID of sUser
Static iLogAttempts as Integer 'Number of Login Attempts

2) Get the User ID after User has been verified, but before you try and
verify the pass:

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get User ID
lngUser = Dlookup("ID", "QueryName", "[Staff Name] = '" & sUser & "'")

3) MAKE SURE lngUser IS RETURNING CORRECTLY!!!

4) Change the "Get Password" part from this:

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

to this:

sStoredPass = Nz(Dlookup("MyPassField", "MyPassQuery", "ID = " & lngUser), "")

Note the lack of quotations in the second example as compared to the first.
This passes the value of lngUser as a numeric value rather than a string
value as in the first example (hence your datatype mismatch error).

The above example should work, but based on this information, the real way
to do this would be to bind the original cboUserName to the ID column, so
that the combo box actually gives the ID rather than the Name.

Anyway, try the above example. The key is to make sure that you can get the
ID from the Staff User. As long as that works, everything else should be
fine. One way to quickly test this is in the immediate window. Type:

?Dlookup("ID", "QueryName", "[Staff Name] = '" & sUser & "'")

if this returns the correct ID, make the rest of the changes and you should
be good to go.

dbalorenzini said:
The source is based on the following query:

SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First
Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First
Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First
Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last
Name])) AS [Staff Name], tblStaff.*
FROM tblStaff
ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First
Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First
Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[First
Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last
Name]));

The ID column is the primary key and Staff Name will never be duplicated.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

I had assumed your primary key was the User name (mine is). If the User name
is not your PK, you need a way to get the Autonumber from the Username. If
you have duplicate user names, this could(will) cause an issue.

So, essentially, what you need is code where you can input the User name and
return the corresponding Autonumber value. Generally, this would would look
something like this:

Dlookup("AutoNumberField", "UserTable", "UserNameField = '" & sUser & "'")

This may or may not work depending on your table. Luckily, there's a few
different ways to go about this. Post some info on your user table. We need
to know what field(s) are your Primary Keys, and if your User field will ever
contain duplicates.

If you user field does not have duplicates in the table, this is an easy
fix. If there are duplicate user names, things get a little trickier.

Let me know how the table is structured and we'll take it from there.

dbalorenzini said:

Is this the user name or ID? WHen I try [ID] I get a datatype mismatch be
cause sUser is string and ID is a autonumber.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

Click to expand...

Click to expand...

Dymondjack · Jan 13, 2009

make sure you refer to the textbox value .text rather than the value

Ex:
Me.txtPassword.Text
instead of
Me.txtPassword

I have an unbound control which holds the password (with the same input
mask) and this works fine.

I don't know if it will make a difference being bound/unbound

dbalorenzini said:
OK. I think I getting a little closer. After I made the changes to the
tblStaff (added the userid and adjusted the cboEmploy combo box to look at
it. On the Password text box the input mask is set to Password and when this
line of code interpets as a set of astericks instead of it real value.

If (InStr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then

It is showing that sStoredPass = 'edward' and sPass ='******' thus they are
unequal. If I remove the input mask it works. But I ca't have that.

--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

I had assumed your primary key was the User name (mine is). If the User name
is not your PK, you need a way to get the Autonumber from the Username. If
you have duplicate user names, this could(will) cause an issue.

So, essentially, what you need is code where you can input the User name and
return the corresponding Autonumber value. Generally, this would would look
something like this:

Dlookup("AutoNumberField", "UserTable", "UserNameField = '" & sUser & "'")

This may or may not work depending on your table. Luckily, there's a few
different ways to go about this. Post some info on your user table. We need
to know what field(s) are your Primary Keys, and if your User field will ever
contain duplicates.

If you user field does not have duplicates in the table, this is an easy
fix. If there are duplicate user names, things get a little trickier.

Let me know how the table is structured and we'll take it from there.

dbalorenzini said:

Is this the user name or ID? WHen I try [ID] I get a datatype mismatch be
cause sUser is string and ID is a autonumber.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value)

Will return true regardless of case. Maybe you're fine with this, but if
you do want case sensitive, I use the following:

If (Instr(1, <StoredPass>, <UserInputPass>, vbBinaryCompare) = 1) And _
(Len(<StoredPass>) = Len(<UserInputPass>)) Then
'Password Verification OK
End If

HTH
-jack

Click to expand...

Click to expand...

dbalorenzini · Jan 13, 2009

This is what I have in my login click event:

Private Sub cmdLogin_Click()
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee.Value, ""), Nz(Me.txtPassword.Text, "")
End Sub

it's a unbound text box but it is still passsing in the'*' instead of the
actual text.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:
make sure you refer to the textbox value .text rather than the value

Ex:
Me.txtPassword.Text
instead of
Me.txtPassword

I have an unbound control which holds the password (with the same input
mask) and this works fine.

I don't know if it will make a difference being bound/unbound

dbalorenzini said:

OK. I think I getting a little closer. After I made the changes to the
tblStaff (added the userid and adjusted the cboEmploy combo box to look at
it. On the Password text box the input mask is set to Password and when this
line of code interpets as a set of astericks instead of it real value.

If (InStr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then

It is showing that sStoredPass = 'edward' and sPass ='******' thus they are
unequal. If I remove the input mask it works. But I ca't have that.

--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

I had assumed your primary key was the User name (mine is). If the User name
is not your PK, you need a way to get the Autonumber from the Username. If
you have duplicate user names, this could(will) cause an issue.

So, essentially, what you need is code where you can input the User name and
return the corresponding Autonumber value. Generally, this would would look
something like this:

Dlookup("AutoNumberField", "UserTable", "UserNameField = '" & sUser & "'")

This may or may not work depending on your table. Luckily, there's a few
different ways to go about this. Post some info on your user table. We need
to know what field(s) are your Primary Keys, and if your User field will ever
contain duplicates.

If you user field does not have duplicates in the table, this is an easy
fix. If there are duplicate user names, things get a little trickier.

Let me know how the table is structured and we'll take it from there.

:

Is this the user name or ID? WHen I try [ID] I get a datatype mismatch be
cause sUser is string and ID is a autonumber.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Click to expand...

Click to expand...

Dymondjack · Jan 13, 2009

My apologies... try only Me.txtPassword (without the .Text)

Me.ctlPassword.SetFocus
sPass = Nz(Me.ctlPassword, "")

The above came directly from my Click event on the login form.

dbalorenzini said:
This is what I have in my login click event:

Private Sub cmdLogin_Click()
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee.Value, ""), Nz(Me.txtPassword.Text, "")
End Sub

it's a unbound text box but it is still passsing in the'*' instead of the
actual text.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

make sure you refer to the textbox value .text rather than the value

Ex:
Me.txtPassword.Text
instead of
Me.txtPassword

I have an unbound control which holds the password (with the same input
mask) and this works fine.

I don't know if it will make a difference being bound/unbound

dbalorenzini said:

OK. I think I getting a little closer. After I made the changes to the
tblStaff (added the userid and adjusted the cboEmploy combo box to look at
it. On the Password text box the input mask is set to Password and when this
line of code interpets as a set of astericks instead of it real value.

If (InStr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then

It is showing that sStoredPass = 'edward' and sPass ='******' thus they are
unequal. If I remove the input mask it works. But I ca't have that.

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I had assumed your primary key was the User name (mine is). If the User name
is not your PK, you need a way to get the Autonumber from the Username. If
you have duplicate user names, this could(will) cause an issue.

So, essentially, what you need is code where you can input the User name and
return the corresponding Autonumber value. Generally, this would would look
something like this:

Dlookup("AutoNumberField", "UserTable", "UserNameField = '" & sUser & "'")

This may or may not work depending on your table. Luckily, there's a few
different ways to go about this. Post some info on your user table. We need
to know what field(s) are your Primary Keys, and if your User field will ever
contain duplicates.

If you user field does not have duplicates in the table, this is an easy
fix. If there are duplicate user names, things get a little trickier.

Let me know how the table is structured and we'll take it from there.

:

Is this the user name or ID? WHen I try [ID] I get a datatype mismatch be
cause sUser is string and ID is a autonumber.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Click to expand...

Click to expand...

dbalorenzini · Jan 13, 2009

That solved that issue. Thanks. Ok now the next question is now that the Home
form is open. I stored the Staff Name into a public variable called
sLoggedInUser. What I would liek to happen is to have that Staff Name that is
in the public variable inserted into the cboCurrentStaff combo box on the
Home form. This should filter my subforms on the Home form.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:
make sure you refer to the textbox value .text rather than the value

Ex:
Me.txtPassword.Text
instead of
Me.txtPassword

I have an unbound control which holds the password (with the same input
mask) and this works fine.

I don't know if it will make a difference being bound/unbound

dbalorenzini said:

OK. I think I getting a little closer. After I made the changes to the
tblStaff (added the userid and adjusted the cboEmploy combo box to look at
it. On the Password text box the input mask is set to Password and when this
line of code interpets as a set of astericks instead of it real value.

If (InStr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then

It is showing that sStoredPass = 'edward' and sPass ='******' thus they are
unequal. If I remove the input mask it works. But I ca't have that.

--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

I had assumed your primary key was the User name (mine is). If the User name
is not your PK, you need a way to get the Autonumber from the Username. If
you have duplicate user names, this could(will) cause an issue.

So, essentially, what you need is code where you can input the User name and
return the corresponding Autonumber value. Generally, this would would look
something like this:

Dlookup("AutoNumberField", "UserTable", "UserNameField = '" & sUser & "'")

This may or may not work depending on your table. Luckily, there's a few
different ways to go about this. Post some info on your user table. We need
to know what field(s) are your Primary Keys, and if your User field will ever
contain duplicates.

If you user field does not have duplicates in the table, this is an easy
fix. If there are duplicate user names, things get a little trickier.

Let me know how the table is structured and we'll take it from there.

:

Is this the user name or ID? WHen I try [ID] I get a datatype mismatch be
cause sUser is string and ID is a autonumber.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,
you can refer to it in an event procedure (event procedure: A procedure that
is automatically executed in response to an event initiated by the user or
program code, or that is triggered by the system.), a Visual Basic for
Applications (VBA) (Visual Basic for Applications (VBA): A macro-language
version of Microsoft Visual Basic that is used to program Microsoft
Windows-based applications and is included with several Microsoft programs.)
module, a query, or an expression. For example, if you created a temporary
variable named MyVar, you could use the variable as the control source for a
text box by using the following syntax:
=[TempVars]![MyVar]

On a side note, in case you weren't aware, your password verification is not
case sensitive

Click to expand...

Click to expand...

Dymondjack · Jan 13, 2009

In the OnOpen procedure of the Home form:

Me.cboCurrentStaff = sLoggedInUser

Personally, I prefer the OpenArgs method... public variables have a tendancy
to lose thier value and are in general are fairly tricksome.

To accomplish this using the OpenArgs, find the line in your udf_Login that
opens the form... the last argument to DoCmd.OpenForm is Openargs. Your line
should look like this:

DoCmd.OpenForm "frmHome", , , , , , sUser

This will pass the value of sUser (or whatever else you put there) to the
forms OpenArgs. Then, in your Form_OnOpen event, put the following:

If IsNull(Me.OpenArgs) = False Then
Me.cboCurrentStaff = Me.OpenArgs
End If

dbalorenzini said:
That solved that issue. Thanks. Ok now the next question is now that the Home
form is open. I stored the Staff Name into a public variable called
sLoggedInUser. What I would liek to happen is to have that Staff Name that is
in the public variable inserted into the cboCurrentStaff combo box on the
Home form. This should filter my subforms on the Home form.
--
Thanks,
Art
Database Administrator
Yankton, SD

Dymondjack said:

make sure you refer to the textbox value .text rather than the value

Ex:
Me.txtPassword.Text
instead of
Me.txtPassword

I have an unbound control which holds the password (with the same input
mask) and this works fine.

I don't know if it will make a difference being bound/unbound

dbalorenzini said:

OK. I think I getting a little closer. After I made the changes to the
tblStaff (added the userid and adjusted the cboEmploy combo box to look at
it. On the Password text box the input mask is set to Password and when this
line of code interpets as a set of astericks instead of it real value.

If (InStr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then

It is showing that sStoredPass = 'edward' and sPass ='******' thus they are
unequal. If I remove the input mask it works. But I ca't have that.

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I had assumed your primary key was the User name (mine is). If the User name
is not your PK, you need a way to get the Autonumber from the Username. If
you have duplicate user names, this could(will) cause an issue.

So, essentially, what you need is code where you can input the User name and
return the corresponding Autonumber value. Generally, this would would look
something like this:

Dlookup("AutoNumberField", "UserTable", "UserNameField = '" & sUser & "'")

This may or may not work depending on your table. Luckily, there's a few
different ways to go about this. Post some info on your user table. We need
to know what field(s) are your Primary Keys, and if your User field will ever
contain duplicates.

If you user field does not have duplicates in the table, this is an easy
fix. If there are duplicate user names, things get a little trickier.

Let me know how the table is structured and we'll take it from there.

:

Is this the user name or ID? WHen I try [ID] I get a datatype mismatch be
cause sUser is string and ID is a autonumber.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

You're where condition is trying to find data in the Password field that is
equal to the username ("Password = '" & sUser & "'")

Change 'Password' to whatever field name holds the User

"fldUser = '" & sUser & "'"

So you're line should look like this:

sStoredPass = Nz(Dlookup("Password", "Staff Extended", "MyUserField = '" &
sUser & "'"), "")

:

I think this might work but the following line is giving me fits:

'Get stored password
sStoredPass = Nz(DLookup("Password", "Staff Extended", "Password = '" &
sUser & "'"), "")

This is alwas coming blank.

Password is the name of the field
Staff Extended is the name of the query

sUser has the right USerID so I am not sure what is going on.
--
Thanks,
Art
Database Administrator
Yankton, SD

:

In a new or existing standard module:

'CODE START
Public Function udf_Login(sUser As String, _
sPass As String) As Long
On Error Goto Error_udf_Login

Dim Ret as Long 'Return Value
Dim sStoredPass as String 'Correct Password from user table
Static iLogAttempts as Integer 'Number of Login Attempts

Ret = 0 'Initialize Return
iLogAttempts = iLogAttempts + 1

'Verify acceptable login attempts
If iLogAttempts > 3 Then
MsgBox "You do not have access to this database..."
Application.Quit
End If

'Verify User
If Len(sUser) = 0 Then
Ret = 2
MsgBox "Please select a User to login."
Goto Exit_udf_Login
End If

'Get Stored Password
sStoredPass = Nz(Dlookup("MyPassField", "MyPassTable", "MyPassField = '" _
& sUser & "'"), "")

'Verify Password
If (Instr(1, sStoredPass, sPass) <> 1) Or (Len(sStoredPass) <> Len(sPass))
Then
Ret = 3
MsgBox "Password Incorrect, Please try again."
Goto Exit_udf_Login
End If

'User & Pass verified
DoCmd.Close acForm, "Login Dialog", acSaveNo
Docmd.OpenForm "Home"

'Save the logged user to somwhere, in this case to a control on form Home
Forms!Home.Controls!ctlCurrUser.Value = sUser

'If everything went well and the function made it this far...
iLogAttempts = 0 'Reset Login Attempt Counter
Ret = 1 'Return a value (not required in this case but good practice
nonetheless)

Exit_udf_Login:
udf_login = Ret
Exit Function
Error_udf_Login:
Ret = 0
Resume Exit_udf_Login
Resume
End Function
'CODE END

And in the btnLogin_Click event of form Login Dialog:
'CODE START
Private Sub btnLogin_Click
Me.txtPassword.SetFocus
udf_Login Nz(Me.cboEmployee, "") Nz(Me.txtPassword.Text, "")
End Sub

This code is UNTESTED and may be subject to fine tuning. I would strongly
suggest renaming your forms to "frmLoginDialog" and "frmHome" Or at least
anything else besides Home... that sounds very much like a reserved word in
some reference or another.

-jack

:

Could you help me write this as a function? Is it as simple as changing my
reference to my controls (Me.cboEmployee and Me.txtPassword) to reference
sUSer and sPass?

Public Function udf_Login(sUser, sPass)
'Check to see if data is entered into the UserName combo box

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

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

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

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

If Me.txtPassword.Value = DLookup("Password", "tblStaff", "[ID]=" &
Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login Dialog", acSaveNo
DoCmd.OpenForm "Home"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid
Entry!"
Me.txtPassword.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 Function

--
Thanks,
Art
Database Administrator
Yankton, SD

:

I not sure about doing that in a macro, I don't generally use them. If I had
to run a login procedure off a macro I would just make a function to call
from the macro that does the checks and opens the form if everything passes.

in the macro:
RunCode
=fLogin(Me.ctlUser, Me.ctlPass)

and the function:
Public Function fLogin(sUser, sPass)
'check that the user exists
'make sure user isnt already logged on
'verify password
'open frmHome and close frmLogin
End Function

As far as the TempVars I'm not really sure, I don't have any experience with
them (they're new to 07 i think? i'm on 03), but I run an Active Users table.
A record of the user, computer and the winuser are logged from fLogin and
fLogout on my frmHome_Close event removes the entry (and other cleanup..).

This might be a little off leauge with what you were looking, I wouldn't
know how to handle it with a macro, but I just got done wrapping up a solid
login/out procedure about a month ago and that worked out well for me.

:

Not sure how to approach that. Woould there be a way to do the password
validation within the Macro?
--
Thanks,
Art
Database Administrator
Yankton, SD

:

Taken from:
http://office.microsoft.com/en-us/access/HA101202161033.aspx

Temporary variables are global. Once a temporary variable has been created,

Click to expand...

Click to expand...