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

sathyaganapathi

Board Regular
Joined
Apr 29, 2021
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
[CODE]rst.Fields("Route_Time").Value = CDate(Plab_Update.RouteTime.Value)
[/CODE]
VBA Code:
rst.Fields("H1_Release_DateTime").Value = CDate(Plab_Update.Release1Date.Value)

I tried with below two codes and did not help.

VBA Code:
[CODE]rst.Fields("Route_Time").Value = VBA.Format(Plab_Update.RouteTime.Value, "HH:mm")
[/CODE]
VBA 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.
VBA Code:
[CODE]Plab_Update.RouteTime.Value = VBA.Format(Now(), "HH:mm")
[/CODE]
VBA 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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,266
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
If Not IsNull(RouteTime) Then rst.Fields("Route_Time")= CDate(Plab_Update.RouteTime)
 

sathyaganapathi

Board Regular
Joined
Apr 29, 2021
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,266
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 29, 2021
Messages
55
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
Rich (BB code):
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
VBA Code:
rst.Fields("Route_Time")= CDate(Plab_Update.RouteTime)
 

Attachments

  • runtimeerror13 type mismatch.jpg
    runtimeerror13 type mismatch.jpg
    20.8 KB · Views: 5

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,266
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 29, 2021
Messages
55
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,360
Office Version
  1. 365
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) & "#"
 

Forum statistics

Threads
1,144,363
Messages
5,723,932
Members
422,527
Latest member
TotalBeginner201

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top