Problem with For Loop and If Then Else

[jpl458]

In preceeding code I successfully get a record from SQL server, and I want to see if the contents of any column in the recordset is Null, Zero Length. I want to loop through the fields in the recordset (rsdbo). There is data in the recordset, I've checked that, but the loop bombs with eithet "Next without For", or "Else without If", depending on the order. The "If Trim" statement was supplied by MajP (and there is another try commented out), but I suspect that the problem might be there, or there is something about For Loops and Ifs I don't understand.

If there is no data in the record set I want to do nothing. But if there is data I want to publish a message box alerting the data entry person that data already exists.

Sounds simple enough, but my my, I can't get it to work.

'See if data already entered
Dim I As Integer
I = 0
rsdbo.MoveFirst 'Position cursor on first record
'In rsdbo recordset
'Start Loop on Last_Name column in rsdbo recordset
For I = 0 To 9
'If Len(rsdbo.Fields(I).Value & "") = 0 Then
If Trim(rsdbo.Fields(I).Value & " ") = "" Then 'If Null or Zero Length
MsgBox "Null or Zero"
Next
Else
End If
MsgBox "Data Already Entered"
Me.Last_Name.SetFocus

Thanks in advance

jpl

 

[dhookom]

You can't use If inside a For Next loop unless the End If is also inside the loop.


Duane
Hook'D on Access
MS Access MVP

 

[randy700]

For I = 0 To 9
   If Trim(rsdbo.Fields(I).Value & " ") = "" Then
      MsgBox "Null or Zero"
   Else
      MsgBox "Data Already Entered"
   End If
Next

Randy

 

[jpl458]

Thanks to Duane and Randy, that solves the loop problem, and it also makes perfect sense, at least now. That was probably the only varation I didn't try. But the If Trim, or the other attempt, neither work. If there is data in the recordset or not it takes the "Data Already Entered" branch. I've tried taking the data out of the recordset and putting it in a variant or a string then doing the If, but that dosen't work either. Your sage help would be appreciated.

Thanks again

jpl

 

[jpl458]

I found the problem. The recordset will always have an account number, and my loop checking for Nulls started at relative zero in the recordset, which is account number. I start the loop at position 1 and it works fine.

Thanks again for the prompt help.

jpl