For Each control variable ...... 1

[Domino2]

I have some code that runs ok in Access 2010, but coughs in Access 2007

Dim TmpKeywords() As String
Dim tblKeywords As String
Dim i As Integer
Dim sql As String

tblKeywords = "one,two,three"
TmpKeywords = Split(tblKeywords, ",")

For Each i In TmpKeywords
       sql = sql & " OR Documents Like '%" & i & "%'"
Next i

It halts on the i, giving a notice:
For each control variable on arrays must be varient

I had to import the Access 2010 into a 2007 database because the control events did not work?, looked at all references.
seemed same. Maybe a backward compatability problem? Any ideas appreciated

 

[MajP]

 Dim TmpKeywords() As String
 Dim tblKeywords As String
 Dim i As Integer
 Dim sql As String

 tblKeywords = "one,two,three"
 TmpKeywords = Split(tblKeywords, ",")

For i = lbound(TmpKeywords) to ubound(tmpKeywords)
   sql = sql & " OR Documents Like '%" & tempkeywords(i) & "%'"
Next i

That code most definately never worked as written.

 

[Domino2]

Many thanks MajP, that works, just had to change tempKeywords (tmpkeywords)

It did work in Access 2010, must have been lucky. Anyway, many thanks

 

[PHV]

You simply had to change the type of i:

Dim TmpKeywords() As String
Dim tblKeywords As String
Dim i As [!]Variant[/!]
Dim sql As String

tblKeywords = "one,two,three"
TmpKeywords = Split(tblKeywords, ",")

For Each i In TmpKeywords
       sql = sql & " OR Documents Like '%" & i & "%'"
Next i

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Domino2]

Thanks PHV. I thought that was the case and had tried it, but the same error was generated. All worked in 2010 but 2007 did not like it?

 

[MajP]

This may have been a typo, but I doubt you got lucky on this line
sql = sql & " OR Documents Like '%" & i & "%'"
Has to be
sql = sql & " OR Documents Like '%" & tempkeywords(i) & "%'

 

[PHV]

MajP, For Each iterates the valuse, not the indices.

 

[PHV]

OOps, sorry for the typo (valuse instead of values)

 

[PHV]

Domino2, FYI: the code I've posted 15 Jan 13 13:30 works as expected in my ac2007 db.

 

[Domino2]

Strange why I had problems. I know I had the pain trying to find why events were not working (just because of trusted locations. However all working now thanks. If I can I will revert it back to see what happens now events working.