Type Mismatch error when entering data into access database from excel using userform

[sathyaganapathi]

I am entering data into userform and want to store the data in access database.
I am getting 'Type mismatch' error (run_time error '-2147352571(80020005)' while running the program and the error is pointing out to the below code.
If I enter the date into userform field I am not getting the error and data is saved into access database.
I have not entered any date to the field. It is left blank. There are several date and time fields which will be filled at different time. I cannot enter date into all the fields together.

[CODE]rst.Fields("Route_Time").Value = CDate(Plab_Update.RouteTime.Value)

[/CODE]

rst.Fields("H1_Release_DateTime").Value = CDate(Plab_Update.Release1Date.Value)

I tried with below two codes and did not help.

[CODE]rst.Fields("Route_Time").Value = VBA.Format(Plab_Update.RouteTime.Value, "HH:mm")

[/CODE]

[CODE=vba]rst.Fields("Route_Time").Value = VBA.Format(Plab_Update.RouteTime.Value, "HH:nn")

[/CODE]

The field "Plab_Update.RouteTime" and "Plab_Update.Release1Date" are set with below format.

[CODE]Plab_Update.RouteTime.Value = VBA.Format(Now(), "HH:mm")

[/CODE]

Plab_Update.Release1Date.Value = VBA.Format(Now(), "mm/dd/yyyy HH:mm")

In other VBA code I have used the above code (1st one) and it is working well.
Please help with some solution.

 

[Micron]

You cannot pass Null to a conversion function (e.g. CDate, Cdbl, etc.). Suggest use IF block or statement to execute the line or bypass it. This will only attempt to convert if RouteTime is not Null:

If Not IsNull(RouteTime) Then rst.Fields("Route_Time")= CDate(Plab_Update.RouteTime)

 

[sathyaganapathi]

You cannot pass Null to a conversion function (e.g. CDate, Cdbl, etc.). Suggest use IF block or statement to execute the line or bypass it. This will only attempt to convert if RouteTime is not Null:

If Not IsNull(RouteTime) Then rst.Fields("Route_Time")= CDate(Plab_Update.RouteTime)

Hi Micron,
Thanks again for the suggestion.
I tried that and now I am getting 'Type Mismatch with Run-time error 13'.
Any other option or some other correction required?

 

[Micron]

Post what you tried rather than quoting my post and maybe include the whole procedure if it isn't very long.
Please try not to be vague. I can't see what you see so I'm left wondering "on which line?" the same one or a different one? I tend to take nothing for granted when troubleshooting code.

 

[sathyaganapathi]

Post what you tried rather than quoting my post and maybe include the whole procedure if it isn't very long.
Please try not to be vague. I can't see what you see so I'm left wondering "on which line?" the same one or a different one? I tend to take nothing for granted when troubleshooting code.

Hi Micron,
Sorry..
I tried the code you provided as it is.

If Not IsNull(RouteTime) Then rst.Fields("Route_Time")= CDate(Plab_Update.RouteTime)

and the type mismatch (run-time error 13) error is pointing out on

rst.Fields("Route_Time")= CDate(Plab_Update.RouteTime)

 

[jackd]

What is the data type of your Route_Time field?

 

[sathyaganapathi]

What is the data type of your Route_Time field?

Hi jackd,
it is 'Date/Time' with 'short time' Format.

 

[Micron]

AFAIC, this is key
If I enter the date into userform field I am not getting the error
Since I have to go out and you don't seem to want to reveal the rest of the code I'll let jackd take over. Good luck!

 

[sathyaganapathi]

AFAIC, this is key
If I enter the date into userform field I am not getting the error
Since I have to go out and you don't seem to want to reveal the rest of the code I'll let jackd take over. Good luck!

As the code was too big, I was preparing a short version for posting it here. I have reduced the code the minimum and attaching here. The error is observed when the date fields are left blank and run it.

Private Sub SaveClose_Click()
        
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim qry As String
    Dim Source As String

    Source = frm_PlabInput.Source.Value
    
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Source
    
    If frm_PlabInput.txtId.Value <> "" Then
        qry = "SELECT * FROM TBL_PlabInput WHERE ID = " & frm_PlabInput.txtId.Value
    Else
        qry = "SELECT * FROM TBL_PlabInput Where ID = 0"
    End If
    
    rst.Open qry, cnn, adOpenKeyset, adLockOptimistic
    
    If rst.RecordCount = 0 Then
        rst.AddNew
    End If
 
    
    rst.Fields("Mould_No").Value = Plab_Update.MouldNo.Value
    rst.Fields("Microscope_No").Value = Plab_Update.Microscope.Value
    rst.Fields("Routed_by").Value = Plab_Update.RouteBy.Value
    
    If Not IsNull(RouteTime) Then rst.Fields("Route_Time") = CDate(Plab_Update.RouteTime)

    rst.Fields("Reading_By").Value = Plab_Update.ReadingBy.Value
    rst.Fields("Reading_Time").Value = CDate(Plab_Update.ReadingTime.Value)
    rst.Fields("SAP_Entry_By").Value = Plab_Update.SAPEntryBy.Value
    rst.Fields("SAP_Entry_Time").Value = CDate(Plab_Update.SAPEntryTime.Value)
    rst.Fields("Lot_Cleared_By").Value = Plab_Update.LotClearedBy.Value
    rst.Fields("Lot_Cleared_DateTime").Value = CDate(Plab_Update.LotClearDateTime.Value)
    
    rst.Update
    
    MsgBox "Updated Successfully", vbInformation
        Plab_Update.Hide
        frm_PlabInput.Show
    Call frm_PlabInput.List_box_Data
End Sub

 

[jackd]

is your form bound?
Untested, but for your date/time fields try

rst.Fields("Reading_By").Value = Plab_Update.ReadingBy.Value
rst.Fields("Reading_Time").Value = "#" & CDate(Plab_Update.ReadingTime.Value) & "#"
rst.Fields("SAP_Entry_By").Value = Plab_Update.SAPEntryBy.Value
rst.Fields("SAP_Entry_Time").Value = "#" & CDate(Plab_Update.SAPEntryTime.Value) & "#"
rst.Fields("Lot_Cleared_By").Value = Plab_Update.LotClearedBy.Value
rst.Fields("Lot_Cleared_DateTime").Value = "#" & CDate(Plab_Update.LotClearDateTime.Value) & "#"