dlookup criteria & related

  • Thread starter Thread starter puzzled
  • Start date Start date
P

puzzled

I am trying to set up a log-in screen with UserIDs and
passwords. (Please, do NOT refer me to the database
security, it is not what I want, and I know the security
of this is limited, lol)
I have a table listing the authorized users (Useracc) and
their passwords. I have an empty table (UserID & Password
only) used as the control source for a log-in screen form.
I have managed to perform a dlookup check on the User
entered on the form back to the UserName holding table:
****************************************************
Private Sub User_BeforeUpdate(Cancel As Integer)
Dim varUserID As Variant
varUserID = DLookup("ID", "Useracc", "[UserName]=User")
If IsNull(varUserID) Then
MsgBox "This user is not recognised, please try again"
Exit Sub
End If
End Sub
*****************************************************
I have two problems:

1. I would like if when the message box is activated that
the focus shift back to the User field and not progress
through the form as if its ok really- ideally having
removed the dodgy user name as well- is this do-able?
All my attempts have resulted in errors telling me the
user field must be saved before it can get a focus- I
want it deleted, as currently if the form closes the
record still gets created.

2. I can't get a multiple criteria to look at the
password field. I've tried adding an "And..." before the
end of quotation marks in the criteria and adding the
addtional reference (ie using: UserName=User And
Password1=Password), but I'm not at all sure what exactly
goes wrong. Other examples I have seen use "&" a lot,
which I don't fathom- is it an older version of VB code?
(I also have queries that validate it all fine, but can't
work out how to reference these in the form as way to
determine an action...its been a long week and a bad day
all round...paracetamol!!)
Any assistance would be gratefully rec'd, thanx
 
puzzled said:
I am trying to set up a log-in screen with UserIDs and
passwords. (Please, do NOT refer me to the database
security, it is not what I want, and I know the security
of this is limited, lol)
I have a table listing the authorized users (Useracc) and
their passwords. I have an empty table (UserID & Password
only) used as the control source for a log-in screen form.
I have managed to perform a dlookup check on the User
entered on the form back to the UserName holding table:
****************************************************
Private Sub User_BeforeUpdate(Cancel As Integer)
Dim varUserID As Variant
varUserID = DLookup("ID", "Useracc", "[UserName]=User")
If IsNull(varUserID) Then
MsgBox "This user is not recognised, please try again"
Exit Sub
End If
End Sub
*****************************************************
I have two problems:

1. I would like if when the message box is activated that
the focus shift back to the User field and not progress
through the form as if its ok really- ideally having
removed the dodgy user name as well- is this do-able?
All my attempts have resulted in errors telling me the
user field must be saved before it can get a focus- I
want it deleted, as currently if the form closes the
record still gets created.

2. I can't get a multiple criteria to look at the
password field. I've tried adding an "And..." before the
end of quotation marks in the criteria and adding the
addtional reference (ie using: UserName=User And
Password1=Password), but I'm not at all sure what exactly
goes wrong. Other examples I have seen use "&" a lot,
which I don't fathom- is it an older version of VB code?
(I also have queries that validate it all fine, but can't
work out how to reference these in the form as way to
determine an action...its been a long week and a bad day
all round...paracetamol!!)
Click to expand...


You really need to start fathoming those & thingies, not to
mention all the quotes gobledygook ;-)

The reason for the & is that you have to place the value of
the user control in the criteria, not the name of the
control. All the "extra" quotes is because the fields in
the table are Text type fields:

DLookup("ID", "Useracc", "[UserName]=""" & User & """ AND
Password1=""" & Password & """")
 
lol, thanx Marsh
still not sure why everyone wants to exit a sub if its
not sinking though? ;-)

That code did the trick- the reason I did not fathom
the "&" etc before was that the original example code I
found did not use them and my own attempts had the quotes
in what I see now were the wrong places..

Back to the other part of the question though- is it
possible to send the cursor back to the field and remove
the typing that was incorrect as part of the process
(after displaying the message box)?

Thanx for your humourous reply in advance
-----Original Message-----
puzzled said:
I am trying to set up a log-in screen with UserIDs and
passwords. (Please, do NOT refer me to the database
security, it is not what I want, and I know the security
of this is limited, lol)
I have a table listing the authorized users (Useracc) and
their passwords. I have an empty table (UserID & Password
only) used as the control source for a log-in screen form.
I have managed to perform a dlookup check on the User
entered on the form back to the UserName holding table:
****************************************************
Private Sub User_BeforeUpdate(Cancel As Integer)
Dim varUserID As Variant
varUserID = DLookup("ID", "Useracc", "[UserName]=User")
If IsNull(varUserID) Then
MsgBox "This user is not recognised, please try again"
Exit Sub
End If
End Sub
*****************************************************
I have two problems:

1. I would like if when the message box is activated that
the focus shift back to the User field and not progress
through the form as if its ok really- ideally having
removed the dodgy user name as well- is this do-able?
All my attempts have resulted in errors telling me the
user field must be saved before it can get a focus- I
want it deleted, as currently if the form closes the
record still gets created.

2. I can't get a multiple criteria to look at the
password field. I've tried adding an "And..." before the
end of quotation marks in the criteria and adding the
addtional reference (ie using: UserName=User And
Password1=Password), but I'm not at all sure what exactly
goes wrong. Other examples I have seen use "&" a lot,
which I don't fathom- is it an older version of VB code?
(I also have queries that validate it all fine, but can't
work out how to reference these in the form as way to
determine an action...its been a long week and a bad day
all round...paracetamol!!)
Click to expand...


You really need to start fathoming those & thingies, not to
mention all the quotes gobledygook ;-)

The reason for the & is that you have to place the value of
the user control in the criteria, not the name of the
control. All the "extra" quotes is because the fields in
the table are Text type fields:

DLookup("ID", "Useracc", "[UserName]=""" & User & """ AND
Password1=""" & Password & """")
Click to expand...
 
less said:
lol, thanx Marsh
still not sure why everyone wants to exit a sub if its
not sinking though? ;-)

That code did the trick- the reason I did not fathom
the "&" etc before was that the original example code I
found did not use them and my own attempts had the quotes
in what I see now were the wrong places..

Back to the other part of the question though- is it
possible to send the cursor back to the field and remove
the typing that was incorrect as part of the process
(after displaying the message box)?
Click to expand...

One shot at humor per day is all I've got in me '-)

Sorry, I missed the other part of your question. It is
Friday, isn't it?

Anyway, keep your code in the BeforeUpdate event, just add
the line:
Cancel = True
right after the msgbox.

This prevents the control from losing the focus until an
acceptaable value is entered. Heaven help the person that
forgot their password, they'll have to kill Access to get
out of it.
--
Marsh
MVP [MS Access]


-----Original Message-----
puzzled said:
I am trying to set up a log-in screen with UserIDs and
passwords. (Please, do NOT refer me to the database
security, it is not what I want, and I know the security
of this is limited, lol)
I have a table listing the authorized users (Useracc) and
their passwords. I have an empty table (UserID & Password
only) used as the control source for a log-in screen form.
I have managed to perform a dlookup check on the User
entered on the form back to the UserName holding table:
****************************************************
Private Sub User_BeforeUpdate(Cancel As Integer)
Dim varUserID As Variant
varUserID = DLookup("ID", "Useracc", "[UserName]=User")
If IsNull(varUserID) Then
MsgBox "This user is not recognised, please try again"
Exit Sub
End If
End Sub
*****************************************************
I have two problems:

1. I would like if when the message box is activated that
the focus shift back to the User field and not progress
through the form as if its ok really- ideally having
removed the dodgy user name as well- is this do-able?
All my attempts have resulted in errors telling me the
user field must be saved before it can get a focus- I
want it deleted, as currently if the form closes the
record still gets created.

2. I can't get a multiple criteria to look at the
password field. I've tried adding an "And..." before the
end of quotation marks in the criteria and adding the
addtional reference (ie using: UserName=User And
Password1=Password), but I'm not at all sure what exactly
goes wrong. Other examples I have seen use "&" a lot,
which I don't fathom- is it an older version of VB code?
(I also have queries that validate it all fine, but can't
work out how to reference these in the form as way to
determine an action...its been a long week and a bad day
all round...paracetamol!!)
Click to expand...


You really need to start fathoming those & thingies, not to
mention all the quotes gobledygook ;-)

The reason for the & is that you have to place the value of
the user control in the criteria, not the name of the
control. All the "extra" quotes is because the fields in
the table are Text type fields:

DLookup("ID", "Useracc", "[UserName]=""" & User & """ AND
Password1=""" & Password & """")
Click to expand...
Click to expand...
 
When I claimed it did the trick I forgot to test for if
the password was right, ie not a null value- but it only
produces a null value no matter what :-(

What went wrong Marsh- is it Friday 13th?
I am feeling really dense, and a tad frustrated- at my
own inability to perform a simple task, I may add, not
with you Marsh...any ideas?
-----Original Message-----
less said:
lol, thanx Marsh
still not sure why everyone wants to exit a sub if its
not sinking though? ;-)

That code did the trick- the reason I did not fathom
the "&" etc before was that the original example code I
found did not use them and my own attempts had the quotes
in what I see now were the wrong places..

Back to the other part of the question though- is it
possible to send the cursor back to the field and remove
the typing that was incorrect as part of the process
(after displaying the message box)?
Click to expand...

One shot at humor per day is all I've got in me '-)

Sorry, I missed the other part of your question. It is
Friday, isn't it?

Anyway, keep your code in the BeforeUpdate event, just add
the line:
Cancel = True
right after the msgbox.

This prevents the control from losing the focus until an
acceptaable value is entered. Heaven help the person that
forgot their password, they'll have to kill Access to get
out of it.
--
Marsh
MVP [MS Access]


-----Original Message-----
puzzled wrote:

I am trying to set up a log-in screen with UserIDs and
passwords. (Please, do NOT refer me to the database
security, it is not what I want, and I know the security
of this is limited, lol)
I have a table listing the authorized users (Useracc) and
their passwords. I have an empty table (UserID & Password
only) used as the control source for a log-in screen form.
I have managed to perform a dlookup check on the User
entered on the form back to the UserName holding table:
****************************************************
Private Sub User_BeforeUpdate(Cancel As Integer)
Dim varUserID As Variant
varUserID = DLookup("ID", "Useracc", "[UserName] =User")
If IsNull(varUserID) Then
MsgBox "This user is not recognised, please try again"
Exit Sub
End If
End Sub
*****************************************************
I have two problems:

1. I would like if when the message box is activated that
the focus shift back to the User field and not progress
through the form as if its ok really- ideally having
removed the dodgy user name as well- is this do-able?
All my attempts have resulted in errors telling me the
user field must be saved before it can get a focus- I
want it deleted, as currently if the form closes the
record still gets created.

2. I can't get a multiple criteria to look at the
password field. I've tried adding an "And..." before the
end of quotation marks in the criteria and adding the
addtional reference (ie using: UserName=User And
Password1=Password), but I'm not at all sure what exactly
goes wrong. Other examples I have seen use "&" a lot,
which I don't fathom- is it an older version of VB code?
(I also have queries that validate it all fine, but can't
work out how to reference these in the form as way to
determine an action...its been a long week and a bad day
all round...paracetamol!!)


You really need to start fathoming those & thingies,
Click to expand...
not
to
mention all the quotes gobledygook ;-)

The reason for the & is that you have to place the
Click to expand...
value
of
the user control in the criteria, not the name of the
control. All the "extra" quotes is because the fields in
the table are Text type fields:

DLookup("ID", "Useracc", "[UserName]=""" & User & """ AND
Password1=""" & Password & """")
Click to expand...
Click to expand...

.
Click to expand...
 
PUZZLED said:
When I claimed it did the trick I forgot to test for if
the password was right, ie not a null value- but it only
produces a null value no matter what :-(
Click to expand...

Are you sure you spelled the field names correctly?

I assumed that both the user and password fields are Text
fields, is either field a numeric type?

Maybe there's some kind of typo?? Post a copy/paste of the
DLookup line.
 
I double-checked all the spellings and they were correct,
but last message did point me at what appears to have
been the cause, though I am not entirely sure it should
have been:
I had been toying with the "required" property of the
field- it was set to yes in the holding table, and no in
the empty table- I set both to yes and then it worked.
Seems an odd way to define a match, but I now learn how
sensitive it can be...
With regards to the "Cancel = True" it is a bit brutal,
isn't it?- I shall try and ponder if there isn't some
other gentler method to achieve this, perhaps involving
the closing and re-opening of the form..I had intended to
use password prompts (that the user would define when
setting up password), but its going to be difficult to
let them get at it at this rate..lol

thanx for your help Marsh, enjoy the w/e
 
Just to let you know I have managed to solve the other
parts to the problem also- how to reset it and shift the
cursor around accordingly:
After the msgbox telling them to retry I did a SendKeys
({ESC}) which reset the typed fields to null.
On the next (in tab order) control's GotFocus event I
retested the same dlookup (using varied names for my
variable)and if null I set the focus backward there (thus
avoiding the unsaved record errors)- works a treat and no
more looping cancel do-dah. I copied the code in case
anyone else (like me) likes to see how other's problems
are resolved.

happy as a sandman now!

Private Sub Password_BeforeUpdate(Cancel As Integer)
Dim UPassword As Variant
UPassword = DLookup("ID", "Useracc", "[UserName]=""" &
User & """ AND Password1 = """ & Password & """")
If IsNull(UPassword) Then
MsgBox "This password is pants- try it again"
SendKeys ("{ESC}")
End If
End Sub
______________________________________________________
'placed on next control's event procedure

Private Sub Command11_GotFocus()
Dim ULOG As Variant
ULOG = DLookup("ID", "Useracc", "[UserName]=""" & User
& """ AND Password1 = """ & Password & """")
If IsNull(ULOG) Then
Me![User].SetFocus
End If
End Sub
 
puzzle said:
Just to let you know I have managed to solve the other
parts to the problem also- how to reset it and shift the
cursor around accordingly:
After the msgbox telling them to retry I did a SendKeys
({ESC}) which reset the typed fields to null.
On the next (in tab order) control's GotFocus event I
retested the same dlookup (using varied names for my
variable)and if null I set the focus backward there (thus
avoiding the unsaved record errors)- works a treat and no
more looping cancel do-dah. I copied the code in case
anyone else (like me) likes to see how other's problems
are resolved.

happy as a sandman now!
Click to expand...


Just goes to prove the old adage that ignorance is bliss.

Don't mean to slap you upside the head with a brick here,
but SendKeys is something to be avoided like the plague. It
has never worked well, toggling the NumLock setting and
sending the Esc to the wrong form or even another
application are a couple of its more infamous drawbacks.
Time to go back to the drawing board.

If you want to clear the values, you can use the Me.Undo
method instead.

Something else to consider is to use a counter and bail out
after the user makes a limited number of failed attemts to
enter a valid user name or password. E.g:

Private Sub User_BeforeUpdate(Cancel As Integer)
Static intAttempts As Integer
Dim varUserID As Variant
varUserID = DLookup("ID", "Useracc", "[UserName]=User")
If IsNull(varUserID) Then
MsgBox "This user is not recognised, please try again"
Me.Undo
Cancel = True
If intAttempts > 3 Then
Application.Quit
End If
End If
End Sub
 
Back
Top