Why does this line of code work half the time, and the other half gives me Data Type Conversion Error 3421

Question

Here is the full code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim frm As Access.Form
Dim i As Long

'For readability
Set frm = Forms!Frm_JobTicket

'Open Tbl_Schedule for adding Schedule Dates
Set db = CurrentDb
Set rs = db.OpenRecordset("Tbl_Schedule", dbOpenDynaset, dbAppendOnly)

'Creates loop for fields 1-14. Sets Date_ScheduledX = Forms!Frm_JobTicket!Txt_DateScheduledX. Runs through Loop then closes recordset
rs.AddNew
 For i = 1 To 14
    If (Not IsNull(frm("Txt_DateScheduled" & i & "_JobTicket"))) Then
    rs("Date_Scheduled" & i) = frm("Txt_DateScheduled" & i & "_JobTicket")
    End If
    Next i

'Adds in Sales Order Number to Tbl_Schedule
rs!Sales_Order_Number = frm("Sales_Order_Number")

'Adds in Part Number to Tbl_Schedule
rs!Part_Number = frm("Part_Number")
    
    'Adds updates and closes table
    rs.Update
    rs.Close
    
'Shows message box to inform the User if item was Scheduled
MsgBox "Item Scheduled."

'Runs Private Sub above. Clears all values from DateScheduled1-14 on Frm_JobTicket to null
ClearFields

'Clears DB and RS to null
Set db = Nothing
Set rs = Nothing

The line that doesn't work is this rs("Date_Scheduled" & i) = frm("Txt_DateScheduled" & i & "_JobTicket"). Sometimes it will run perfectly fine, and other times it gives me an endless flow of 3421 Data type conversion errors. I do not know what could be going wrong, none of the fields have default values, all of the fields in the table side are Date/Time with this same format, and now I am checking for nulls.

Any help would be greatly appreciated!!

Answer 1

Maybe something like

If Len(Me.Txt_DateScheduled & vbNullString) > 0 Then
    rs("Date_Scheduled" & i) = frm("Txt_DateScheduled" & i & "_JobTicket")
Else
    rs("Date_Scheduled" & i) = ""
End If

This is completely untested, but I think you should get the concept.