Error in query expression

[arunakumari02]

I have 5 rows but if I have only 2 rows in record set then I getting an error .

For i = 1 to 5
Call change()
Next
 
 
CHANGE(i)
 
Sql = "select * from table where Num= " &forms!frmchange("txtnum" &i)
Set recordset = CurrentDb.OpenRecordset(Sql)

If I have only 2 records in the recordset it gives an error "Syntax error in query expression 'Num = '

Any hints on it.

 

[wiklendt]

have you tried

i = 0 to 4

?

 

[boblarson]

Do a dcount first to find out how many records there actually are.

 

[LPurvis]

Also, can you describe a bit what you're actually doing?
You have five controls on your form (called txtnum 1 to 5).
Presumably in these you somehow have field values entered for specific rows in a table?
How do you know which values? How do you know the values will exist in the table?

It seems to be that if the rows were missing from the table then you wouldn't be getting the error - you'd just be executing a SQL statement which returned no rows into the recordset.
So it must be that there are empty controls on your form... (so again - I just question what you mean about "rows"...).
If they're distinct controls rather than rows then you'd want to check that the control is not Null before proceeding?

With Forms!frmchange("txtnum" & i)
    If Not IsNull(.Value) Then
        Sql = "select * from table where Num= " & .Value
        Set recordset = CurrentDb.OpenRecordset(Sql)
    End If
End With

Cheers.

 

[LPurvis]

Or... to validate input a bit more, instead of just checking for Null you could go with:

If IsNumeric(.Value) Then

As it seems the input you're expecting is going to be numeric ("Num" being my clue there) - and IsNumeric function handles Null nicely (which certainly can't be said of all built in functions).

 

[datAdrenaline]

.. Hmmm ... maybe this:

Call change()

Should be ...

Call change(i)

.. But as Leigh as asked ... more detail would be great!! ... Also, just an FYI, if you are using SQL and RECORDSET as variable names, I would suggest NOT using them as they are reserved words.

 

[georgedwilkinson]

Could you please put:

Debug.Print Sql

on the line right after the "Sql =" and post the results from the immediate window back to us?

Or, just try the SQL yourself from the immediate window in a query window.

 

[arunakumari02]

This worked fine.

Thanks

Also, can you describe a bit what you're actually doing?
You have five controls on your form (called txtnum 1 to 5).
Presumably in these you somehow have field values entered for specific rows in a table?
How do you know which values? How do you know the values will exist in the table?

It seems to be that if the rows were missing from the table then you wouldn't be getting the error - you'd just be executing a SQL statement which returned no rows into the recordset.
So it must be that there are empty controls on your form... (so again - I just question what you mean about "rows"...).
If they're distinct controls rather than rows then you'd want to check that the control is not Null before proceeding?

With Forms!frmchange("txtnum" & i)
    If Not IsNull(.Value) Then
        Sql = "select * from table where Num= " & .Value
        Set recordset = CurrentDb.OpenRecordset(Sql)
    End If
End With

Cheers.