If then else Statement with multiple conditions

[kirsco]

Hi

I'm hoping someone can help me with this. I'm doing some additions to a database at work that someone else made, and it's mostly going well.

What I'm stuck with is code to get one of three forms to open depending on the field values in two other fields.

Here's the code I have so far:

Private Sub cmdEdit_Click()
Dim stDocName As String
Dim strFilter As String
Dim stLinkCriteria As String


If Preventative_Reqd = False And Assigned_Evaluator Is Null Then
stDocName = "frm_Results"
strFilter = "Number= '" & Me.Number & "'"
stLinkCriteria = "frm_CIF_To_Be_ActionedEvaluation"
DoCmd.OpenForm stDocName, acNormal, , strFilter, , , stLinkCriteria

ElseIf Preventative_Reqd = True And Assigned_Evaluator Is Null Then
stDocName = "frm_ResultsPreventative"
strFilter = "Number= '" & Me.Number & "'"
stLinkCriteria = "frm_CIF_To_Be_ActionedEvaluation"
DoCmd.OpenForm stDocName, acNormal, , strFilter, , , stLinkCriteria

ElseIf Preventative_Reqd = False And Assigned_Evaluator Is Not Null Then
stDocName = "frm_ResultsEvaluator"
strFilter = "Number= '" & Me.Number & "'"
stLinkCriteria = "frm_CIF_To_Be_ActionedEvaluation"
DoCmd.OpenForm stDocName, acNormal, , strFilter, , , stLinkCriteria

ElseIf Preventative_Reqd = True And Assigned_Evaluator Is Not Null Then
stDocName = "frm_ResultsEvaluator"
strFilter = "Number= '" & Me.Number & "'"
stLinkCriteria = "frm_CIF_To_Be_ActionedEvaluation"
DoCmd.OpenForm stDocName, acNormal, , strFilter, , , stLinkCriteria

End If

End Sub

I keep getting a 424 Run_time error code.

My guess is that having two conditions in the If and ElseIf statements is what's making it crash.

I'm still fairly new at VBA, self-taught so have probably got it completely wrong. Any help would be greatly appreciated.

 

[vbaInet]

You seem to be checking for a combination of four possible values - Is Null; Not Is Null; True; False.

Put the Is Null check in one block and check for True False in an inner IF ELSE block...
Else (i.e. it's not null)
...Check again for True False in an inner IF ELSE block

Or do it the other way round:

Put the True check in one block and check for Is Null / Not Is Null in an inner IF ELSE block...
Else (i.e. it's false)
...Check again for Is Null / Not Is Null

 

[kirsco]

Hi vbaInet

I'm not sure how to do an inner Block.

 

[vbaInet]

Here's a skeleton:

If IsNull(this) then
     If that = True Then
     .....do something
     Else 
     .... do something else
     End If
Else
     If that = True Then
     ....do something
     Else
     ....do something else
     End If
End If

That would prevent you from writing too many combinations.

 

[kirsco]

Thanks vbaInet.

After a bit of mucking around, I did get it to go with the help of the skeleton you posted.

I ended up with:

Private Sub cmdEdit_Click()
Dim stDocName As String
Dim strFilter As String
Dim stLinkCriteria As String

'Determines which results form opens.

If IsNull(Assigned_Evaluator) Then
If Preventative_Reqd = True Then
stDocName = "frm_ResultsPreventative"
strFilter = "Number= '" & Me.Number & "'"
stLinkCriteria = "frm_CIF_To_Be_ActionedEvaluation"
DoCmd.OpenForm stDocName, acNormal, , strFilter, , , stLinkCriteria
ElseIf Preventative_Reqd = False Then
stDocName = "frm_Results"
strFilter = "Number= '" & Me.Number & "'"
stLinkCriteria = "frm_CIF_To_Be_ActionedEvaluation"
DoCmd.OpenForm stDocName, acNormal, , strFilter, , , stLinkCriteria
End If
End If

If Not IsNull(Assigned_Evaluator) Then
stDocName = "frm_ResultsEvaluate"
strFilter = "Number= '" & Me.Number & "'"
stLinkCriteria = "frm_CIF_To_Be_ActionedEvaluation"
DoCmd.OpenForm stDocName, acNormal, , strFilter, , , stLinkCriteria
End If
End Sub

 

[vbaInet]

Great job!!

There was just a little thing you missed, remember the outter ELSE line instead of writing these two lines

End If

If Not IsNull(Assigned_Evaluator) Then

 

[cb@chrisb.eu]

Private Sub Report_Open(Cancel As Integer)
If Forms!LogIn!txtSecurityID = 13 Then
DoCmd.OpenReport "Psychology M Report", acViewPreview
Then MsgBox "Sorry, not authorised"
Else
DoCmd.Close
End If
End Sub
Hi. Access doesn't like if then else like this. Any ideas?

 

[jonathanchye]

Private Sub Report_Open(Cancel As Integer)
If Forms!LogIn!txtSecurityID = 13 Then
DoCmd.OpenReport "Psychology M Report", acViewPreview
Then MsgBox "Sorry, not authorised"
Else
DoCmd.Close
End If
End Sub
Hi. Access doesn't like if then else like this. Any ideas?

If you pay just a little attention you would've noticed the 2 "Thens"...

Try :

Private Sub Report_Open(Cancel As Integer)
If Forms!LogIn!txtSecurityID = 13 Then
DoCmd.OpenReport "Psychology M Report", acViewPreview
DoCmd.Close
Else
MsgBox "Sorry, not authorised"
End If
End Sub

 

[cb@chrisb.eu]

Hi. But if it's not 13, it opens on the left hand side.
So if 13, open. if not, close (and) say sorry. It's the (and)' I'm having problems with.

 

[jonathanchye]

Hi. But if it's not 13, it opens on the left hand side.
So if 13, open. if not, close (and) say sorry. It's the (and)' I'm having problems with.

Well I am assuming the report is already open in the first place so just modify the code to look like this :

Private Sub Report_Open(Cancel As Integer) 
If Forms!LogIn!txtSecurityID = 13 Then 
DoCmd.OpenReport "Psychology M Report", acViewPreview 
Else
DoCmd.Close , "Psychology M Report", acSavePrompt 
MsgBox "Sorry, not authorised" 
End If 
End Sub

 

[cb@chrisb.eu]

Hi. Log in code ADMIN./admin. When you go in through the main form, no Management Report. But if you F11 it, you can open it, despite your code and after an initial message box. Any ideas?