Home > Mobile >  Why does this line of code work half the time, and the other half gives me Data Type Conversion Erro
Why does this line of code work half the time, and the other half gives me Data Type Conversion Erro

Time:07-28

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!!

CodePudding user response:

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.

  • Related