VBA Error: 'Compile Error: Block If Without End If' (1 Viewer)

T

Tor_Fey

Registered User.
Local time
Today, 18:01
Joined
Feb 8, 2013
Messages
121
Good Afternoon all;

I am getting an a Compile Error: 'Block If Without End If' in the below code :banghead::

Code: 
Private Sub Processbutton_Click()

If Me.RiskGrand = 0 Then
MsgBox "The Case has not been risk tested yet", vbExclamation, "RISK ASSESSMENT REQUIRED"
Exit Sub
End If

If Me.RiskGrand <= 154 Then
If MsgBox("You are about to finalise this record as a Low Risk. You will be unable to edit it" & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + vbYesNo, "SIGN OFF") = vbNo Then
Exit Sub
Else

Me.TF_Ben_Outcome = "Low Risk, NFA"
Me.TF_Ben_Outcome_Date = Date
Me.TF_Ben_Outcome_By = CurrentUser()
End If
End If

If Me.RiskGrand <= 154 Then
If MsgBox("You are about to finalise this record as a Low Risk. You will be unable to edit it" & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + vbYesNo, "SIGN OFF") = vbNo Then
Exit Sub
End If

If Me.RiskGrand >= 155 And Me.RiskGrand <= 189 Then
MsgBox "Medium Risk"
End If

If Me.TF_Ben_NI_Number > 9 Then
MsgBox "Please Change The NI Number To 9 Digits", vbInformation, "NI Number Can Only Be 9 Digits"
Me.TF_Ben_NI_Number.SetFocus
Exit Sub
 
If Me.RiskGrand > 189 Then
If MsgBox("You are about to finalise this record and create an Investigation Case." & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + vbYesNo, "SIGN OFF") = vbNo Then
Exit Sub
Else
Me.TF_Ben_Outcome = "High Risk, Case created"
Me.TF_Ben_Outcome_Date = Date
Me.TF_Ben_Outcome_By = CurrentUser()

DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry_TF_Ben_PrelimtoMain", acViewNormal, acEdit
DoCmd.SetWarnings True
DoCmd.Close acForm, "frm_tf_ben_IUO1"
End If

If MsgBox("DO YOU WANT TO OPEN MAIN RECORD? " & vbCrLf, vbYesNo) = vbYes Then
DoCmd.OpenForm "frm_tf_ben_main", acNormal, "", "[tbl_tf_ben_main]![tf_ben_id]=[form]![frm_tf_ben_IUO1]![tf_ben_id]", , acNormal
DoCmd.Close acForm, "frm_tf_ben_IUO1"
Else
DoCmd.Close acForm, "frm_tf_ben_IUO1"
End If
    
End Sub
I can't seem to find where I am missing the 'End if' from my code, was hoping a fresh pair of eyes would help.

Kind Regards
Tor Fey
 
Last edited:
kevlray

kevlray

Registered User.
Local time
Today, 10:01
Joined
Apr 5, 2010
Messages
1,046
I found a couple of places where you are missing a end if. The first one, where is the end if for the Me.RiskGrand <= 154 then where is the end if for the If Me.TF_Ben_NI_Number > 9.

If you indent for each If, it can be easier to track where you need the end if's
 
T

Tor_Fey

Registered User.
Local time
Today, 18:01
Joined
Feb 8, 2013
Messages
121
Hi kevlray

Where do the End ifs go in that code then? I'm not very great with VBA and has always been a struggle.

Regards
Tor Fey

kevlray said:
I found a couple of places where you are missing a end if. The first one, where is the end if for the Me.RiskGrand <= 154 then where is the end if for the If Me.TF_Ben_NI_Number > 9.

If you indent for each If, it can be easier to track where you need the end if's
Click to expand...
 
isladogs

isladogs

MVP / VIP
Local time
Today, 18:01
Joined
Jan 14, 2017
Messages
18,213
Its usually easier to read code like this if you indent it as below

Basically you misplaced 1 End If & missed out another
Anyway 2 End Ifs added & 1 removed

Hopefully this will be correct

Tor_Fey said:
Good Afternoon all;

I am getting an a Compile Error: 'Block If Without End If' in the below code :banghead::

Code: 
Private Sub Processbutton_Click()

If Me.RiskGrand = 0 Then
	MsgBox "The Case has not been risk tested yet", vbExclamation, "RISK ASSESSMENT REQUIRED"
Exit Sub
End If
If Me.RiskGrand <= 154 Then
	If MsgBox("You are about to finalise this record as a Low Risk. You will be unable to edit it" & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + 		vbYesNo, "SIGN OFF") = vbNo Then
		Exit Sub
	End If '<<ADDED
Else
	Me.TF_Ben_Outcome = "Low Risk, NFA"
	Me.TF_Ben_Outcome_Date = Date
	Me.TF_Ben_Outcome_By = CurrentUser()
End If
'End If '<<REMOVED

If Me.RiskGrand <= 154 Then
	If MsgBox("You are about to finalise this record as a Low Risk. You will be unable to edit it" & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + 		vbYesNo, "SIGN OFF") = vbNo Then
	Exit Sub
End If

If Me.RiskGrand >= 155 And Me.RiskGrand <= 189 Then
	MsgBox "Medium Risk"
End If

If Me.TF_Ben_NI_Number > 9 Then
	MsgBox "Please Change The NI Number To 9 Digits", vbInformation, "NI Number Can Only Be 9 Digits"
	Me.TF_Ben_NI_Number.SetFocus
Exit Sub
 
If Me.RiskGrand > 189 Then
	If MsgBox("You are about to finalise this record and create an Investigation Case." & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + vbYesNo, "SIGN 		OFF") = vbNo Then
		Exit Sub
	End If '<<ADDED
Else
	Me.TF_Ben_Outcome = "High Risk, Case created"
	Me.TF_Ben_Outcome_Date = Date
	Me.TF_Ben_Outcome_By = CurrentUser()

	DoCmd.SetWarnings False
	DoCmd.OpenQuery "Qry_TF_Ben_PrelimtoMain", acViewNormal, acEdit
	DoCmd.SetWarnings True
	DoCmd.Close acForm, "frm_tf_ben_IUO1"
End If

If MsgBox("DO YOU WANT TO OPEN MAIN RECORD? " & vbCrLf, vbYesNo) = vbYes Then
	DoCmd.OpenForm "frm_tf_ben_main", acNormal, "", "[tbl_tf_ben_main]![tf_ben_id]=[form]![frm_tf_ben_IUO1]![tf_ben_id]", , acNormal
	DoCmd.Close acForm, "frm_tf_ben_IUO1"
Else
	DoCmd.Close acForm, "frm_tf_ben_IUO1"
End If
    
End Sub
Tor Fey
Click to expand...

EDIT: Just spotted that kevlray said much the same thing whilst I was looking at this
 
T

Tor_Fey

Registered User.
Local time
Today, 18:01
Joined
Feb 8, 2013
Messages
121
Hi ridders;

Thanks for the pointers, seems to have cracked my issue.

Kind Regards
Tor Fey
 
T

Tor_Fey

Registered User.
Local time
Today, 18:01
Joined
Feb 8, 2013
Messages
121
Hi Ridders;

Think I spoke to soon, still getting the error :(

Regards
Tor Fey
 
isladogs

isladogs

MVP / VIP
Local time
Today, 18:01
Joined
Jan 14, 2017
Messages
18,213
Do a debug & identify where ...

It would help to add error handling in your procedures

This bit may is wrong...It should be Forms!
Check the filter criteria - it looks wrong from here

DoCmd.OpenForm "frm_tf_ben_main", acNormal, "", "[tbl_tf_ben_main]![tf_ben_id]=[Forms]![frm_tf_ben_IUO1]![tf_ben_id]", , acNormal
Click to expand...
 
T

Tor_Fey

Registered User.
Local time
Today, 18:01
Joined
Feb 8, 2013
Messages
121
Hi Ridders;

I have found the issue with my code, this bit of my code isn't working and causing the issue:

Code: 
If Me.RiskGrand <= 154 Then
    If MsgBox("You are about to finalise this record as a Low Risk. You will be unable to edit it" & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + vbYesNo, "SIGN OFF") = vbNo Then
    Exit Sub
End If

Is this because it has the 'Exit Sub' option?

Also this bit of code was wrong:

Code: 
If Me.TF_Ben_NI_Number > 9 Then
    'MsgBox "Please Change The NI Number To 9 Digits", vbInformation, "NI Number Can Only Be 9 Digits"
    'Me.TF_Ben_NI_Number.SetFocus
Exit Sub
End If

It should have been:

Code: 
If Len(TF_Ben_NI_Number) > 9 Then
   MsgBox "Please Change The NI Number To 9 Digits", vbInformation, "NI Number Can Only Be 9 Digits"
Exit Sub
End If

And also, you are were correct about this bit of the code:

Code: 
If MsgBox("DO YOU WANT TO OPEN MAIN RECORD? " & vbCrLf, vbYesNo) = vbYes Then
    DoCmd.OpenForm "frm_tf_ben_main", acNormal, "", "[tbl_tf_ben_main]![tf_ben_id]=[form]![frm_tf_ben_IUO1]![tf_ben_id]", , acNormal
    DoCmd.Close acForm, "frm_tf_ben_IUO1"
Else
    DoCmd.Close acForm, "frm_tf_ben_IUO1"
End If

it should have been:

Code: 
If MsgBox("DO YOU WANT TO OPEN MAIN RECORD? " & vbCrLf, vbYesNo) = vbYes Then
    DoCmd.OpenForm "frm_tf_ben_main", acNormal, "", "[tbl_tf_ben_main]![tf_ben_id]=[forms]![frm_tf_ben_IUO1]![tf_ben_id]", , acNormal
    DoCmd.Close acForm, "frm_tf_ben_IUO1"
Else
    DoCmd.Close acForm, "frm_tf_ben_IUO1"
End If

So everything seem to be working now, except for this:
Code: 
'If Me.RiskGrand <= 154 Then
    'If MsgBox("You are about to finalise this record as a Low Risk. You will be unable to edit it" & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + vbYesNo, "SIGN OFF") = vbNo Then
    'Exit Sub
'End If

as I had to comment it out, but unsure as to why it isn't working?

Thanks very much for all your help this far :)

Kind Regards
Tor Fey
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 18:01
Joined
Sep 21, 2011
Messages
14,264
As Ridders has not replied for a while, I'll try and help.
For the code not working either put the Exit Sub on the same line as the If or as I'd pefer, complete it with an End If as normal.
Code: 
If Me.RiskGrand <= 154 Then
    If MsgBox("You are about to finalise this record as a Low Risk. You will be unable to edit it" & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + vbYesNo, "SIGN OFF") = vbNo Then
        Exit Sub
    End If
End If
 
isladogs

isladogs

MVP / VIP
Local time
Today, 18:01
Joined
Jan 14, 2017
Messages
18,213
Gasman said:
As Ridders has not replied for a while, I'll try and help.
For the code not working either put the Exit Sub on the same line as the If or as I'd pefer, complete it with an End If as normal.
Code: 
If Me.RiskGrand <= 154 Then
    If MsgBox("You are about to finalise this record as a Low Risk. You will be unable to edit it" & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + vbYesNo, "SIGN OFF") = vbNo Then
        Exit Sub
    End If
End If
Click to expand...

Hello I'm back
Yes I agree with Gasman
In fact that's what I wrote in post #4 ...! :D
 
Orthodox Dave

Orthodox Dave

Home Developer
Local time
Today, 18:01
Joined
Apr 13, 2017
Messages
218
When I write a long sub with lots of interweaved If statements, I find it helpful to annotate the End If's with a comment showing which "If" they belong to. This also forces you to think the logic through. In your code for example:-
Code: 
Private Sub Processbutton_Click()

If Me.RiskGrand = 0 Then
MsgBox "The Case has not been risk tested yet", vbExclamation, "RISK ASSESSMENT REQUIRED"
Exit Sub
End If [B][I]'Me.RiskGrand = 0[/I][/B]

If Me.RiskGrand <= 154 Then
If MsgBox("You are about to finalise this record as a Low Risk. You will be unable to edit it" & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + vbYesNo, "SIGN OFF") = vbNo Then
Exit Sub
Else

Me.TF_Ben_Outcome = "Low Risk, NFA"
Me.TF_Ben_Outcome_Date = Date
Me.TF_Ben_Outcome_By = CurrentUser()
End If [B][I]'MsgBox("You are about to finalise this record as a Low Risk...[/I][/B]
End If [B][I]'Me.RiskGrand <= 154[/I][/B]
etc etc.

Ridders' and kevlray's indents are right, but if you have If statements that interweave with one another like some of yours, even indenting can become difficult to decipher.

Also, if I'm not mistaken you seem to have this statement in twice:
Code: 
If Me.RiskGrand <= 154 Then
If MsgBox("You are about to finalise this record as a Low Risk. You will be unable to edit it" & vbCrLf & vbCrLf & "Do you want to continue?", vbQuestion + vbYesNo, "SIGN OFF") = vbNo Then
Exit Sub
Else
 
T

Tor_Fey

Registered User.
Local time
Today, 18:01
Joined
Feb 8, 2013
Messages
121
Afternoon all;

Thanks for all your help on this, I think it is finally sorted.

Kind Regards
Tor Fey
 
You must log in or register to reply here.

Similar threads

N
Error Handling Code in Ms Access
Replies
10
Views
448
gemma-the-husky
gemma-the-husky
N
Implementing Parameterized Queries in DAO for Enhanced Security in Access- SQL Server Application
Replies
12
Views
653
mdlueck
M
P
Exit Do loop
Replies
9
Views
592
gemma-the-husky
gemma-the-husky
P
Create seperate PDF Files from a report Base on a query with more than 300 records
Replies
5
Views
506
Uncle Gizmo
Uncle Gizmo
D
  • Question
End if without block if Error
2
Replies
22
Views
1,827
Donnie1ben
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom