0

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

Zrhoden
  • 57
  • 7
  • Are you certain that those 14 fields are always populated with valid data? Do you need to add a check for null? – David.Warwick Jul 27 '22 at 16:03
  • Trap for `IsDate(frm("Txt_DateScheduled" & i & "_JobTicket"))` – dbmitch Jul 27 '22 at 16:37
  • @David.Warwick They are not all always populated, only on a few items do we need all 14. Would you recommend an if then statement for it? If it is not null, then do the code above, and if null than to ignore it? – Zrhoden Jul 27 '22 at 17:45
  • So I have added an if statement to check for null values, but I still am coming up with data type conversion errors. Now it looks like: `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")` `Next i` – Zrhoden Jul 27 '22 at 18:34
  • @Zrhoden, can you please update your code in your original post? – David.Warwick Jul 27 '22 at 18:51
  • @Zrhoden, it's been a while since I messed with VBA. But I think the textbox itself is never going to be null. It is a textbox. You need to check if there is a value in the textbox. I believe you are trying to make a recordset based on values entered into a textbox, right? – David.Warwick Jul 27 '22 at 19:06
  • @David.Warwick of course, sorry! – Zrhoden Jul 27 '22 at 19:11
  • @David.Warwick the text box is an unbound one from a form. I am trying to basically INSERT these fields into another table I have, Tbl_Schedule. The end user will enter dates in these fields, anywhere from 2 of them to all 14. That being said, I do believe they all start out as null values when you open the form. They have no control or default value. So upon opening it is just a blank box. One thought I have had is should I dim i as a string instead of Long? Because the numbers themselves are integers, but the field name should be a string through and through, no? – Zrhoden Jul 27 '22 at 19:17
  • Here you go: https://social.msdn.microsoft.com/Forums/office/en-US/f6d50674-840b-4d27-8d32-0851f72e6c37/how-to-test-textbox-value-for-null-or-empty?forum=accessdev – David.Warwick Jul 27 '22 at 19:29
  • I Googled 'microsoft access check for value in textbox'. You should use: If Len(Me.TextBoxNameHere & vbNullString) = 0 Then – David.Warwick Jul 27 '22 at 19:31
  • I don't need to check for nulls, I am doing the opposite in checking Not IsNull(Myfield). Really I would like to learn how to make the command ignore them. I found a .ignorenulls property in the DAO library, but I don't know how to make that line of `rs("Date_Scheduled" & i)` ignore them. – Zrhoden Jul 27 '22 at 19:33
  • So make it >0 instead of = 0 – David.Warwick Jul 27 '22 at 19:35

1 Answers1

0

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.

David.Warwick
  • 620
  • 1
  • 9
  • 28
  • The only change I made was changing the double quotes "" to Null. It has worked on 5 different orders so far, so I think that did it! Thanks a ton! – Zrhoden Jul 27 '22 at 19:53