Probs with nested For/Next Loop

[Guest]

Hello folks,
Below is some code that I'd be grateful if you'd look over and tell me what
I've done wrong.
I'm trying to get the outer loop and inner loop to increase by one each time
so that the SQL is acting open each of the 12 sets of controls in the form.
The outer loop seems to work fine but the inner loop is stuck at the first
control. I know this should be easy but I'm stuck again!!

X = 1
Y = 1
For X = 1 To 12
varEstCode = Me("cboEstCode" & X)
If IsNull(varEstCode) Then
Exit For
End If
For Y = 1 To 12
strCands = Me("txtCands" & Y)
strSQL = "INSERT INTO tbl_Estimates (CentreNo, EstCode, Cands)" _
& " VALUES (" & Me!cboCentreNo & ", " & "'" & varEstCode & "'" &
", " & strCands & ");"
db.Execute strSQL
Exit For
Next
Next

Thanks for any help/advice you can give.
Regards,

Lee

 

[Guest]

Try removing the Exit For statement after the db.Execute statement in the
scond loop.

Hope This Helps
Gerald Stanley MCSD

 

[Guest]

Thanks for the reply Gerald but I'm afraid that doesn't work. I've now
resolved it by removing the inner loop - I realise on looking at it yet again
that there's no need for one anyway!! I've also added the Nz function to
stop the value in strCands being Null.
The revised code is below should anyone else view this post:

X = 1
For X = 1 To 12
varEstCode = Me("cboEstCode" & X)
strCands = Nz(Me("txtCands" & X), "")

If IsNull(varEstCode) Then
Exit For
End If

strSQL = "INSERT INTO tbl_Estimates (CentreNo, EstCode, Cands)" _
& " VALUES (" & Me!cboCentreNo & ", " & "'" & varEstCode & "'" & ", " &
strCands & ");"
db.Execute strSQL
Next

Regards,

Lee