Macro to VBA

[dbalorenzini]

That did the trick. I want to thank you for all your help.
--
Thanks,
Art
Database Administrator
Yankton, SD

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

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

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


:

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