Data type mismatch - excel userform to access table

[chinaboy]

HI, I am using the code below to send data from a excel userform to an access table. I get a data mismatch at the line ".Fields("account_no").Value = textname2" In access this is a number field. If I the user fills in the account number in the user form, the data is submitted correctly. If they leave it blank then they get the data type mismatch. How can I avoid this error. I need the form to be submitted regardless or not if the field is blank.

Thanks in advance.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim wsh As Excel.Application
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "Table Name", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
With rst
.AddNew
.Fields("name").Value = textname
.Fields("name2").Value = textname1
.Fields("account_no").Value = textname2

.Update
End With

[MarvinP]

Hi chinaboy,

How about using a zero if the userform field is blank? Would Access like a zero better than a blank? Do you have validation turned on for that field in Access? Is it required and nulls allowed, in the Access table definition?

[chinaboy]

Yes access will allow a zero, but I rather it not have a zero the field. I do not have validation turned on for that field in access. The field is not a required field and I am not sure how to check if nulls are allowed.

[chinaboy]

I am still having trouble correcting this this error. It seems like the userform is submiting spaces and when I hover over the compile error . I tried the code below to try to get it to submit as empty, but that didn't work. What else can I do to make sure when the userform data is submitted to access the blank field is really empty? This field is a numeric field, not sure if this makes a different. But if I put a value in the field it works perfectly. But the field in not required and don't want to force the user to enter a value.

If Len(Account_no.Text) = 0 Then _
.Fields("Account_no").Value = ""

I found this solution in another post, but this seems like it goes to a spreadsheet first before going to Access. My user form should send the data directly to access.

if len(trim(Range("B" & r).Text)) > 0 then _
.Fields("Dispatch_date") = Trim(Range("B" & r).Value)

Thanks