Recordset Problem

[PC User]

I made a function to insert a record from one table into another table. Sometimes some of the fields are blank; so I included the Nz() function to deal with null fields. After eliminating the null errors with the Nz() statements, the function no longer inserts the record from one table to the other when one of the fields is blank. However, the function still works if all the fields have data. I can't seem to trace the change in the function that keeps it from working all the time. Can someone take a look at this and help me?

Thanks,
PC

Code:
--------------------------------------------------------------------------------
'Insert project information into Work Order table.
Public Function InsertData()
On Error Resume Next
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim ctlListBox As Control

Set ctlListBox = Forms!frmMainEntry!lstNotifications
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblMainData", dbOpenDynaset)

With rst
.AddNew
If Nz(ctlListBox.Column(0), 0) <> 0 Then
![ProgramID] = ctlListBox.Column(0)
End If
If Nz(ctlListBox.Column(1), 0) <> 0 Then
![ActionDescription] = ctlListBox.Column(1)
End If
If Nz(ctlListBox.Column(2), 0) <> 0 Then
![Facility] = ctlListBox.Column(2)
End If
If Nz(ctlListBox.Column(3), 0) <> 0 Then
![ResponsibleParty] = ctlListBox.Column(3)
End If
If Nz(ctlListBox.Column(6), 0) <> 0 Then
![AdvancedNoticeDate] = ctlListBox.Column(6)
End If
If Nz(ctlListBox.Column(8), 0) <> 0 Then
![PM ID] = ctlListBox.Column(8)
End If
If Nz(ctlListBox.Column(9), 0) <> 0 Then
![Location] = ctlListBox.Column(9)
End If
If Nz(ctlListBox.Column(10), 0) <> 0 Then
![Section] = ctlListBox.Column(10)
End If
If Nz(ctlListBox.Column(11), 0) <> 0 Then
![Unit] = ctlListBox.Column(11)
End If
![EmailSent] = Now()
![EmailSender] = Forms!frmMainEntry!txtWelcome
![CreatedBy] = Forms!frmMainEntry!txtWelcome
![CreatedWhen] = Now()
.Update
End With

rst.Close
MsgBox "This program was added to the project list!" & vbCrLf & _
"Ok to continue?", vbOKOnly + vbExclamation, _
"Program Added To Project List"
DoCmd.Requery
DoCmd.SetWarnings True

End Function
--------------------------------------------------------------------------------

 

[Samoan]

Does the table you are inserting into require data to be in any or all of those fields? Do you get an error message when the insert fails?