Choose Form that Loads Based on Login

R

ryguy7272

I received some great help here and I am trying to do a few more things now.
I have the Access 2007 Bible, which is great, but is doesn’t seem to answer
my questions so I have returned here once again. Just yesterday I got some
help with the code below (with modifications…which causes it to not work):

'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub
'End Sub
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

'Remeber: Login is a text field and that you really want to get the
'Password returned by Dlookup. You need to surround the value of
'CboEmployee with quote marks so it is treated as a string.


If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=Director" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=RVP" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
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
admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


I am now trying to figure out how to modify this slightly to check the Table
tblLogin for the individual’s Login name and the person’s password, and then
if the individual is a ‘Director’ open one Form (frm Director) and if the
individual is an ‘RVP’ open another Form (frmRVP). The individual’s level
comes from a field named ‘Level’. The code, as it is now, doesn’t do
anything; not error, just nothing.

I’d really appreciate any help on this.


Thanks,
Ryan---
 
K

Klatuu

Here is how I would do it. It doesn't require a command button.

Don't bother to set the focus to txtPassword, just make it the next control
in the tab order.
Notice I do the password checking in the password control's before update
event.

I also see some inconsistency as to the data type of the employee in the
Login field.
Your original code was addressing it as if it is text, but also in your code
I see you assign it to a variable with a name that makes it appear is if it
is numeric. the code below is expecting it to be text. If it is not,
changes will be required. Also, be aware this is all air code and will
probably have syntax errors to iron out.


Private Sub txtPassword_BeforeUpdate(Cancel As Integer)
Static lngReTrys as Long
Dim rst As DAO.Recordset
Dim srtFormName As String

'Check for number of tries

If lngRetrys > 3 Then
Docmd.Close acForm, Me.Name, acSaveNo
End If

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

If Me.cboEmployee & vbNullString = vbNullstring Then
MsgBox "You must enter a User Name.", vbExclamation + _
vbOKOnly, "Required Data"
Cancel = True
Me.cboEmployee.SetFocus
Exit Sub
End If

If Me.txtPassword & vbNullString = vbNullString Then
MsgBox "You must enter a Password.", vbExclamation + _
vbOKOnly, "Required Data"
Cancel = True
lngRetrys = lngRetrys + 1
Exit Sub
End If

Set rst = Currentdb.OpenRecordset("tblLogin", dbOpenDynaset)
With rst
.FindFirst "[Login] = """ & Me.cboEmployee & """"
If .NoMatch then
MsgBox "Invalid User Name"
Cancel = True
lngRetrys = lngRetrys + 1
.Close
Me.cboEmployee.SetFocus
Exit Sub
End If ![Password] <> Me.txtPassword Then
MsgBox "Invalid Password"
Cancel = True
lngRetrys = lngRetrys + 1
.Close
Exit Sub
End If

'If we got to here, the user and password are good
'select a form to open

If ![Level] = "Director" Then
strFormName = "frmDirector"
Else
strFormName = "frmRVP"
End If

.Close
End With

Docmd.OpenFrom strFormName
End Sub

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=Director" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=RVP" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
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
admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


--
Dave Hargis, Microsoft Access MVP


ryguy7272 said:
I received some great help here and I am trying to do a few more things now.
I have the Access 2007 Bible, which is great, but is doesn’t seem to answer
my questions so I have returned here once again. Just yesterday I got some
help with the code below (with modifications…which causes it to not work):

'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub
'End Sub
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

'Remeber: Login is a text field and that you really want to get the
'Password returned by Dlookup. You need to surround the value of
'CboEmployee with quote marks so it is treated as a string.


If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=Director" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=RVP" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
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
admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


I am now trying to figure out how to modify this slightly to check the Table
tblLogin for the individual’s Login name and the person’s password, and then
if the individual is a ‘Director’ open one Form (frm Director) and if the
individual is an ‘RVP’ open another Form (frmRVP). The individual’s level
comes from a field named ‘Level’. The code, as it is now, doesn’t do
anything; not error, just nothing.

I’d really appreciate any help on this.


Thanks,
Ryan---
Click to expand...
 
R

ryguy7272

Thanks for the suggestions Dave, but I really don't understand this code very
well. What you gave me didn't work, and I don't even know why.

I am much more comfortable working with Excel and I am working with Access.
The project that I am working on now is not going to fit into a 2D Excel
format, so I am leveraging what I know about Access (which is a little, but
only a little), and just trying to resolve a few issues in my office. Isn't
there a way of using the original VBA syntax that I had before, modifying it
just a little, and getting the macro to search for the Login in name as well
as the individual’s 'Level', and then open the appropriate Form? I'd like to
stick pretty close to the original code. I'll fiddle with it a little more
and try to make it work. If you have any ideas in the meantime, Dave, please
do post back.

Regards,
Ryan---

--
RyGuy


Klatuu said:
Here is how I would do it. It doesn't require a command button.

Don't bother to set the focus to txtPassword, just make it the next control
in the tab order.
Notice I do the password checking in the password control's before update
event.

I also see some inconsistency as to the data type of the employee in the
Login field.
Your original code was addressing it as if it is text, but also in your code
I see you assign it to a variable with a name that makes it appear is if it
is numeric. the code below is expecting it to be text. If it is not,
changes will be required. Also, be aware this is all air code and will
probably have syntax errors to iron out.


Private Sub txtPassword_BeforeUpdate(Cancel As Integer)
Static lngReTrys as Long
Dim rst As DAO.Recordset
Dim srtFormName As String

'Check for number of tries

If lngRetrys > 3 Then
Docmd.Close acForm, Me.Name, acSaveNo
End If

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

If Me.cboEmployee & vbNullString = vbNullstring Then
MsgBox "You must enter a User Name.", vbExclamation + _
vbOKOnly, "Required Data"
Cancel = True
Me.cboEmployee.SetFocus
Exit Sub
End If

If Me.txtPassword & vbNullString = vbNullString Then
MsgBox "You must enter a Password.", vbExclamation + _
vbOKOnly, "Required Data"
Cancel = True
lngRetrys = lngRetrys + 1
Exit Sub
End If

Set rst = Currentdb.OpenRecordset("tblLogin", dbOpenDynaset)
With rst
.FindFirst "[Login] = """ & Me.cboEmployee & """"
If .NoMatch then
MsgBox "Invalid User Name"
Cancel = True
lngRetrys = lngRetrys + 1
.Close
Me.cboEmployee.SetFocus
Exit Sub
End If ![Password] <> Me.txtPassword Then
MsgBox "Invalid Password"
Cancel = True
lngRetrys = lngRetrys + 1
.Close
Exit Sub
End If

'If we got to here, the user and password are good
'select a form to open

If ![Level] = "Director" Then
strFormName = "frmDirector"
Else
strFormName = "frmRVP"
End If

.Close
End With

Docmd.OpenFrom strFormName
End Sub

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=Director" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=RVP" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
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
admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


--
Dave Hargis, Microsoft Access MVP


ryguy7272 said:
I received some great help here and I am trying to do a few more things now.
I have the Access 2007 Bible, which is great, but is doesn’t seem to answer
my questions so I have returned here once again. Just yesterday I got some
help with the code below (with modifications…which causes it to not work):

'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub
'End Sub
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

'Remeber: Login is a text field and that you really want to get the
'Password returned by Dlookup. You need to surround the value of
'CboEmployee with quote marks so it is treated as a string.


If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=Director" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=RVP" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
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
admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


I am now trying to figure out how to modify this slightly to check the Table
tblLogin for the individual’s Login name and the person’s password, and then
if the individual is a ‘Director’ open one Form (frm Director) and if the
individual is an ‘RVP’ open another Form (frmRVP). The individual’s level
comes from a field named ‘Level’. The code, as it is now, doesn’t do
anything; not error, just nothing.

I’d really appreciate any help on this.


Thanks,
Ryan---
Click to expand...
Click to expand...
 
K

Klatuu

It appears the code you posted wouldn't work at all. As I said before, it
may have issues because I wrote the code in the message editor. It is late
today, but I do have something similar I can tweak using your names and send
it to you tomorrow.
--
Dave Hargis, Microsoft Access MVP


ryguy7272 said:
Thanks for the suggestions Dave, but I really don't understand this code very
well. What you gave me didn't work, and I don't even know why.

I am much more comfortable working with Excel and I am working with Access.
The project that I am working on now is not going to fit into a 2D Excel
format, so I am leveraging what I know about Access (which is a little, but
only a little), and just trying to resolve a few issues in my office. Isn't
there a way of using the original VBA syntax that I had before, modifying it
just a little, and getting the macro to search for the Login in name as well
as the individual’s 'Level', and then open the appropriate Form? I'd like to
stick pretty close to the original code. I'll fiddle with it a little more
and try to make it work. If you have any ideas in the meantime, Dave, please
do post back.

Regards,
Ryan---

--
RyGuy


Klatuu said:
Here is how I would do it. It doesn't require a command button.

Don't bother to set the focus to txtPassword, just make it the next control
in the tab order.
Notice I do the password checking in the password control's before update
event.

I also see some inconsistency as to the data type of the employee in the
Login field.
Your original code was addressing it as if it is text, but also in your code
I see you assign it to a variable with a name that makes it appear is if it
is numeric. the code below is expecting it to be text. If it is not,
changes will be required. Also, be aware this is all air code and will
probably have syntax errors to iron out.


Private Sub txtPassword_BeforeUpdate(Cancel As Integer)
Static lngReTrys as Long
Dim rst As DAO.Recordset
Dim srtFormName As String

'Check for number of tries

If lngRetrys > 3 Then
Docmd.Close acForm, Me.Name, acSaveNo
End If

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

If Me.cboEmployee & vbNullString = vbNullstring Then
MsgBox "You must enter a User Name.", vbExclamation + _
vbOKOnly, "Required Data"
Cancel = True
Me.cboEmployee.SetFocus
Exit Sub
End If

If Me.txtPassword & vbNullString = vbNullString Then
MsgBox "You must enter a Password.", vbExclamation + _
vbOKOnly, "Required Data"
Cancel = True
lngRetrys = lngRetrys + 1
Exit Sub
End If

Set rst = Currentdb.OpenRecordset("tblLogin", dbOpenDynaset)
With rst
.FindFirst "[Login] = """ & Me.cboEmployee & """"
If .NoMatch then
MsgBox "Invalid User Name"
Cancel = True
lngRetrys = lngRetrys + 1
.Close
Me.cboEmployee.SetFocus
Exit Sub
End If ![Password] <> Me.txtPassword Then
MsgBox "Invalid Password"
Cancel = True
lngRetrys = lngRetrys + 1
.Close
Exit Sub
End If

'If we got to here, the user and password are good
'select a form to open

If ![Level] = "Director" Then
strFormName = "frmDirector"
Else
strFormName = "frmRVP"
End If

.Close
End With

Docmd.OpenFrom strFormName
End Sub

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=Director" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=RVP" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
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
admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


--
Dave Hargis, Microsoft Access MVP


ryguy7272 said:
I received some great help here and I am trying to do a few more things now.
I have the Access 2007 Bible, which is great, but is doesn’t seem to answer
my questions so I have returned here once again. Just yesterday I got some
help with the code below (with modifications…which causes it to not work):

'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub
'End Sub
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

'Remeber: Login is a text field and that you really want to get the
'Password returned by Dlookup. You need to surround the value of
'CboEmployee with quote marks so it is treated as a string.


If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=Director" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=RVP" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
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
admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


I am now trying to figure out how to modify this slightly to check the Table
tblLogin for the individual’s Login name and the person’s password, and then
if the individual is a ‘Director’ open one Form (frm Director) and if the
individual is an ‘RVP’ open another Form (frmRVP). The individual’s level
comes from a field named ‘Level’. The code, as it is now, doesn’t do
anything; not error, just nothing.

I’d really appreciate any help on this.


Thanks,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Thanks in advance Dave! I am hoping to get this part working soon, because
there are a couple more, smaller, issues that I am trying to resolve too, and
it is a lot to juggle all at once. One of my main objectives here is to
learn, and ultimately, when I know my way around Access, I plan to help
others as you have melped me.

Regards,
Ryan--

PS, any additional thoughts on that other question about passing variables
or parameters?

--
RyGuy


Klatuu said:
It appears the code you posted wouldn't work at all. As I said before, it
may have issues because I wrote the code in the message editor. It is late
today, but I do have something similar I can tweak using your names and send
it to you tomorrow.
--
Dave Hargis, Microsoft Access MVP


ryguy7272 said:
Thanks for the suggestions Dave, but I really don't understand this code very
well. What you gave me didn't work, and I don't even know why.

I am much more comfortable working with Excel and I am working with Access.
The project that I am working on now is not going to fit into a 2D Excel
format, so I am leveraging what I know about Access (which is a little, but
only a little), and just trying to resolve a few issues in my office. Isn't
there a way of using the original VBA syntax that I had before, modifying it
just a little, and getting the macro to search for the Login in name as well
as the individual’s 'Level', and then open the appropriate Form? I'd like to
stick pretty close to the original code. I'll fiddle with it a little more
and try to make it work. If you have any ideas in the meantime, Dave, please
do post back.

Regards,
Ryan---

--
RyGuy


Klatuu said:
Here is how I would do it. It doesn't require a command button.

Don't bother to set the focus to txtPassword, just make it the next control
in the tab order.
Notice I do the password checking in the password control's before update
event.

I also see some inconsistency as to the data type of the employee in the
Login field.
Your original code was addressing it as if it is text, but also in your code
I see you assign it to a variable with a name that makes it appear is if it
is numeric. the code below is expecting it to be text. If it is not,
changes will be required. Also, be aware this is all air code and will
probably have syntax errors to iron out.


Private Sub txtPassword_BeforeUpdate(Cancel As Integer)
Static lngReTrys as Long
Dim rst As DAO.Recordset
Dim srtFormName As String

'Check for number of tries

If lngRetrys > 3 Then
Docmd.Close acForm, Me.Name, acSaveNo
End If

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

If Me.cboEmployee & vbNullString = vbNullstring Then
MsgBox "You must enter a User Name.", vbExclamation + _
vbOKOnly, "Required Data"
Cancel = True
Me.cboEmployee.SetFocus
Exit Sub
End If

If Me.txtPassword & vbNullString = vbNullString Then
MsgBox "You must enter a Password.", vbExclamation + _
vbOKOnly, "Required Data"
Cancel = True
lngRetrys = lngRetrys + 1
Exit Sub
End If

Set rst = Currentdb.OpenRecordset("tblLogin", dbOpenDynaset)
With rst
.FindFirst "[Login] = """ & Me.cboEmployee & """"
If .NoMatch then
MsgBox "Invalid User Name"
Cancel = True
lngRetrys = lngRetrys + 1
.Close
Me.cboEmployee.SetFocus
Exit Sub
End If ![Password] <> Me.txtPassword Then
MsgBox "Invalid Password"
Cancel = True
lngRetrys = lngRetrys + 1
.Close
Exit Sub
End If

'If we got to here, the user and password are good
'select a form to open

If ![Level] = "Director" Then
strFormName = "frmDirector"
Else
strFormName = "frmRVP"
End If

.Close
End With

Docmd.OpenFrom strFormName
End Sub

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=Director" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=RVP" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
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
admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


--
Dave Hargis, Microsoft Access MVP


:

I received some great help here and I am trying to do a few more things now.
I have the Access 2007 Bible, which is great, but is doesn’t seem to answer
my questions so I have returned here once again. Just yesterday I got some
help with the code below (with modifications…which causes it to not work):

'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub
'End Sub
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

'Remeber: Login is a text field and that you really want to get the
'Password returned by Dlookup. You need to surround the value of
'CboEmployee with quote marks so it is treated as a string.


If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=Director" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=RVP" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
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
admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


I am now trying to figure out how to modify this slightly to check the Table
tblLogin for the individual’s Login name and the person’s password, and then
if the individual is a ‘Director’ open one Form (frm Director) and if the
individual is an ‘RVP’ open another Form (frmRVP). The individual’s level
comes from a field named ‘Level’. The code, as it is now, doesn’t do
anything; not error, just nothing.

I’d really appreciate any help on this.


Thanks,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Try the following If Me.txtPassword.Value = _
DLookup("Password", "tblLogin", _
"[Login]=""" & Me.cboEmployee.Value & """ AND [Level]=""Director""") Then

The last argument to the DLookup must be a valid WHERE string without the word
WHERE at the beginning. Also, if DLookup doesn't find a match, then it
returns NULL



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I received some great help here and I am trying to do a few more things now.
I have the Access 2007 Bible, which is great, but is doesn’t seem to answer
my questions so I have returned here once again. Just yesterday I got some
help with the code below (with modifications…which causes it to not work):

'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub
'End Sub
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

'Remeber: Login is a text field and that you really want to get the
'Password returned by Dlookup. You need to surround the value of
'CboEmployee with quote marks so it is treated as a string.


If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=Director" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=RVP" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
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
admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


I am now trying to figure out how to modify this slightly to check the Table
tblLogin for the individual’s Login name and the person’s password, and then
if the individual is a ‘Director’ open one Form (frm Director) and if the
individual is an ‘RVP’ open another Form (frmRVP). The individual’s level
comes from a field named ‘Level’. The code, as it is now, doesn’t do
anything; not error, just nothing.

I’d really appreciate any help on this.


Thanks,
Ryan---
Click to expand...
 
R

ryguy7272

Thanks John!! That did it!! Also, thank you Dave!!

Final version looks like this:
If Me.txtPassword.Value = _
DLookup("Password", "tblLogin", _
"[Login]=""" & Me.cboEmployee.Value & """ AND [Level]=""Director""")
Then
lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

Else
If Me.txtPassword.Value = _
DLookup("Password", "tblLogin", _
"[Login]=""" & Me.cboEmployee.Value & """ AND [Level]=""RVP""") Then
lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"


Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
End If


I'm coming from Excel-world, and although we have the Dlookup, I don't think
anyone really uses it. I can see, in Access-world, it is very important.
Thanks guys for getting me straightened out guys.

Regards,
Ryan---

--
RyGuy


John Spencer said:
Try the following If Me.txtPassword.Value = _
DLookup("Password", "tblLogin", _
"[Login]=""" & Me.cboEmployee.Value & """ AND [Level]=""Director""") Then

The last argument to the DLookup must be a valid WHERE string without the word
WHERE at the beginning. Also, if DLookup doesn't find a match, then it
returns NULL



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
I received some great help here and I am trying to do a few more things now.
I have the Access 2007 Bible, which is great, but is doesn’t seem to answer
my questions so I have returned here once again. Just yesterday I got some
help with the code below (with modifications…which causes it to not work):

'Private Sub cboEmployee_BeforeUpdate(Cancel As Integer)
Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub
'End Sub
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

'Remeber: Login is a text field and that you really want to get the
'Password returned by Dlookup. You need to surround the value of
'CboEmployee with quote marks so it is treated as a string.


If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=Director" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmDirector"

If Me.txtPassword.Value = DLookup("Password", "tblLogin", _
"[Login]=""" & "[Level]=RVP" & Me.cboEmployee.Value & """") Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmRVP"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
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
admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub


I am now trying to figure out how to modify this slightly to check the Table
tblLogin for the individual’s Login name and the person’s password, and then
if the individual is a ‘Director’ open one Form (frm Director) and if the
individual is an ‘RVP’ open another Form (frmRVP). The individual’s level
comes from a field named ‘Level’. The code, as it is now, doesn’t do
anything; not error, just nothing.

I’d really appreciate any help on this.


Thanks,
Ryan---
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with form login in Access 2007 0
Setting Users Permission 0
Even an error with this code I copied 8
How to create a User Login Form 5
Lock out a user from using a field 2
username and password form 3
Opening a password protected form with the signed on user's record 0
Login Help 1

Top