End If without Block If Error Message (1 Viewer)

[Pauline123]

Hi all hope you can help - I have the following vba code but I keep getting the End If without Block If error message - cannot figure out what I have done wrong - can anyone help please

Private Sub txtPassword_AfterUpdate()

'Check that User is selected
If IsNull(Me.cboUser) Then
MsgBox "You need to select a user!", vbCritical
Me.cboUser.SetFocus
Else
'Check for correct password
If Me.txtPassword = Me.cboUser.Column(2) Then
'Check if password needs to be reset
If Me.cboUser.Column(4) Then
Select Case Nz(DLookup("Access", "tblUser", "AccessLevelID=" & Me.cboUser), "N/A")
Case "4"
DoCmd.OpenForm "UserSwitchboard"
Case "3"
DoCmd.OpenForm "AdminSwitchboard"
Case "2"
DoCmd.OpenForm "StoreManagerSwitchboard"
Case "1"
DoCmd.OpenForm "Main Switchboard"
End If
DoCmd.Close acForm, "frmLogin"

Else
MsgBox "Password does not match, please re-enter!", vboOkOnly
Me.txtPassword = Null
Me.txtPassword.SetFocus
End If
End If
End Sub

 

[JHB]

You are missing the End Select for the Select Case statement.
A good advice is to use Indent style in your code!
And please use code tags when you're posting code.

Private Sub txtPassword_AfterUpdate()
  'Check that User is selected
  If IsNull(Me.cboUser) Then
    MsgBox "You need to select a user!", vbCritical
    Me.cboUser.SetFocus
  Else
    'Check for correct password
    If Me.txtPassword = Me.cboUser.Column(2) Then
      'Check if password needs to be reset
      If Me.cboUser.Column(4) Then
        Select Case Nz(DLookup("Access", "tblUser", "AccessLevelID=" & Me.cboUser), "N/A")
          Case "4"
            DoCmd.OpenForm "UserSwitchboard"
          Case "3"
            DoCmd.OpenForm "AdminSwitchboard"
          Case "2"
            DoCmd.OpenForm "StoreManagerSwitchboard"
          Case "1"
            DoCmd.OpenForm "Main Switchboard"
        [B][COLOR=Red]End Select[/COLOR][/B]
      End If
      DoCmd.Close acForm, "frmLogin"
    Else
      MsgBox "Password does not match, please re-enter!", vboOkOnly
      Me.txtPassword = Null
      Me.txtPassword.SetFocus
    End If
  End If
End Sub

 

[Pauline123]

Hi JHB thank you for your reply and your good advice - will make sure I will Indent and use code tags and the first problem is sorted but I am now getting the following debug message: Run time error '2001' you have cancelled the previous operation.

  'Check that User is selected
  If IsNull(Me.cboUser) Then
    MsgBox "You need to select a user!", vbCritical
    Me.cboUser.SetFocus
  Else
    'Check for correct password
    If Me.txtPassword = Me.cboUser.Column(2) Then
      'Open switchboard for current user based on AccessLevelID
      If Me.cboUser.Column(4) Then
        Select Case Nz(DLookup("Access", "tblUser", "AccessLevelID=" & Me.cboUser), "N/A")
          Case "4"
            DoCmd.OpenForm "UserSwitchboard"
          Case "3"
            DoCmd.OpenForm "AdminSwitchboard"
          Case "2"
            DoCmd.OpenForm "StoreManagerSwitchboard"
          Case "1"
            DoCmd.OpenForm "Main Switchboard"
        End Select
      End If
      DoCmd.Close acForm, "frmLogin"
      'Incorrect Password message
    Else
      MsgBox "Password does not match, please re-enter!", vboOkOnly
      Me.txtPassword = Null
      Me.txtPassword.SetFocus
    End If
  End If

 

[Pauline123]

PS - I did paste the coding indented but this website has justified the lines :-(

 

[Frothingslosh]

PS - I did paste the coding indented but this website has justified the lines :-(

It won't do that if you use the code tags as requested.

 

[Pauline123]

Hi sorry but I did not know what a code tag was - just googled it so now understand.

Just wondering if you have any insight on my runtime issue

          Select Case Nz(DLookup("Access", "tblUser", "AccessLevelID=" & Me.cboUser), "N/A")

 

[JHB]

.. and the first problem is sorted but I am now getting the following debug message: Run time error '2001' you have cancelled the previous operation.
...

In which line do you get the error?

 

[Pauline123]

Hi its on this line

 Select Case Nz(DLookup("Access", "tblUser", "AccessLevelID=" & Me.cboUser), "N/A")

 

[JHB]

And the code is still in the AfterUpdate event?
Is Me.cboUser a number value?

 

[Pauline123]

Hi the code is still in the AfterUpdate Event in the txtPassword box.
The Me.cboUser refers to the combo box which has the following row source,
Column count 0;1;0;0 with the UserID being numerical:

 SELECT tblUser.UserID, [Lname] & ", " & [FName] AS Fullname, tblUser.Password, tblUser.AccessLevelID FROM tblUser ORDER BY tblUser.LName, tblUser.FName;

 

[JHB]

There is one error here, because there is no Column 4, then column order start at 0.

If Me.cboUser.Column(4) Then

Are you able to post (a stripped version) of your database with some sample data + info where the code is?

 

[Pauline123]

Hi have attached a stripped down version for you

 

[JHB]

First, you're using Column(4) in cboUser combobox which doesn't exist, because Column start index 0!

If Me.cboUser.Column(4) Then

Second, you do some lookup in table tblUser for a field called Access, which doesn't exist! I don't know what you want to check here?

Select Case Nz(DLookup("Access", "tblUser", "AccessLevelID=" & Me.cboUser), "N/A")

In a way you're doing some double checking I think, so I've comment out the DLookup part and do the select case on the cboUser combobox column(3) instead.

 

[Pauline123]

Hi thank you so much this now works a treat

 

[JHB]

You're welcome!
By the way did you read my signature?