problem with for loop

[RobJen]

This is my first attempt at looping in VBA but it generates a "cant find method or (datamember? bad translation sorry lol)"-error. Does anyone know where the problem is hiding??

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql As String
Dim i As Integer

Set db = CurrentDb

For i = 1 To 2

If i = 0 Then
varWhere = "WHERE OPS_REG_ALGEMEEN.[TypeInzet] = 2 Or OPS_REG_ALGEMEEN.[TypeInzet] = 1 Or OPS_REG_ALGEMEEN.[TypeInzet] = 3 "
varNumber = 0.8
varMsg = "AA"
End If

If i = 1 Then
varWhere = "WHERE OPS_REG_ALGEMEEN.[TypeInzet] = 3 "
varNumber = 26
varMsg = "AB"
End If

If i = 2 Then
varWhere = "WHERE OPS_REG_ALGEMEEN.[TypeInzet] = 10 "
varNumber = 90
varMsg = "AC"
End If

    sql = "SELECT TOP 1 OPS_REG_ALGEMEEN.[PrimaryID], OPS_REG_ALGEMEEN.[TypeInzet], OPS_REG_ALGEMEEN.[Datum], Now()-[Datum] AS Expr1 " & _
    "FROM OPS_REG_ALGEMEEN " & _
    " " & varWhere & " " & _
    "ORDER BY OPS_REG_ALGEMEEN.[Datum] DESC;"
    Set rs = db.OpenRecordset(sql)
    If rs.Expr1 = varNumber Then MsgBox "w00t '" & varMsg & "' "

Next i

Set rs = Nothing
Set db = Nothing

End Sub

 

[casey]

Usually this error means that you have referenced a field name that is misspelled or doesn't exist. Either your sql statement has an error or your field names are misspelled or don't exist in the recordset you've created.

Set a breakpoint at the beginning of the code and step through the code using the F8 key to see where it errors out.

I would create a text box and set it = to your sql statement. Run the code and check the textbox results to see if the sql statement looks to be correct. If everything looks ok, then or copy them to a query and run it to see what you get.

HTH.

 

[RuralGuy]

Your code:

For i = 1 To 2

[b]If i = 0 Then[/b]
varWhere = "WHERE OPS_REG_ALGEMEEN.[TypeInzet] = 2 Or OPS_REG_ALGEMEEN.[TypeInzet] = 1 Or OPS_REG_ALGEMEEN.[TypeInzet] = 3 "
varNumber = 0.8
varMsg = "AA"
End If

i will never be = to 0

Just thought you'd like to know.

 

[RobJen]

Thanks, missed that error. Found the mistake tho, its not in the loop but the use of rs fields:

rs.Expr1 -->> rs("Expr1")