Data Type Conversion Error 3421 1

[xeb]

I keep getting Data Type Conversion Error 3421 on the following. Debug stops on !TimeInI = Me.TimeInI.Value.
Also, I don't get the error when I open the database and save the first record. It happens only when I try to save the second record.

Set db = CurrentDb
Set rst = db.OpenRecordset("Data")
With rst
.AddNew
!StudentFullName = Me.StudentFullName
!Staff1 = Me.Staff1
!Staff2 = Me.Staff2
!Staff3 = Me.Staff3
!Staff4 = Me.Staff4
!Staff5 = Me.Staff5
!cDate = Me.cDate
!Reason1 = Me.Reason1
!Reason2 = Me.Reason2
!Type1 = Me.Type1
!Type2 = Me.Type2
!Type3 = Me.Type3
!TimeInI = Me.TimeInI.Value
!TimeOutI = Me.TimeOutI.Value
!TimeInR = Me.TimeInR.Value
!TimeOutR = Me.TimeOutR.Value
!Room = Me.Room
!Door = Me.Door.Value
!ConstantContact = Me.ConstantContact.Value
!Checks = Me.Checks
.Update

End With

rst.Close

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "CLEARME" Then ctl.Value = ""
Next ctl

 

[CautionMP]

xeb
Is [tt]Me.TimeInI[/tt] one of the fields that gets cleared becuase the tag is "[tt]CLEARME[/tt]"?

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[SuryaF]

What is the type of the TimeInI field?
What is the value of the Me.TimeInI.Value when the error happens?

 

[xeb]

Thanks for answering.

Me.TimeInI does have the CLEARME tag. They all do.

Me.TimeInI is a Combo Box that posts Short Time.

Me.TimeInI value when the error occurs is empty. It is not a required field. If you enter data in Me.TimeInI the error then occurs on Me.TimeOutI, the next one.

Also, when I put something like Me.TimeInI.Value = Null it works but posts "0" in the field instead of what the user entered.

Thanks again.

 

[JerryKlmns]

You should then, check for empty and if true then don't include it in the fields to be updated. Also "0" is probably a default value on that field. Delete it from the table design.

 

[xeb]

Thanks for answering.

I took "0" out as a default value in the table.

How do I check for empty and if true then not include it in the fields to be updated?

 

[JerryKlmns]

With rst
.AddNew
!StudentFullName = Me.StudentFullName
!Staff1 = Me.Staff1
!Staff2 = Me.Staff2
!Staff3 = Me.Staff3
!Staff4 = Me.Staff4
!Staff5 = Me.Staff5
!cDate = Me.cDate
!Reason1 = Me.Reason1
!Reason2 = Me.Reason2
!Type1 = Me.Type1
!Type2 = Me.Type2
!Type3 = Me.Type3
If Me.TimeInI.Value & "" <> "" Then 'NOT Empty
!TimeInI = Me.TimeInI.Value
End If
If Me.TimeOutI.Value & "" <> "" Then 'NOT Empty
!TimeOutI = Me.TimeOutI.Value
End If
!TimeInR = Me.TimeInR.Value
!TimeOutR = Me.TimeOutR.Value
!Room = Me.Room
!Door = Me.Door.Value
!ConstantContact = Me.ConstantContact.Value
!Checks = Me.Checks
.Update

End With

 

[xeb]

JerryKlmns:

Thanks so much. It works fine now.

Also, thanks for posting the code exactly how it should be. That is the difference between a good answer and a great answer for someone like me who is new at this.

Thanks again,

xeb

 

[JerryKlmns]

xeb

I treat people the way I like to be treated, too. Thank you for your kind words and the *. Glad to be of help.