help with my vba code (1 Viewer)

[polyubi]

hello there.
I have the following code which runs well, but after i add a "if" condition, it went wrong with " end if wiithout block if error".
**************
Private Sub lookup_Click()

Dim i As Variant

Dim strWhere As String

strWhere = ""

If Not IsNull(Me.city) Then '-------I added an condition here

For Each i In Me!city.ItemsSelected
If strWhere <> "" Then
strWhere = strWhere & " OR "
End If


strWhere = strWhere & "[city]='" _
& Me![city].ItemData(i) & "'"

strWhere = strWhere & " AND "

End If '--------this is the place cannot go through, says "end if without block if"


If Not IsNull(Me.Text9) Then

strWhere = strWhere & "([age] >= " & Me.Text9 & ") AND "
End If
If Not IsNull(Me.Text11) Then

strWhere = strWhere & "([age] <= " & Me.Text11 & ") AND "
End If
If Len(strWhere) > 0 Then

strWhere = Left(strWhere, Len(strWhere) - 5)
End If
Next i
Debug.Print strWhere
Me.subform.Form.Filter = strWhere
Me.subform.Form.FilterOn = True

End Sub
***********************
could any people help me?

 

[PeterF]

There is no if command between both 'end if' marked red.
You test for a blank value of strWhere, if it's blank you should not append the 'or' or 'and', if it's not you have to prefix with 'or' to prevent ending with an unwanted extra or at the end (code in green not tested).

For Each i In Me!city.ItemsSelected
If strWhere <> "" Then
[COLOR="SeaGreen"]strWhere = strWhere & "or [city]='" & i & "'"
else
strWhere = strWhere & "[city]='" & i & "'"[/COLOR]
[COLOR="Red"]End If[/COLOR]

strWhere = strWhere & "[city]='" _
& Me![city].ItemData(i) & "'"

strWhere = strWhere & " AND "

[COLOR="red"]End If '--------this is the place cannot go through, says "end if without block if"[/COLOR]

 

[polyubi]

thanks for your reply.

did you see line 4?
If Not IsNull(Me.city) Then '-------I added an condition here

 

[MStef]

I suppose only, (because I don't know your task).
Your END IF must be after NEXT I.

 

[polyubi]

I suppose only, (because I don't know your task).
Your END IF must be after NEXT I.

I only want to make the list box "city" conditional

I tested if I put the "end if" after next i, also cannot go through.....

 

[jal]

By the way, I doubt VBA accepts:

Next i

I think it prefers:

Next

 

[Galaxiom]

Most problems with If and Loops become obvious if you indent your code properly.

 

[Galaxiom]

By the way, I doubt VBA accepts:

Next i

I think it prefers:

Next

The i can be used though it is not necessary.
Including it is a type of syntactic sugar

 

[boblarson]

The problem is this line:

If Not IsNull(Me.city) Then '-------I added an condition here

Because you have split the IF's in all other cases you can't have it on a single line. You would need to do the thing

If Not IsNull(Me.city) Then 
[B]    Whatever[/B]
[B]End If

[/B]

 

[Galaxiom]

As I said earlier, these problems become obvious when the code is formatted properly.

Private Sub lookup_Click()
 
Dim i As Variant
Dim strWhere As String
 
   strWhere = ""
 
   If Not IsNull(Me.city) Then [COLOR=seagreen]'-------I added an condition here[/COLOR]
 
      For Each i In Me!city.ItemsSelected
 
         If strWhere <> "" Then
            strWhere = strWhere & " OR "
         End If
 
         strWhere = strWhere & "[city]='" & Me![city].ItemData(i) & "'"
         strWhere = strWhere & " AND "
[COLOR=red]' The If block and For Loop are overlapping[/COLOR]
   End If  [COLOR=seagreen]'--------this is the place cannot go through, says "end if without block if"[/COLOR]
 
   If Not IsNull(Me.Text9) Then
      strWhere = strWhere & "([age] >= " & Me.Text9 & ") AND "
   End If
 
   If Not IsNull(Me.Text11) Then
      strWhere = strWhere & "([age] <= " & Me.Text11 & ") AND "
   End If
 
   If Len(strWhere) > 0 Then
      strWhere = Left(strWhere, Len(strWhere) - 5)
   End If
 
   Next i
 
   Debug.Print strWhere
   Me.subform.Form.Filter = strWhere
   Me.subform.Form.FilterOn = True
 
End Sub

 

[polyubi]

As I said earlier, these problems become obvious when the code is formatted properly.

Private Sub lookup_Click()
 
Dim i As Variant
Dim strWhere As String
 
   strWhere = ""
 
   If Not IsNull(Me.city) Then [COLOR=seagreen]'-------I added an condition here[/COLOR]
 
      For Each i In Me!city.ItemsSelected
 
         If strWhere <> "" Then
            strWhere = strWhere & " OR "
         End If
 
         strWhere = strWhere & "[city]='" & Me![city].ItemData(i) & "'"
         strWhere = strWhere & " AND "
[COLOR=red]' The If block and For Loop are overlapping[/COLOR]
   End If  [COLOR=seagreen]'--------this is the place cannot go through, says "end if without block if"[/COLOR]
 
   If Not IsNull(Me.Text9) Then
      strWhere = strWhere & "([age] >= " & Me.Text9 & ") AND "
   End If
 
   If Not IsNull(Me.Text11) Then
      strWhere = strWhere & "([age] <= " & Me.Text11 & ") AND "
   End If
 
   If Len(strWhere) > 0 Then
      strWhere = Left(strWhere, Len(strWhere) - 5)
   End If
 
   Next i
 
   Debug.Print strWhere
   Me.subform.Form.Filter = strWhere
   Me.subform.Form.FilterOn = True
 
End Sub

************************************

I agree with u, but I am totally confused now. Please just correct my code

 

[Galaxiom]

Without actually analysing the whole code I expect you need to move the troublesome End If to the line after the Next i

 

[polyubi]

Without actually analysing the whole code I expect you need to move the troublesome End If to the line after the Next i

I tried yesterday it doesn't work

 

[jal]

I tried yesterday it doesn't work

But there's no escaping it - you have to put the Next I in a suitable position to avoid compiler errors.

Add a Msgbox strWhere
to your code and then use control C to copy and paste the strWhere from the Msgbox into this thread. And if it looks incorrect, tell us how you WANT it to look and maybe we can help you re-code the loop to build it properly.