Dealing with Nulls in a loop

[Baby Face Lee]

<< I accidentally posted this to the Database Design group but have stated
there to ignore it as it was really intended for Forms-Coding >>

Hello again guys,
Would you be able to look at the code below and tell me where I'm going
wrong. I'm trying to get the loop to undertake an update SQL query where
there are values in a control but I'd like it to exit the sub when it comes
across a control that's empty.

Dim db As DAO.Database
Dim strSQL As String
Dim X As Integer 'EstCode control number
Dim Y As Integer 'Cands control number
Dim strEstCode As String
Dim strCands As String

Set db = DBEngine(0)(0)

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

Hope you can help get me back on track again.

Thanks!

Lee

 

[Marshall Barton]

Would you be able to look at the code below and tell me where I'm going
wrong. I'm trying to get the loop to undertake an update SQL query where
there are values in a control but I'd like it to exit the sub when it comes
across a control that's empty.

Dim db As DAO.Database
Dim strSQL As String
Dim X As Integer 'EstCode control number
Dim Y As Integer 'Cands control number
Dim strEstCode As String
Dim strCands As String

Set db = DBEngine(0)(0)

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

String variables can not be Null. Either change it to a
Variant or, better, chect the combo box for Null before
assigning it to the string variable.

 

[Baby Face Lee]

Thanks for the advice Marshall.
Much appreciated.

Lee