Dear all,
I have a Excel form (Invfrm) that data will be saved to Access.
In Excel form I have a textbox (txtAmt) for user to key in sum of money and in Access I have a field Amount (not primary key) having some properties as belows:
Data type Number
Field size Double
Format Standard
Decimal Places Auto
Required No
Smart Tags No
and I use the code below to save data from Excel form to Access:
Sub InvoiceLink()
On Error GoTo InvoiceLink_Err
Dim objMyConn, objMyRecordset, strSQL
Set objMyConn = CreateObject("ADODB.Connection")
Set objMyRecordset = CreateObject("ADODB.Recordset")
objMyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=F:\Nam\Macro\Excel.mdb"
objMyConn.Open
strSQL = "select * from INVREGISTER order by InvNo"
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open Source:=strSQL, CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, Options:=adCmdText
With objMyRecordset
.AddNew
!Amt = Invfrm.txtAmt
.Update
End With
Else
Exit Sub
End If
InvoiceLink_Exit:
objMyConn.Close
Set objMyConn = Nothing
Exit Sub
InvoiceLink_Err:
MsgBox Err.Description
Resume InvoiceLink_Exit
objMyConn.Close
Set objMyConn = Nothing
End Sub
The problem is that if the user key in nothing to txtAmt or txtAmt="" then if I run the above the error is 'type mismatch'. I wonder that Access doesn't accept empty value.
Could any one can help me!
Thanks a lot!
Nam
Bookmarks