Password

[Dave Hawks]

I am using the following code to limit the ability of users to delete records.
Private Sub Player1_BeforeUpdate(Cancel As Integer)
Dim Response As String

Dim Password, Loggin
Password = "pwd"
If Not IsNull(Player1.OldValue) Then

Response = MsgBox("To avoid unauthorised deletions please enter your
password ", vbOKOnly)
If Response = vbOK Then

Loggin = InputBox("Enter Password")
If Loggin <> Password Then
MsgBox "Wrong Password"
Cancel = True
Player1.Undo

Else
End If
End If
End If
End Sub

I would like to allocate each user their own password, can anyone suugest a
method of comparing the entered password against a list of allowable
passwords.

 

[Randy Wayne]

Create a table of users. (ie. tblUsers)
Each user has a "UserID" and a "Password"

All UserIDs must be unique.

Simpley create a WHERE statement that looks at UserID and Password

Example:

UserID: dhawks
Password: pwd

Then your "test" would be:

"Select * from tblUsers WHERE UserId = '" & Me.txtUser.Value & "'AND
Password = '" Me.txtPassword & "'"

(Note single and double quotes. If you do not have experience passing a
value from a form write back.)

If BOF and EOF then '(This tests if there are any records in the recordset)
'No Match - Add code to tell user they are not allowed like your "wrong
password"
Else
'There is a match -- add code to allow them to delete
End if

Let me know if this helps

Randy

 

[Dave Hawks]

Randy
Thanks for the suggestion, but I have no experience in passing values from a
form so I would appreciate a bit more help.
I have created the tblUsers, but I am unure of the syntax for the where
statement and how that should be inserted into my original code.
Thanks

 

[Randy Wayne]

Here is one method:

Assumptions -
Table named tblUsers
fields in tblUsers - USERID and PWORD

Form named frmAuthenticate
textboxes on frmAuthenticate - txtUserID and txtPword
command button on form - cmdAuthenticate


Private Sub cmdAuthenticate_Click()
On Error GoTo Err_cmdAuthenticate_Click


Dim oRS As ADODB.Recordset

strSQL = "SELECT * FROM [tblUsers] " & _
"WHERE USERID = '" & Me.txtUserID.Value & "' AND" & _
"PWORD = '" & Me.txtPword.value & "'"

Set oRS = New ADODB.Recordset
oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic

'Ensure there is a match...
If oRS.EOF And oRS.BOF Then 'if this is true, there is no match...
'Insert Code to tell the User they do not have permission...
'Like a message box with no choices...

'Clean up and close recordset...
oRS.Close
Set oRS = Nothing
Exit Sub
Else
'Insert Code to allow the user to delete...
'Like setting the delete property of the form to YES...

oRS.Close
Set oRS = Nothing

End If

Exit_cmdAuthenticate_Click:
Exit Sub

Err_cmdAuthenticate_Click:
MsgBox Err.Description
Resume Exit_cmdAuthenticate_Click


The BOF (Beginning of File) EOF (End of File) method checks to see if there
is any record. If BOF and EOF are both true then the recordset is empty,
therefore no match.

If I typed it correctly you should be able to copy the above code and insert
it on the Click event for the cmdAuthenticate button. You can add additonal
code to communicate with the user as you wish.

Let me know if this helps.


Randy

 

[Dave Hawks]

Randy
I created the form from the UserId table and inserted your code on the click
event but got a syntax error message on the line

-- oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
Any Idea what may be wrong.
Thanks
Dave Hawks

Here is one method:

Assumptions -
Table named tblUsers
fields in tblUsers - USERID and PWORD

Form named frmAuthenticate
textboxes on frmAuthenticate - txtUserID and txtPword
command button on form - cmdAuthenticate


Private Sub cmdAuthenticate_Click()
On Error GoTo Err_cmdAuthenticate_Click


Dim oRS As ADODB.Recordset

strSQL = "SELECT * FROM [tblUsers] " & _
"WHERE USERID = '" & Me.txtUserID.Value & "' AND" & _
"PWORD = '" & Me.txtPword.value & "'"

Set oRS = New ADODB.Recordset
oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic

'Ensure there is a match...
If oRS.EOF And oRS.BOF Then 'if this is true, there is no match...
'Insert Code to tell the User they do not have permission...
'Like a message box with no choices...

'Clean up and close recordset...
oRS.Close
Set oRS = Nothing
Exit Sub
Else
'Insert Code to allow the user to delete...
'Like setting the delete property of the form to YES...

oRS.Close
Set oRS = Nothing

End If

Exit_cmdAuthenticate_Click:
Exit Sub

Err_cmdAuthenticate_Click:
MsgBox Err.Description
Resume Exit_cmdAuthenticate_Click


The BOF (Beginning of File) EOF (End of File) method checks to see if there
is any record. If BOF and EOF are both true then the recordset is empty,
therefore no match.

If I typed it correctly you should be able to copy the above code and insert
it on the Click event for the cmdAuthenticate button. You can add additonal
code to communicate with the user as you wish.

Let me know if this helps.


Randy

Randy
Thanks for the suggestion, but I have no experience in passing values from a
form so I would appreciate a bit more help.
I have created the tblUsers, but I am unure of the syntax for the where
statement and how that should be inserted into my original code.
Thanks

 

[Randy Wayne]

Make sure the adLockOptimistic is on the same line as the rest of the line.
It looks like the screen on the Discussion Group message moved it to the next
line.

If you line ends with adOpenForwardOnly,

then that is "a" problem. Simply move the adLock Optimistic to the right of
the comma.

Let me know if that is the case.


Randy

Randy
I created the form from the UserId table and inserted your code on the click
event but got a syntax error message on the line

-- oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
Any Idea what may be wrong.
Thanks
Dave Hawks

Here is one method:

Assumptions -
Table named tblUsers
fields in tblUsers - USERID and PWORD

Form named frmAuthenticate
textboxes on frmAuthenticate - txtUserID and txtPword
command button on form - cmdAuthenticate


Private Sub cmdAuthenticate_Click()
On Error GoTo Err_cmdAuthenticate_Click


Dim oRS As ADODB.Recordset

strSQL = "SELECT * FROM [tblUsers] " & _
"WHERE USERID = '" & Me.txtUserID.Value & "' AND" & _
"PWORD = '" & Me.txtPword.value & "'"

Set oRS = New ADODB.Recordset
oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic

'Ensure there is a match...
If oRS.EOF And oRS.BOF Then 'if this is true, there is no match...
'Insert Code to tell the User they do not have permission...
'Like a message box with no choices...

'Clean up and close recordset...
oRS.Close
Set oRS = Nothing
Exit Sub
Else
'Insert Code to allow the user to delete...
'Like setting the delete property of the form to YES...

oRS.Close
Set oRS = Nothing

End If

Exit_cmdAuthenticate_Click:
Exit Sub

Err_cmdAuthenticate_Click:
MsgBox Err.Description
Resume Exit_cmdAuthenticate_Click


The BOF (Beginning of File) EOF (End of File) method checks to see if there
is any record. If BOF and EOF are both true then the recordset is empty,
therefore no match.

If I typed it correctly you should be able to copy the above code and insert
it on the Click event for the cmdAuthenticate button. You can add additonal
code to communicate with the user as you wish.

Let me know if this helps.


Randy

Randy
Thanks for the suggestion, but I have no experience in passing values from a
form so I would appreciate a bit more help.
I have created the tblUsers, but I am unure of the syntax for the where
statement and how that should be inserted into my original code.
Thanks
--
Dave Hawks


:

Create a table of users. (ie. tblUsers)
Each user has a "UserID" and a "Password"

All UserIDs must be unique.

Simpley create a WHERE statement that looks at UserID and Password

Example:

UserID: dhawks
Password: pwd

Then your "test" would be:

"Select * from tblUsers WHERE UserId = '" & Me.txtUser.Value & "'AND
Password = '" Me.txtPassword & "'"

(Note single and double quotes. If you do not have experience passing a
value from a form write back.)

If BOF and EOF then '(This tests if there are any records in the recordset)
'No Match - Add code to tell user they are not allowed like your "wrong
password"
Else
'There is a match -- add code to allow them to delete
End if

Let me know if this helps

Randy


:

I am using the following code to limit the ability of users to delete records.
Private Sub Player1_BeforeUpdate(Cancel As Integer)
Dim Response As String

Dim Password, Loggin
Password = "pwd"
If Not IsNull(Player1.OldValue) Then

Response = MsgBox("To avoid unauthorised deletions please enter your
password ", vbOKOnly)
If Response = vbOK Then

Loggin = InputBox("Enter Password")
If Loggin <> Password Then
MsgBox "Wrong Password"
Cancel = True
Player1.Undo

Else
End If
End If
End If
End Sub

I would like to allocate each user their own password, can anyone suugest a
method of comparing the entered password against a list of allowable
passwords.

 

[Dave Hawks]

Randy
I have done as you suggested which has cleared the syntax error.
When I Click frmAuthenticate I get an error message
Syntax error (missing operator) in query expression"USERID = "hawks"
ANDPWORD =hawksd123 "".

The tblUsers has fields USERID and PWORD and hawks and hawkd123 were the
user id and password I typed into frmAuthenticate before clicking
cmdAuthenticate.
Thanks
--
Dave Hawks

Make sure the adLockOptimistic is on the same line as the rest of the line.
It looks like the screen on the Discussion Group message moved it to the next
line.

If you line ends with adOpenForwardOnly,

then that is "a" problem. Simply move the adLock Optimistic to the right of
the comma.

Let me know if that is the case.


Randy

Randy
I created the form from the UserId table and inserted your code on the click
event but got a syntax error message on the line

-- oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
Any Idea what may be wrong.
Thanks
Dave Hawks

Here is one method:

Assumptions -
Table named tblUsers
fields in tblUsers - USERID and PWORD

Form named frmAuthenticate
textboxes on frmAuthenticate - txtUserID and txtPword
command button on form - cmdAuthenticate


Private Sub cmdAuthenticate_Click()
On Error GoTo Err_cmdAuthenticate_Click


Dim oRS As ADODB.Recordset

strSQL = "SELECT * FROM [tblUsers] " & _
"WHERE USERID = '" & Me.txtUserID.Value & "' AND" & _
"PWORD = '" & Me.txtPword.value & "'"

Set oRS = New ADODB.Recordset
oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic

'Ensure there is a match...
If oRS.EOF And oRS.BOF Then 'if this is true, there is no match...
'Insert Code to tell the User they do not have permission...
'Like a message box with no choices...

'Clean up and close recordset...
oRS.Close
Set oRS = Nothing
Exit Sub
Else
'Insert Code to allow the user to delete...
'Like setting the delete property of the form to YES...

oRS.Close
Set oRS = Nothing

End If

Exit_cmdAuthenticate_Click:
Exit Sub

Err_cmdAuthenticate_Click:
MsgBox Err.Description
Resume Exit_cmdAuthenticate_Click


The BOF (Beginning of File) EOF (End of File) method checks to see if there
is any record. If BOF and EOF are both true then the recordset is empty,
therefore no match.

If I typed it correctly you should be able to copy the above code and insert
it on the Click event for the cmdAuthenticate button. You can add additonal
code to communicate with the user as you wish.

Let me know if this helps.


Randy


:

Randy
Thanks for the suggestion, but I have no experience in passing values from a
form so I would appreciate a bit more help.
I have created the tblUsers, but I am unure of the syntax for the where
statement and how that should be inserted into my original code.
Thanks
--
Dave Hawks


:

Create a table of users. (ie. tblUsers)
Each user has a "UserID" and a "Password"

All UserIDs must be unique.

Simpley create a WHERE statement that looks at UserID and Password

Example:

UserID: dhawks
Password: pwd

Then your "test" would be:

"Select * from tblUsers WHERE UserId = '" & Me.txtUser.Value & "'AND
Password = '" Me.txtPassword & "'"

(Note single and double quotes. If you do not have experience passing a
value from a form write back.)

If BOF and EOF then '(This tests if there are any records in the recordset)
'No Match - Add code to tell user they are not allowed like your "wrong
password"
Else
'There is a match -- add code to allow them to delete
End if

Let me know if this helps

Randy


:

I am using the following code to limit the ability of users to delete records.
Private Sub Player1_BeforeUpdate(Cancel As Integer)
Dim Response As String

Dim Password, Loggin
Password = "pwd"
If Not IsNull(Player1.OldValue) Then

Response = MsgBox("To avoid unauthorised deletions please enter your
password ", vbOKOnly)
If Response = vbOK Then

Loggin = InputBox("Enter Password")
If Loggin <> Password Then
MsgBox "Wrong Password"
Cancel = True
Player1.Undo

Else
End If
End If
End If
End Sub

I would like to allocate each user their own password, can anyone suugest a
method of comparing the entered password against a list of allowable
passwords.

 

[Randy Wayne]

The problem is in setting up the quotes. Note there are some single quote
double quote combinations. When you copy and paste it to VBA sometimes it
does not always interpret the order correctly.

Pay particular attention to this:

There should be as a double quote, single quote, and space before the AND as
well as a space between the AND and the double quote that comes after it.(my
mistake)
& "' AND " & _

Make sure that after PWORD = there is a space, single quote, and double quote.
"PWORD = '"

Make sure that you end with a double quote, single quote, double quote
"'"

This look right in your example but make sure that after USERID = there is a
space, a single quote, and a double quote.
USERID = '"
(It looks like three quote marks, but the single quote must come first.)


All this is done to tell VBA what is a text value (single quotes) and what
is verbage for the statement (double quotes). Believe me, we all struggle
with this when we start. You have the right idea now and once you get this
part right it should work.

I have written the line again as one line. This message board is what
causes it to wrap to a new line. You could copy the two lines back to back
and it should work as well. (Note there is a space at the end of the first
line after .Value) The problem with this method and the reason you see the &
_ at the end of the lines is to keep your code from running way off your
screen.

strSQL = "SELECT * FROM [tblUsers] WHERE USERID = '" & Me.txtUserID.Value &
"' AND PWORD = '" & Me.txtPword.value & "'"

Finally, if you are still having problems write back and we will figure it
out.
Randy

Randy
I have done as you suggested which has cleared the syntax error.
When I Click frmAuthenticate I get an error message
Syntax error (missing operator) in query expression"USERID = "hawks"
ANDPWORD =hawksd123 "".

The tblUsers has fields USERID and PWORD and hawks and hawkd123 were the
user id and password I typed into frmAuthenticate before clicking
cmdAuthenticate.
Thanks
--
Dave Hawks

Make sure the adLockOptimistic is on the same line as the rest of the line.
It looks like the screen on the Discussion Group message moved it to the next
line.

If you line ends with adOpenForwardOnly,

then that is "a" problem. Simply move the adLock Optimistic to the right of
the comma.

Let me know if that is the case.


Randy

Randy
I created the form from the UserId table and inserted your code on the click
event but got a syntax error message on the line

-- oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
Any Idea what may be wrong.
Thanks
Dave Hawks


:

Here is one method:

Assumptions -
Table named tblUsers
fields in tblUsers - USERID and PWORD

Form named frmAuthenticate
textboxes on frmAuthenticate - txtUserID and txtPword
command button on form - cmdAuthenticate


Private Sub cmdAuthenticate_Click()
On Error GoTo Err_cmdAuthenticate_Click


Dim oRS As ADODB.Recordset

strSQL = "SELECT * FROM [tblUsers] " & _
"WHERE USERID = '" & Me.txtUserID.Value & "' AND" & _
"PWORD = '" & Me.txtPword.value & "'"

Set oRS = New ADODB.Recordset
oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic

'Ensure there is a match...
If oRS.EOF And oRS.BOF Then 'if this is true, there is no match...
'Insert Code to tell the User they do not have permission...
'Like a message box with no choices...

'Clean up and close recordset...
oRS.Close
Set oRS = Nothing
Exit Sub
Else
'Insert Code to allow the user to delete...
'Like setting the delete property of the form to YES...

oRS.Close
Set oRS = Nothing

End If

Exit_cmdAuthenticate_Click:
Exit Sub

Err_cmdAuthenticate_Click:
MsgBox Err.Description
Resume Exit_cmdAuthenticate_Click


The BOF (Beginning of File) EOF (End of File) method checks to see if there
is any record. If BOF and EOF are both true then the recordset is empty,
therefore no match.

If I typed it correctly you should be able to copy the above code and insert
it on the Click event for the cmdAuthenticate button. You can add additonal
code to communicate with the user as you wish.

Let me know if this helps.


Randy


:

Randy
Thanks for the suggestion, but I have no experience in passing values from a
form so I would appreciate a bit more help.
I have created the tblUsers, but I am unure of the syntax for the where
statement and how that should be inserted into my original code.
Thanks
--
Dave Hawks


:

Create a table of users. (ie. tblUsers)
Each user has a "UserID" and a "Password"

All UserIDs must be unique.

Simpley create a WHERE statement that looks at UserID and Password

Example:

UserID: dhawks
Password: pwd

Then your "test" would be:

"Select * from tblUsers WHERE UserId = '" & Me.txtUser.Value & "'AND
Password = '" Me.txtPassword & "'"

(Note single and double quotes. If you do not have experience passing a
value from a form write back.)

If BOF and EOF then '(This tests if there are any records in the recordset)
'No Match - Add code to tell user they are not allowed like your "wrong
password"
Else
'There is a match -- add code to allow them to delete
End if

Let me know if this helps

Randy


:

I am using the following code to limit the ability of users to delete records.
Private Sub Player1_BeforeUpdate(Cancel As Integer)
Dim Response As String

Dim Password, Loggin
Password = "pwd"
If Not IsNull(Player1.OldValue) Then

Response = MsgBox("To avoid unauthorised deletions please enter your
password ", vbOKOnly)
If Response = vbOK Then

Loggin = InputBox("Enter Password")
If Loggin <> Password Then
MsgBox "Wrong Password"
Cancel = True
Player1.Undo

Else
End If
End If
End If
End Sub

I would like to allocate each user their own password, can anyone suugest a
method of comparing the entered password against a list of allowable
passwords.

 

[Dave Hawks]

Randy
I don't know if it was crucial but the second "Value" had a lower case v,
which together with the other changes seems to have sorted it out.
I will now try to get it to work with the rest of the application.
Thanks for your help it was much appreciated.

--
Dave Hawks

The problem is in setting up the quotes. Note there are some single quote
double quote combinations. When you copy and paste it to VBA sometimes it
does not always interpret the order correctly.

Pay particular attention to this:

There should be as a double quote, single quote, and space before the AND as
well as a space between the AND and the double quote that comes after it.(my
mistake)
& "' AND " & _

Make sure that after PWORD = there is a space, single quote, and double quote.
"PWORD = '"

Make sure that you end with a double quote, single quote, double quote
"'"

This look right in your example but make sure that after USERID = there is a
space, a single quote, and a double quote.
USERID = '"
(It looks like three quote marks, but the single quote must come first.)


All this is done to tell VBA what is a text value (single quotes) and what
is verbage for the statement (double quotes). Believe me, we all struggle
with this when we start. You have the right idea now and once you get this
part right it should work.

I have written the line again as one line. This message board is what
causes it to wrap to a new line. You could copy the two lines back to back
and it should work as well. (Note there is a space at the end of the first
line after .Value) The problem with this method and the reason you see the &
_ at the end of the lines is to keep your code from running way off your
screen.

strSQL = "SELECT * FROM [tblUsers] WHERE USERID = '" & Me.txtUserID.Value &
"' AND PWORD = '" & Me.txtPword.value & "'"

Finally, if you are still having problems write back and we will figure it
out.
Randy

Randy
I have done as you suggested which has cleared the syntax error.
When I Click frmAuthenticate I get an error message
Syntax error (missing operator) in query expression"USERID = "hawks"
ANDPWORD =hawksd123 "".

The tblUsers has fields USERID and PWORD and hawks and hawkd123 were the
user id and password I typed into frmAuthenticate before clicking
cmdAuthenticate.
Thanks
--
Dave Hawks

Make sure the adLockOptimistic is on the same line as the rest of the line.
It looks like the screen on the Discussion Group message moved it to the next
line.

If you line ends with adOpenForwardOnly,

then that is "a" problem. Simply move the adLock Optimistic to the right of
the comma.

Let me know if that is the case.


Randy


:

Randy
I created the form from the UserId table and inserted your code on the click
event but got a syntax error message on the line

-- oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
Any Idea what may be wrong.
Thanks
Dave Hawks


:

Here is one method:

Assumptions -
Table named tblUsers
fields in tblUsers - USERID and PWORD

Form named frmAuthenticate
textboxes on frmAuthenticate - txtUserID and txtPword
command button on form - cmdAuthenticate


Private Sub cmdAuthenticate_Click()
On Error GoTo Err_cmdAuthenticate_Click


Dim oRS As ADODB.Recordset

strSQL = "SELECT * FROM [tblUsers] " & _
"WHERE USERID = '" & Me.txtUserID.Value & "' AND" & _
"PWORD = '" & Me.txtPword.value & "'"

Set oRS = New ADODB.Recordset
oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic

'Ensure there is a match...
If oRS.EOF And oRS.BOF Then 'if this is true, there is no match...
'Insert Code to tell the User they do not have permission...
'Like a message box with no choices...

'Clean up and close recordset...
oRS.Close
Set oRS = Nothing
Exit Sub
Else
'Insert Code to allow the user to delete...
'Like setting the delete property of the form to YES...

oRS.Close
Set oRS = Nothing

End If

Exit_cmdAuthenticate_Click:
Exit Sub

Err_cmdAuthenticate_Click:
MsgBox Err.Description
Resume Exit_cmdAuthenticate_Click


The BOF (Beginning of File) EOF (End of File) method checks to see if there
is any record. If BOF and EOF are both true then the recordset is empty,
therefore no match.

If I typed it correctly you should be able to copy the above code and insert
it on the Click event for the cmdAuthenticate button. You can add additonal
code to communicate with the user as you wish.

Let me know if this helps.


Randy


:

Randy
Thanks for the suggestion, but I have no experience in passing values from a
form so I would appreciate a bit more help.
I have created the tblUsers, but I am unure of the syntax for the where
statement and how that should be inserted into my original code.
Thanks
--
Dave Hawks


:

Create a table of users. (ie. tblUsers)
Each user has a "UserID" and a "Password"

All UserIDs must be unique.

Simpley create a WHERE statement that looks at UserID and Password

Example:

UserID: dhawks
Password: pwd

Then your "test" would be:

"Select * from tblUsers WHERE UserId = '" & Me.txtUser.Value & "'AND
Password = '" Me.txtPassword & "'"

(Note single and double quotes. If you do not have experience passing a
value from a form write back.)

If BOF and EOF then '(This tests if there are any records in the recordset)
'No Match - Add code to tell user they are not allowed like your "wrong
password"
Else
'There is a match -- add code to allow them to delete
End if

Let me know if this helps

Randy


:

I am using the following code to limit the ability of users to delete records.
Private Sub Player1_BeforeUpdate(Cancel As Integer)
Dim Response As String

Dim Password, Loggin
Password = "pwd"
If Not IsNull(Player1.OldValue) Then

Response = MsgBox("To avoid unauthorised deletions please enter your
password ", vbOKOnly)
If Response = vbOK Then

Loggin = InputBox("Enter Password")
If Loggin <> Password Then
MsgBox "Wrong Password"
Cancel = True
Player1.Undo

Else
End If
End If
End If
End Sub

I would like to allocate each user their own password, can anyone suugest a
method of comparing the entered password against a list of allowable
passwords.

 

[Dave Hawks]

Randy
I have got the password code to work in terms of recognising a valid
password, but I am struggling to come up with code to delete the record in
the original form when the password is correct.
I tried
[Table1]![Player1].Undo

But I get an error "microsoft access can't find the field '!' referred to in
your expression"
Can you suggest how to identify a field correctly when it is on a different
form.
Thanks
--
Dave Hawks

Randy
I don't know if it was crucial but the second "Value" had a lower case v,
which together with the other changes seems to have sorted it out.
I will now try to get it to work with the rest of the application.
Thanks for your help it was much appreciated.

--
Dave Hawks

The problem is in setting up the quotes. Note there are some single quote
double quote combinations. When you copy and paste it to VBA sometimes it
does not always interpret the order correctly.

Pay particular attention to this:

There should be as a double quote, single quote, and space before the AND as
well as a space between the AND and the double quote that comes after it.(my
mistake)
& "' AND " & _

Make sure that after PWORD = there is a space, single quote, and double quote.
"PWORD = '"

Make sure that you end with a double quote, single quote, double quote
"'"

This look right in your example but make sure that after USERID = there is a
space, a single quote, and a double quote.
USERID = '"
(It looks like three quote marks, but the single quote must come first.)


All this is done to tell VBA what is a text value (single quotes) and what
is verbage for the statement (double quotes). Believe me, we all struggle
with this when we start. You have the right idea now and once you get this
part right it should work.

I have written the line again as one line. This message board is what
causes it to wrap to a new line. You could copy the two lines back to back
and it should work as well. (Note there is a space at the end of the first
line after .Value) The problem with this method and the reason you see the &
_ at the end of the lines is to keep your code from running way off your
screen.

strSQL = "SELECT * FROM [tblUsers] WHERE USERID = '" & Me.txtUserID.Value &
"' AND PWORD = '" & Me.txtPword.value & "'"

Finally, if you are still having problems write back and we will figure it
out.
Randy

Randy
I have done as you suggested which has cleared the syntax error.
When I Click frmAuthenticate I get an error message
Syntax error (missing operator) in query expression"USERID = "hawks"
ANDPWORD =hawksd123 "".

The tblUsers has fields USERID and PWORD and hawks and hawkd123 were the
user id and password I typed into frmAuthenticate before clicking
cmdAuthenticate.
Thanks
--
Dave Hawks


:

Make sure the adLockOptimistic is on the same line as the rest of the line.
It looks like the screen on the Discussion Group message moved it to the next
line.

If you line ends with adOpenForwardOnly,

then that is "a" problem. Simply move the adLock Optimistic to the right of
the comma.

Let me know if that is the case.


Randy


:

Randy
I created the form from the UserId table and inserted your code on the click
event but got a syntax error message on the line

-- oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
Any Idea what may be wrong.
Thanks
Dave Hawks


:

Here is one method:

Assumptions -
Table named tblUsers
fields in tblUsers - USERID and PWORD

Form named frmAuthenticate
textboxes on frmAuthenticate - txtUserID and txtPword
command button on form - cmdAuthenticate


Private Sub cmdAuthenticate_Click()
On Error GoTo Err_cmdAuthenticate_Click


Dim oRS As ADODB.Recordset

strSQL = "SELECT * FROM [tblUsers] " & _
"WHERE USERID = '" & Me.txtUserID.Value & "' AND" & _
"PWORD = '" & Me.txtPword.value & "'"

Set oRS = New ADODB.Recordset
oRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic

'Ensure there is a match...
If oRS.EOF And oRS.BOF Then 'if this is true, there is no match...
'Insert Code to tell the User they do not have permission...
'Like a message box with no choices...

'Clean up and close recordset...
oRS.Close
Set oRS = Nothing
Exit Sub
Else
'Insert Code to allow the user to delete...
'Like setting the delete property of the form to YES...

oRS.Close
Set oRS = Nothing

End If

Exit_cmdAuthenticate_Click:
Exit Sub

Err_cmdAuthenticate_Click:
MsgBox Err.Description
Resume Exit_cmdAuthenticate_Click


The BOF (Beginning of File) EOF (End of File) method checks to see if there
is any record. If BOF and EOF are both true then the recordset is empty,
therefore no match.

If I typed it correctly you should be able to copy the above code and insert
it on the Click event for the cmdAuthenticate button. You can add additonal
code to communicate with the user as you wish.

Let me know if this helps.


Randy


:

Randy
Thanks for the suggestion, but I have no experience in passing values from a
form so I would appreciate a bit more help.
I have created the tblUsers, but I am unure of the syntax for the where
statement and how that should be inserted into my original code.
Thanks
--
Dave Hawks


:

Create a table of users. (ie. tblUsers)
Each user has a "UserID" and a "Password"

All UserIDs must be unique.

Simpley create a WHERE statement that looks at UserID and Password

Example:

UserID: dhawks
Password: pwd

Then your "test" would be:

"Select * from tblUsers WHERE UserId = '" & Me.txtUser.Value & "'AND
Password = '" Me.txtPassword & "'"

(Note single and double quotes. If you do not have experience passing a
value from a form write back.)

If BOF and EOF then '(This tests if there are any records in the recordset)
'No Match - Add code to tell user they are not allowed like your "wrong
password"
Else
'There is a match -- add code to allow them to delete
End if

Let me know if this helps

Randy


:

I am using the following code to limit the ability of users to delete records.
Private Sub Player1_BeforeUpdate(Cancel As Integer)
Dim Response As String

Dim Password, Loggin
Password = "pwd"
If Not IsNull(Player1.OldValue) Then

Response = MsgBox("To avoid unauthorised deletions please enter your
password ", vbOKOnly)
If Response = vbOK Then

Loggin = InputBox("Enter Password")
If Loggin <> Password Then
MsgBox "Wrong Password"
Cancel = True
Player1.Undo

Else
End If
End If
End If
End Sub

I would like to allocate each user their own password, can anyone suugest a
method of comparing the entered password against a list of allowable
passwords.