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.