Object Required Error

[Guest]

I am not sure why I get that error I have commented the part of code that
gives an error:

Set CONNTDC = New ADODB.Connection
CONNTDC.Open connstring
Set rsItem = New ADODB.Recordset
rsItem.ActiveConnection = CONNTDC
rsItem.Open "SELECT IMFGR, ICOLOR, IPATT, ICCTR, IPOL1, IPOL2, IPOL3 FROM
QS36F.ITEM"

DoCmd.DeleteObject acTable, "tblItem"
CurrentProject.Connection.Execute "CREATE TABLE tblItem(MFGR CHAR(3), COLOR
CHAR(4), PATTERN CHAR(9), COSTCENTER CHAR(3), IPOL1 CHAR(3), IPOL2 CHAR(3),
IPOL3 CHAR(3))"

While Not rsItem.EOF
''''''''''''''''''''''''''''''''''If rsItem.Fields(4).Value Is Null Then '
this is where I get an error.
FPOL1 = "-"
Else: FPOL1 = rsItem.Fields(4).Value
If rsItem.Fields(5).Value Is Null Then
FPOL2 = "-"
Else: FPOL2 = rsItem.Fields(5).Value
If rsItem.Fields(6).Value Is Null Then
FPOL3 = "-"
Else: FPOL3 = rsItem.Fields(6).Value
End If
End If
End If

DoCmd.RunSQL "INSERT into tblItem values('" & rsItem.Fields(0).Value & "',"
& QS(rsItem.Fields(1).Value) & "," & QS(rsItem.Fields(2).Value) & ",'" &
rsItem.Fields(3).Value & "','" & FPOL1 & "','" & FPOL2 & "','" & FPOL3 & "')"

rsItem.MoveNext
Wend
rsItem.Close
CONNTDC.Close

 

[ruralguy via AccessMonster.com]

Is Null is used in SQL. In VBA you need to use the IsNull() function.

For example: If IsNull(rsItem.Fields(4)) Then

For the record, the .Value property is the default property in VBA and need
not be specified.