By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,625 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,625 IT Pros & Developers. It's quick & easy.

End If without Block If Error

P: 16
I'm getting an "End if without Block if" error with the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command42_Click()
  3. Dim ctl As Control
  4. Dim strTag As String
  5. Dim intCtr As Integer
  6. Dim strBuild As String
  7. Do
  8. For Each ctl In Me.Controls
  9.   With ctl
  10.     If TagCheck.ControlType = acCheckBox Then                   'Is the Control a Check Box
  11.       If TagCheck.Value = True Then                             'Is it Selected and does it have a Valid Tag?
  12.         intCtr = intCtr + 1                                     'Check Box Counter
  13.           If intCtr = 1 Then strTag = SourceMaterial.Tag        'Retrieve Tag of 1st Check Box
  14.             If SourceMaterial.Tag <> strTag Then                'Tags are not the same
  15.               MsgBox "All items selected must be of the same source material ", _
  16.                      vbExclamation, "Improper Selection"
  17.                      strBuild = ""
  18.                 Exit For
  19.             Else
  20.               strBuild = strBuild & SourceMaterial.Tag & ","    'Items that have been 'cleared'
  21.                 End If
  22.             End If
  23.         End If
  24.     End If
  25.   End With
  26. Loop Until [TagCheck] = False
  27. Next
  28. DoCmd.SetWarnings False
  29. DoCmd.GoToRecord , , acNext
  30. 'DoCmd.Save
  31. DoCmd.OpenQuery "qryTagUpdateFinish"
  32. DoCmd.OpenQuery "qryTagDelete"
  33. DoCmd.SetWarnings True
  34. End Sub
Feb 24 '14 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 10K+
P: 12,430
You have an extra End If.

I don't know if you are aware of this but line 14 does not require an end if. If you put everything on one line, the end if requirement is removed. So what you ended up doing was put in an end if for an if block that does not require it.
Feb 24 '14 #2

P: 16
When I remove one of the End If, I know get a Loop without Do error, which does not make sense because I have a do statement.
Feb 24 '14 #3

Expert Mod 15k+
P: 31,769
If you post which End If you removed we may be in a position to explain the problem for you. When you are explaining a problem you get in your code please post the line # or #s you're referring to. These may seem like obvious instructions but they're also very important.
Feb 24 '14 #4

Expert Mod 5K+
P: 5,397
You have an extra EndIF
You have the Next and Loop reversed
Proper indention will help you to troubleshoot the code.
Look at my indentation in the following:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command42_Click()
  2.     Dim ctl As Control
  3.     Dim strTag As String
  4.     Dim intCtr As Integer
  5.     Dim strBuild As String
  6.     Do
  7.         For Each ctl In Me.Controls
  8.             With ctl
  9.                 If TagCheck.ControlType = acCheckBox Then                   'Is the Control a Check Box
  10.                     If TagCheck.Value = True Then                             'Is it Selected and does it have a Valid Tag?
  11.                     intCtr = intCtr + 1                                     'Check Box Counter
  12.                         If intCtr = 1 Then strTag = SourceMaterial.Tag        'Retrieve Tag of 1st Check Box
  13.                         If SourceMaterial.Tag <> strTag Then                'Tags are not the same
  14.                             MsgBox "All items selected must be of the same source material ", _
  15.                             vbExclamation, "Improper Selection"
  16.                             strBuild = ""
  17.                             Exit For
  18.                         Else
  19.                             strBuild = strBuild & SourceMaterial.Tag & ","    'Items that have been 'cleared'
  20.                         End If
  21.                     End If
  22.                 End If
  23.             'End If '<< This is the extra End if
  24.             End With
  25.         Next ''<< Your Next was out of place
  26.     Loop Until [TagCheck] = False ''<so switched it
  27.     DoCmd.SetWarnings False
  28.     DoCmd.GoToRecord , , acNext
  29.     'DoCmd.Save
  30.     DoCmd.OpenQuery "qryTagUpdateFinish"
  31.     DoCmd.OpenQuery "qryTagDelete"
  32.     DoCmd.SetWarnings True
  33. End Sub
I've also fixed the errors noted in your orginal code so that in the above code, Line 23, Line 25, and Line 26 have the corrections.

It appears that you are running an "action query" in which case you should consider learning how to use the DAO recordset model so that you can use the databaseobject.execute method so that you do not have to disable and re-enable the warnings, as done on lines 27 and 32, you can error trap the query, and you can test to see howmany, if any, records were effected by the update.

Finally, as written, if you have an error after line 27, you have no error trap; thus, leaving the user blind to any and all future errors! (at least until the Program is restarted or successfully encounters the VBA command to re-enable warnings).
Feb 24 '14 #5

Expert 5K+
P: 8,703
This Base Code is significantly different that that which I gave you earlier. Unless I am missing something very obvious, I can't see how it will ever work. You are iterating through each Control on the Form via the Object Variable ctl, but using some obscure Object Reference (TagCheck) to refer to each Control's ControlType and Value Properties Code Lines 5 & 6). You then assign the Tag Property of the Object Variable SourceMaterial to the Variable strTag, but where is it declared and what exactly does it reference? I am referring to Code Lines 8 and 15. The With...End With Construct is stated but I do not see where it is actually used (.ControlType, .Tag). If I missed something in the translation then I apologize, but right now I am confused as to what I am looking at.
Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  3. For Each ctl In Me.Controls
  4.   With ctl
  5.     If TagCheck.ControlType = acCheckBox Then
  6.       If TagCheck.Value = True Then
  7.         intCtr = intCtr + 1
  8.           If intCtr = 1 Then strTag = SourceMaterial.Tag
  9.             If SourceMaterial.Tag <> strTag Then
  10.               MsgBox "All items selected must be of the same source material ", _
  11.                       vbExclamation, "Improper Selection"
  12.                         strBuild = ""
  13.                           Exit For
  14.             Else
  15.               strBuild = strBuild & SourceMaterial.Tag & ","
  16.             End If
  17.           End If
  18.       End If
  19.     End If
  20.   End With
  21. Next
Feb 25 '14 #6

Expert Mod 5K+
P: 5,397
I figured the anlysis of the code execution would constitute a new thread as its application wasn't part of the original post; however, as ADezii has brought it up... I was wondering the same thing!
Feb 25 '14 #7

P: 16
Sorry guys I'm not a big VBA programmer, clearly by my display above, but I thought I knew how I could make the code written for me work with my database.

Going back to the original thread (( Access Form Question about record verification )), the apples and oranges was referring to the [SourceMaterial] (Didn't want to bore you with specifics and the [Tagcheck] refers to the checkbox that is used.

For example, I want a list of all SourceMaterial X, so I check all the source material that is X, then I click Add Tag to have it print a list. But like I explained, I wanted the button to also make sure that all the source material I checked was the same, hence, the code above.

It made since what the code was doing and I thought all I needed to do was specify the the fields on the form ( SourceMaterial and Tagcheck).
Feb 25 '14 #8

Expert 5K+
P: 8,703
As stated in the earlier Reply, apply a Tag Property to ALL Check Boxes even those not related to any Source Material. If you have 4 Check Boxes whose Source Material is Omega3, then set their Tag Property to 'Omega3', if you have 2 Check Boxes whose Source Material is 'Alpha55', then set their Tag Property to 'Alpha55', etc... Should a Check Box not have a Source Material, simply set its Tag Property to 'N/A'. The Code is Generic and will handle the rest ensuring that all Check Boxes Selected relate to the same Source Material or will generate an Error Message to the User. If you cannot work this out with the Original Code that I gave you, then the only other Option as I see it is to Upload a Copy of your Database stripped of any sensitive Data.
Link to Original Thread:
Feb 25 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.