DLookup in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have used a button to switch to another form called "Mantain". I wanted to
password protect the form. I have a table called "Passwords" containing the
passwords for the users. This is my code for the button:-

Private Sub Maintenance_Click()
On Error GoTo Err_Maintenance_Click

Dim stDocName As String, inpass As String, stLinkCriteria As String
Dim passcheck As String

stDocName = "Maintain"

inpass = InputBox("Enter Password")

passcheck = DLookup("[Password]", "Passwords", "[PassLookup] = " & inpass)

If passcheck = Null Then
MsgBox ("Wrong Password")
GoTo Exit_Maintenance_Click
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_Maintenance_Click:
Exit Sub

Err_Maintenance_Click:
MsgBox Err.Description
Resume Exit_Maintenance_Click

End Sub

When I run the sub routine I get a message "You cancelled the previous
operation"

I have not the slightest idea what this means. Can anyone help me please?
 
Mybe the problem is not with this code, but with the form you open.
Do you cancel the load of the other form in certain condition?

Put code break (press F9) in the first line, and when the code stop, step
through the code (prees F8) and see which line cause the problem
 
That very misleading error message often appears when you've made a mistake
in coding your DLookup statement, such as mistyping the table or field name.

Other than that possibility, there are definitely some other problems with
your code.

You've declared passcheck to be a string, yet you're potentially assigning a
Null value to it. Strings can't contain Nulls: the only data type that can
is the Variant.

You cannot use the equality operator to check for Null: by its nature, Null
= Null is Null, which always evaluates to False. You need to use the IsNull
function instead:

If IsNull(passcheck) Then

Is PassLookup a text field? If so, you need to put quotes around its value
to use it in a DLookup statement:

passcheck = DLookup("[Password]", "Passwords", "[PassLookup] = " & Chr$(34)
& inpass & Chr$(34))

or

passcheck = DLookup("[Password]", "Passwords", "[PassLookup] = '" & inpass &
"'")

Exagerated for clarity, that second option is

passcheck = DLookup("[Password]", "Passwords", "[PassLookup] = ' " & inpass
& " ' ")

Since you're not assigning a value to stLinkCriteria, you may as well omit
it from the routine.

That means that, assuming that PassLookup is a text field, your code could
be rewritten as:

Private Sub Maintenance_Click()
On Error GoTo Err_Maintenance_Click

Dim stDocName As String, inpass As String

stDocName = "Maintain"

inpass = InputBox("Enter Password")

If IsNull(DLookup("[Password]", "Passwords", "[PassLookup] = '" & inpass
& "'"))
MsgBox ("Wrong Password")
Else
DoCmd.OpenForm stDocName
End If

Exit_Maintenance_Click:
Exit Sub

Err_Maintenance_Click:
MsgBox Err.Description
Resume Exit_Maintenance_Click

End Sub

There was no need for the GoTo Exit_Maintenance_Click line in the True part
of your If-Else-End If construct, since there's no other code that will
execute.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Julian said:
I have used a button to switch to another form called "Mantain". I wanted to
password protect the form. I have a table called "Passwords" containing the
passwords for the users. This is my code for the button:-

Private Sub Maintenance_Click()
On Error GoTo Err_Maintenance_Click

Dim stDocName As String, inpass As String, stLinkCriteria As String
Dim passcheck As String

stDocName = "Maintain"

inpass = InputBox("Enter Password")

passcheck = DLookup("[Password]", "Passwords", "[PassLookup] = " & inpass)

If passcheck = Null Then
MsgBox ("Wrong Password")
GoTo Exit_Maintenance_Click
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_Maintenance_Click:
Exit Sub

Err_Maintenance_Click:
MsgBox Err.Description
Resume Exit_Maintenance_Click

End Sub

When I run the sub routine I get a message "You cancelled the previous
operation"

I have not the slightest idea what this means. Can anyone help me please?
Click to expand...
 
Many thanks. I haven't made the changes yet but I will do soonest.
--
Cheers
julian


Douglas J Steele said:
That very misleading error message often appears when you've made a mistake
in coding your DLookup statement, such as mistyping the table or field name.

Other than that possibility, there are definitely some other problems with
your code.

You've declared passcheck to be a string, yet you're potentially assigning a
Null value to it. Strings can't contain Nulls: the only data type that can
is the Variant.

You cannot use the equality operator to check for Null: by its nature, Null
= Null is Null, which always evaluates to False. You need to use the IsNull
function instead:

If IsNull(passcheck) Then

Is PassLookup a text field? If so, you need to put quotes around its value
to use it in a DLookup statement:

passcheck = DLookup("[Password]", "Passwords", "[PassLookup] = " & Chr$(34)
& inpass & Chr$(34))

or

passcheck = DLookup("[Password]", "Passwords", "[PassLookup] = '" & inpass &
"'")

Exagerated for clarity, that second option is

passcheck = DLookup("[Password]", "Passwords", "[PassLookup] = ' " & inpass
& " ' ")

Since you're not assigning a value to stLinkCriteria, you may as well omit
it from the routine.

That means that, assuming that PassLookup is a text field, your code could
be rewritten as:

Private Sub Maintenance_Click()
On Error GoTo Err_Maintenance_Click

Dim stDocName As String, inpass As String

stDocName = "Maintain"

inpass = InputBox("Enter Password")

If IsNull(DLookup("[Password]", "Passwords", "[PassLookup] = '" & inpass
& "'"))
MsgBox ("Wrong Password")
Else
DoCmd.OpenForm stDocName
End If

Exit_Maintenance_Click:
Exit Sub

Err_Maintenance_Click:
MsgBox Err.Description
Resume Exit_Maintenance_Click

End Sub

There was no need for the GoTo Exit_Maintenance_Click line in the True part
of your If-Else-End If construct, since there's no other code that will
execute.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Julian said:
I have used a button to switch to another form called "Mantain". I wanted to
password protect the form. I have a table called "Passwords" containing the
passwords for the users. This is my code for the button:-

Private Sub Maintenance_Click()
On Error GoTo Err_Maintenance_Click

Dim stDocName As String, inpass As String, stLinkCriteria As String
Dim passcheck As String

stDocName = "Maintain"

inpass = InputBox("Enter Password")

passcheck = DLookup("[Password]", "Passwords", "[PassLookup] = " & inpass)

If passcheck = Null Then
MsgBox ("Wrong Password")
GoTo Exit_Maintenance_Click
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_Maintenance_Click:
Exit Sub

Err_Maintenance_Click:
MsgBox Err.Description
Resume Exit_Maintenance_Click

End Sub

When I run the sub routine I get a message "You cancelled the previous
operation"

I have not the slightest idea what this means. Can anyone help me please?
Click to expand...
Click to expand...
 
Thanks for this. I will trry it.
--
Cheers
julian


Ofer said:
Mybe the problem is not with this code, but with the form you open.
Do you cancel the load of the other form in certain condition?

Put code break (press F9) in the first line, and when the code stop, step
through the code (prees F8) and see which line cause the problem

--
\\// Live Long and Prosper \\//
BS"D


Julian said:
I have used a button to switch to another form called "Mantain". I wanted to
password protect the form. I have a table called "Passwords" containing the
passwords for the users. This is my code for the button:-

Private Sub Maintenance_Click()
On Error GoTo Err_Maintenance_Click

Dim stDocName As String, inpass As String, stLinkCriteria As String
Dim passcheck As String

stDocName = "Maintain"

inpass = InputBox("Enter Password")

passcheck = DLookup("[Password]", "Passwords", "[PassLookup] = " & inpass)

If passcheck = Null Then
MsgBox ("Wrong Password")
GoTo Exit_Maintenance_Click
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_Maintenance_Click:
Exit Sub

Err_Maintenance_Click:
MsgBox Err.Description
Resume Exit_Maintenance_Click

End Sub

When I run the sub routine I get a message "You cancelled the previous
operation"

I have not the slightest idea what this means. Can anyone help me please?
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

Criteria when opening form 1
If Statements 2
I am not "getting" it.....DoCmd. OpenForm 4
Password Protect Form 8
Syntax question about stLinkCriteria 8
Using a wildcard to open a form 2
Missing operator Error 4
Command Button not working on form - HELP!!! 3

Back
Top