Compile Error: Variable Not Defined

  • Thread starter Thread starter Tara
  • Start date Start date
T

Tara

All of our databases have Login forms. I've reused these forms and the
associated tables many times in the past with other databases (the original
Login form was part of a database that already existed before I started
working here). Now, for the first time, I'm having a problem with it.
Obviously, the code indicates that the variable isn't defined, so I defined
it. Unfortunately, it then just goes on to the next Case statement, and
breaks there. In looking at our other databases, I realized that the
variables aren't defined in those either, at least not that I can see, and
they work perfectly. I'm not proficient at all with coding, and I'm lost
trying to figure out what the underlying issue is. Here's the code
involved...the code originally broke at Case ltNone. Any help is greatly
appreciated!

Private Sub Form_Load()
Dim rst As New ADODB.Recordset
Dim strUser As String

On Error GoTo ErrHandler
rst.Open "SysMisc", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
bUsePasswords = rst.Fields("UsePasswords")

Select Case rst.Fields("Login")
Case ltNone
cmdEnter_Click

Case ltWindows
strUser = GetUser
rst.Close
rst.Open "SELECT * FROM sysEmployees WHERE
sysEmployees.WindowsLogin='" & strUser & "'", CurrentProject.Connection,
adOpenStatic, adLockReadOnly
If rst.RecordCount > 0 Then
Me.comEmployeeID = rst.Fields("EmployeeID")
If bUsePasswords = False Then
cmdEnter_Click
Else
Me.txtPassword.Visible = bUsePasswords
End If
Else
Err.Raise 550, "sysLogin.Form_Load", "Unauthorized access
attempted: " & strUser
rst.Close
Set rst = Nothing
DoCmd.Quit
End If
Case ltDatabase
Me.txtPassword.Visible = bUsePasswords
End Select
rst.Close

ExitHere:
Set rst = Nothing
Exit Sub
ErrHandler:
If Err.Number = 550 Then
MsgBox Err.Description
Application.Quit
Else
MsgBox Err.Description
End If
Resume ExitHere
End Sub
 
Under the VBA window check Tools then Editor tab and see if require variable
declaration is on.

Also, on the VBA input window General Declaration see if it requires Option
Explicit.
Option Compare Database
Option Explicit
 
FGM, I just checked, and require variable declaration is not on, and the
General Declarations do require Option Explicit.

I'd appreciate any other help you can give.
 
Take out Option Explicit and see if it runs without it. I use it but maybe
you have a variable that is not declared and it is not showing. Usually it
highlights the variable that is not defined.
 
Well, I'm getting further now. The Login form opens and I can choose a user
from the dropdown, but the field for password isn't there. If I go ahead a
click Login, I now get a new Compile error. It says Sub or Function not
defined and highlights the If CheckPassword(Nz(Me.txtPassword, "")) Then
loginSuccessful = True of the following code. The error makes sense of
course, since I can't enter a password.

Do you have any idea why this code all works in every other instance that
I've used it and not here??

Thanks!

Private Sub cmdEnter_Click()
Dim rst As New ADODB.Recordset

If Not getLoginType = ltNone Then
If Nz(Me.comEmployeeID, 0) = 0 Then
MsgBox "You must enter a UserName.", vbOKOnly, "No UserName
Entered"
GoTo ExitHere
End If
End If

loginSuccessful = Not bUsePasswords

'if we are using passwords
If iIncorrectTries < ALLOWABLE_TRIES And Not loginSuccessful Then
'find the login type
Select Case getLoginType
Case LoginType.ltNone
loginSuccessful = True

Case LoginType.ltDatabase
rst.Open "SELECT * FROM sysEmployees WHERE
sysEmployees.EmployeeID=" & _
Me.comEmployeeID, CurrentProject.Connection,
adOpenKeyset, _
adLockOptimistic
If rst.Fields("Password") = Me.txtPassword Then
loginSuccessful = True
rst.Close

Case LoginType.ltWindows
If CheckPassword(Nz(Me.txtPassword, "")) Then
loginSuccessful = True

Case Else
MsgBox "There is a problem with the login form. Please
contact an administrator.", , "Login Error"
End Select

If Not loginSuccessful Then
iIncorrectTries = iIncorrectTries + 1
MsgBox "The password you entered is incorrect. Try again.",
vbOKOnly, _
"Login Error"
Me.txtPassword = ""
Me.txtPassword.SetFocus
End If
End If

If Not loginSuccessful And iIncorrectTries = ALLOWABLE_TRIES Then
DoCmd.Quit acQuitSaveNone

If loginSuccessful Then

DoCmd.Close acForm, "SysLogin"
End If
ExitHere:
Set rst = Nothing
Exit Sub
ErrHandler:

End Sub
 
IMO, you should probably leave Option Explicit on, and try to compile all
modules from the VBA window. If it compiles, all variables are there. Even
if you don't see the variable declared inside that particular procedure, it
may be declared somewhere else outside of that procedure in a wider scope (in
the module header as public or private, or in any other module as a public
declaration).

I would advise stepping through the code in debug mode in an app where this
works, and jotting down all of the dependant procedures and variables... then
go back to your problem project and make sure everything is present.

Bottom line though, you definately need to Compile everything before running
(this is good practice even for debugging). And if there's variables not
being there that's causing an issue, it will tell you when you try to
compile. If Option Explicit was already there and you removed it, put it
back ASAP before you cause more problems than you already have. If it works
in other projects with Option Explicit, then you need to have Option
Explicit. To do otherwise can cause some pretty ugly problems aside from
what you already have.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
Thanks Jack. I had already put Option Explicit back since I was pretty sure
that wasn't the issue. Like you said...if it worked in every other instance,
it should work now. Just before I saw your response, I was looking through
the code and then it struck me that I needed to look at the modules. Sure
enough, when I had imported them, I had missed one. Once I went back and
imported it, the problem was solved. I feel like such a dunce...

Thanks for your response though, I appreciate it.
 
Back
Top