using multiple if statements to check for multiple Variables only working for the first statement.

[ShaunG]

the following code works as its suppose to

Private Sub cmdAddStage_Click()
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 1 Then
DoCmd.OpenForm "Stage_BuildingName"


End If
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 2 Then
DoCmd.OpenForm "Stage_BuildingName"
End If
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 3 Then
DoCmd.OpenForm "Stage_BuildingName"
End If
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 4 Then
MsgBox "You Do Not have Access", vbOKOnly
End If
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 5 Then
MsgBox "You Do Not have Access", vbOKOnly
End If
End Sub

this searchs the user name and security level perfectly, but when i throw an if isnull statement in there with them it only works for the first user if that makes sense.

Private Sub cmdAddAddress_Click()
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 1 Then
If IsNull(Me.cboStage) Then
MsgBox "Add Stage Number / Building Name First", vbOKOnly
Else
DoCmd.OpenForm "AddressName"
End If
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 2 Then
If IsNull(Me.cboStage) Then
MsgBox "Add Stage Number / Building Name First", vbOKOnly
Else
DoCmd.OpenForm "AddressName"
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 3 Then
If IsNull(Me.cboStage) Then
MsgBox "Add Stage Number / Building Name First", vbOKOnly
Else
DoCmd.OpenForm "AddressName"
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 4 Then
MsgBox "You Do Not have Access", vbOKOnly
End If
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 5 Then
MsgBox "You Do Not have Access", vbOKOnly
End If
End If
End If
End If
End If
End If
End Sub

i cant work out why it works for one code and not for the other, can any one shed some light would be much appreciated, cheers.

[Minty]

You've nested your If's all wrong;

Private Sub cmdAddAddress_Click()
    If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 1 Then
        If IsNull(Me.cboStage) Then
            MsgBox "Add Stage Number / Building Name First", vbOKOnly
        Else
            DoCmd.OpenForm "AddressName"
        End If
        If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 2 Then
            If IsNull(Me.cboStage) Then
                MsgBox "Add Stage Number / Building Name First", vbOKOnly
            Else
                DoCmd.OpenForm "AddressName"
                If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 3 Then
                    If IsNull(Me.cboStage) Then
                        MsgBox "Add Stage Number / Building Name First", vbOKOnly
                    Else
                        DoCmd.OpenForm "AddressName"
                        If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 4 Then
                            MsgBox "You Do Not have Access", vbOKOnly
                        End If
                        If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 5 Then
                            MsgBox "You Do Not have Access", vbOKOnly
                        End If
                    End If
                End If
            End If
        End If
    End If
End Sub

Can you see where that goes wrong ?

[Minty]

Your initial code can be replaced with

    Dim iAccLvl As Integer
    
    iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)

    Select Case iAccLvl
    
        Case 1, 2, 3
            DoCmd.OpenForm "Stage_BuildingName"
        Case Else
            MsgBox "You Do Not have Access", vbOKOnly
    End Select

Which should steer you to getting a simpler solution to the second problem.

[ShaunG]

You've nested your If's all wrong;

Private Sub cmdAddAddress_Click()
    If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 1 Then
        If IsNull(Me.cboStage) Then
            MsgBox "Add Stage Number / Building Name First", vbOKOnly
        Else
            DoCmd.OpenForm "AddressName"
        End If
        If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 2 Then
            If IsNull(Me.cboStage) Then
                MsgBox "Add Stage Number / Building Name First", vbOKOnly
            Else
                DoCmd.OpenForm "AddressName"
                If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 3 Then
                    If IsNull(Me.cboStage) Then
                        MsgBox "Add Stage Number / Building Name First", vbOKOnly
                    Else
                        DoCmd.OpenForm "AddressName"
                        If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 4 Then
                            MsgBox "You Do Not have Access", vbOKOnly
                        End If
                        If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 5 Then
                            MsgBox "You Do Not have Access", vbOKOnly
                        End If
                    End If
                End If
            End If
        End If
    End If
End Sub

Can you see where that goes wrong ?

no i cant Minty do you mind explaining where ive gone wrong

[Minty]

Okay - well using the simpler method we can add the following;

Private Sub cmdAddStage_Click()
    
    Dim iAccLvl As Integer
    
    iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)


    Select Case iAccLvl
    
        Case 1, 2, 3
            If IsNull(Me.cboStage) Then
                MsgBox "Add Stage Number / Building Name First", vbOKOnly
                Exit Sub
            End If
        Case Else
            MsgBox "You Do Not have Access", vbOKOnly
            Exit Sub
    End Select
    
    DoCmd.OpenForm "AddressName"
    
End Sub

[ShaunG]

Okay - well using the simpler method we can add the following;

   Dim iAccLvl As Integer
    
    iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)

    Select Case iAccLvl
    
        Case 1, 2, 3
              If IsNull(Me.cboStage) Then  
                         MsgBox "Add Stage Number / Building Name First", vbOKOnly        
                         Exit Sub
                  End If
        Case Else
            MsgBox "You Do Not have Access", vbOKOnly     
            Exit Sub
    
     End Select
    
     DoCmd.OpenForm "AddressName"
    

it would be god to get an under standing of what you've done, but i dont quite get it. i though exit sub was always at the end of the code as to cancel out of it.

i just did a quick goggle when you suggested ive nested it wrong and came up with the below code that does work, im definitely keen like i said to understand yours because now i have a lot of other parts to rectify.

Private Sub cmdAddAddress_Click()
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 1 And IsNull(Me.cboStage) Then
MsgBox "Add Stage Number / Building Name First", vbOKOnly
Else
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 1 And Not IsNull(Me.cboStage) Then
DoCmd.OpenForm "AddressName"
End If
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 2 And IsNull(Me.cboStage) Then
MsgBox "Add Stage Number / Building Name First", vbOKOnly
Else
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 2 And Not IsNull(Me.cboStage) Then
DoCmd.OpenForm "AddressName"
End If
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 3 And IsNull(Me.cboStage) Then
MsgBox "Add Stage Number / Building Name First", vbOKOnly
Else
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 3 And Not IsNull(Me.cboStage) Then
DoCmd.OpenForm "AddressName"
End If
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 4 Then
MsgBox "You Do Not have Access", vbOKOnly
End If
If DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser) = 5 Then
MsgBox "You Do Not have Access", vbOKOnly
End If
End If
End If
End If
End Sub

[ShaunG]

Minty you're code works great!!

is select case part of access?

[Minty]

Here is a breakdown of the code I provided, you can read more about Select Case here https://www.techonthenet.com/access/...anced/case.php

Private Sub cmdAddStage_Click()
    
    Dim iAccLvl As Integer
            ' Rather than repeating the same test 3 times we store the access level in a variable called iAccLvl
    iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)

            ' The Select Case contruct allow you to perform a much neater way of making comparions than nested
            ' If Then Else ElseIf type statements.
            
    Select Case iAccLvl     ' Here we tell access to look at the value of iAcctLvl
    
        Case 1, 2, 3        ' If it's 1, 2 or 3 we then do the code immediately afterwards
            If IsNull(Me.cboStage) Then    ' Now we're doing the second check
                MsgBox "Add Stage Number / Building Name First", vbOKOnly
                Exit Sub    ' We use the Exit Sub to finish processing as we're done here
            End If
            
        Case Else           ' In this example all other conditions mean one action
            MsgBox "You Do Not have Access", vbOKOnly
            Exit Sub        ' as above We use the Exit Sub to finish processing as we're done here
    
    End Select      ' This indicates we've finished the Case statement

                         ' If we have managed to get here all conditions are met so we can open the form.
    DoCmd.OpenForm "AddressName"
    
End Sub

[ShaunG]

Here is a breakdown of the code I provided, you can read more about Select Case here

Private Sub cmdAddStage_Click()
    
    Dim iAccLvl As Integer
            ' Rather than repeating the same test 3 times we store the access level in a variable called iAccLvl
    iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)

            ' The Select Case contruct allow you to perform a much neater way of making comparions than nested
            ' If Then Else ElseIf type statements.
            
    Select Case iAccLvl     ' Here we tell access to look at the value of iAcctLvl
    
        Case 1, 2, 3        ' If it's 1, 2 or 3 we then do the code immediately afterwards
            If IsNull(Me.cboStage) Then    ' Now we're doing the second check
                MsgBox "Add Stage Number / Building Name First", vbOKOnly
                Exit Sub    ' We use the Exit Sub to finish processing as we're done here
            End If
            
        Case Else           ' In this example all other conditions mean one action
            MsgBox "You Do Not have Access", vbOKOnly
            Exit Sub        ' as above We use the Exit Sub to finish processing as we're done here
    
    End Select      ' This indicates we've finished the Case statement

' If we have managed to get here all conditions are met so we can open the form.
    DoCmd.OpenForm "AddressName"
    
End Sub

Minty great work and thanks for the break down, i need it when it comes to coding very much appreciated.

[Minty]

No problem, I've added the link I forgot to post for further details.

[ShaunG]

No problem, I've added the link I forgot to post for further details.

thanks im trying to disect my delete code into your simpler method, i came up with this but saying case else out side select case....

Private Sub cmdDeleteJoinery_Click()
Dim iAccLvl As Integer
Dim Response As Integer

iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)


Select Case iAccLvl

Case 1, 2, 3
Response = MsgBox(prompt:="Are you sure you want to Delete?.", Buttons:=vbYesNo)
If Response = vbNo Then
' do nothing
Else
If Response = vbYes And IsNull(Me.cboJoinery) Then
MsgBox "Please select Joinery Unit"
Exit Sub
End If
Case Else
MsgBox "You Do Not have Access", vbOKOnly
Exit Sub

End Select
Dim delJoinery As Integer
delJoinery = "delete * from tblJoineryUnit where (JoineryID_PK = " & Me.cboJoinery & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL delJoinery
DoCmd.SetWarnings True
Me.cboJoinery.Requery
Me.cboJoinery = Me.cboJoinery.ItemData(0)

End Sub

any ideas

[ShaunG]

i managed to get this to work but its deleting the joinery unit even if the No button is pressed to cancel deleting it.

Private Sub cmdDeleteJoinery_Click()
Dim iAccLvl As Integer
Dim Response As Integer

iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)


Select Case iAccLvl

Case 1, 2, 3
Response = MsgBox(prompt:="Are you sure you want to Delete?.", Buttons:=vbYesNo)
If Response = vbNo Then
' do nothing
ElseIf Response = vbYes And IsNull(Me.cboJoinery) Then
MsgBox "Please select Joinery Unit", vbOKOnly
Exit Sub
End If
Case Else
MsgBox "You Do Not have Access", vbOKOnly
Exit Sub

End Select
Dim delJoinery As String
delJoinery = "delete * from tblJoineryUnit where (JoineryID_PK = " & Me.cboJoinery & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL delJoinery
DoCmd.SetWarnings True
Me.cboJoinery.Requery
Me.cboJoinery = Me.cboJoinery.ItemData(0)

End Sub

[Minty]

It will really help you to see how the code is formed if you indent things to see the program / logic flow.

Private Sub cmdDeleteJoinery_Click()
    
    Dim iAccLvl As Integer
    Dim Response As Integer


    iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)


    Select Case iAccLvl


        Case 1, 2, 3
            Response = MsgBox(prompt:="Are you sure you want to Delete?.", Buttons:=vbYesNo)
            
            If Response = vbNo Then Exit Sub
                        ' You don't need to check for vbYes, if it wasn't vbNo it must have been Yes!
            If IsNull(Me.cboJoinery) Then
                MsgBox "Please select Joinery Unit"
                Exit Sub
            End If


        Case Else
            MsgBox "You Do Not have Access", vbOKOnly
            Exit Sub


    End Select


    Dim delJoinery As String    'You had this defined as an Integer ? ?
    
    delJoinery = "delete * from tblJoineryUnit where (JoineryID_PK = " & Me.cboJoinery & ")"
    
    CurrentDb.Execute delJoinery     ' This removes the need for setting warning off and on...
      
    Me.cboJoinery.Requery
    Me.cboJoinery = Me.cboJoinery.ItemData(0)


End Sub

[ShaunG]

It will really help you to see how the code is formed if you indent things to see the program / logic flow.

Private Sub cmdDeleteJoinery_Click()
    
    Dim iAccLvl As Integer
    Dim Response As Integer


    iAccLvl = DLookup("[AccessLevel]", "tblEmployees", "[EmployeeID_PK] = " & Forms!LoginForm!cboUser)


    Select Case iAccLvl


        Case 1, 2, 3
            Response = MsgBox(prompt:="Are you sure you want to Delete?.", Buttons:=vbYesNo)
            
            If Response = vbNo Then Exit Sub
                        ' You don't need to check for vbYes, if it wasn't vbNo it must have been Yes!
            If IsNull(Me.cboJoinery) Then
                MsgBox "Please select Joinery Unit"
                Exit Sub
            End If


        Case Else
            MsgBox "You Do Not have Access", vbOKOnly
            Exit Sub


    End Select


    Dim delJoinery As String    'You had this defined as an Integer ? ?
    
    delJoinery = "delete * from tblJoineryUnit where (JoineryID_PK = " & Me.cboJoinery & ")"
    
    CurrentDb.Execute delJoinery     ' This removes the need for setting warning off and on...
      
    Me.cboJoinery.Requery
    Me.cboJoinery = Me.cboJoinery.ItemData(0)


End Sub

yeah I do indent the code just when copy and pasting it seems come out that way, i noticed i had the deljoinery as interger.... still getting used to it but with the 2 examples you've given me hopefully will get my head around how it works.

[Minty]

Two things, unless you need to include the previous post , don't use the reply with quote button, just use the quick reply, it makes the threads much shorter and easier to read.

For your code press the # button on the quick reply editor and paste your code between the two [ C O D E ] tags then it will maintain its spacing correctly.