SOLVING COMMON ERRORS IN ACCESS (Error 1) : Setting Date Field in Recordset to null

[capsule]

Error 1 : Setting Date Field in Recordset to null

This Forum helped me a lot and i would like to return the favor ..
I will save u a lot of time searching for a solution on the internet to the most Frustrated error in ms access in a simple way without too much coding .

Error 1 : Setting Date Field in Recordset to null " data conversation error 3421 "

Introduction : I gave up finding a straight solution on the internet for this one , so i came up with my own and it worked , so i hope it will work for you also ..


Solution : If the field is null set it back to itself .

Here is a simple dummy example i wrote to demonstrate the solution ( look for the bold text in side the code )

Code :

Sub Event_btnSaveEndTime () 

dim strEndDate as string 

With Form_frmMainForm

strEndTime = .txtEndTime.Value 

rs.Edit

'if the date field is null set it back to itself 

If IsNull(rs.Fields("actEndTime"))  Then

rs.Fields("actEndTime") = rs.Fields("actEndTime")

Else

rs.Fields("actEndTime") = strEndTime

End If

rs.Update

End With 

End Sub

Note : Keep visiting the forum from time to time , i will keep posting more solutions. For consistency i will use the same format for this thread - Error1 , Error 2 , and so on .

Best regards to all And happy coding
Ali - Capsule

[June7]

Could you explain more about the issue you encountered? I don't really understand the usage and why this was needed. Why would you want field to remain Null if there is input to the textbox?

[capsule]

OK The usage is this

scenario setup :

txtendtime , subfrm that display the recordset data , btnSaveTime , rs

Usage :

-ok say u have 2 text boxes for entering endTime and startTime ( they are optional of course ) u can set their value or remove the value and leave them empty .
-The textbox value whether it is empty or not should be reflected and displayed in the subform that has recordset tied to it , for that u need to update both the table and recordset.
-The save button have to update both the value in the database ( Using Update Query ) and the record set ( Using the Example i showed above ) so that it refelct and display the result of update .
-U may say why don't i use rs.requery to refresh the record set value but my project logic doesn't like that


without the solution :
You will encounter " data conversion error 3421 " when u hit btnSaveTime ( the update in database will be saved but in the sub form it will not and by that i mean recordset u need to update recordset value using edit - update with solution i mentioned above )

[June7]

I am still very confused. Why is an UPDATE query needed to set value in record displayed on form? Why not just populate by reference to field or bound textbox? Don't think I've ever encountered anything like this.