[原创]域更新函数

时　间:2009-01-18 11:17:40
作　者:郑家龙   ID：90  城市：上海
摘　要:[原创]域更新函数
正　文:

总觉得ACCESS-VBA中少了这么一个函数,既时写了一个请大家提出意见:
'--------------------------------------------------------------------------------------------------------------
'名称: 域更新函数
'功能: 同Dlookup等域函数
'参数:
' 同Dlookup等域函数,但Expr参数有点像Switch,以便更新多少字段.
' cn参数可以用来?行事??理
'用法:
' 1.先决条件:假如有表[BMB],且含有字段luoma,Int,ddate,check,mony
' 2.debug.Print DSet("luoma,aa,Int,3455,ddate,2009-9-9,check,-1,mony,5000 ","BMB","id=0")
'返回: True表示成功,False表示未成功或没有更新
'日期: 2007-07-26
'改正: 2007-07-26
'版本: 1.01
'作者: zhengjialon
'--------------------------------------------------------------------------------------------------------------
Public Function DSet(Expr As String, Domain As String, Optional Criteria As String, Optional cn As ADODB.Connection) As Boolean
On Error GoTo lblErr:
Dim i As Integer, l As Integer
Dim ExprS As Variant
Dim rst As New ADODB.Recordset

ExprS = Split(Expr, ",")
l = UBound(ExprS)

rst.Open "Select " & "*" & " From " & Domain & IIf(Trim(Criteria) <> "", " Where " & Criteria, ""), CurrentProject.Connection, adOpenStatic, adLockOptimistic, adCmdText

If Not rst.EOF Then
For i = 0 To l - 1 Step 2
Select Case rst(ExprS(i)).Type
Case adChar 'String
rst(ExprS(i)) = IIf(ExprS(i + 1) = "", Null, ExprS(i + 1))
Case adBoolean '
rst(ExprS(i)) = IIf(Trim(ExprS(i + 1)) = "" Or Trim(ExprS(i + 1)) = False Or Trim(ExprS(i + 1)) = 0, False, True)
Case Else
rst(ExprS(i)) = IIf(Trim(ExprS(i + 1)) = "", Null, ExprS(i + 1))
End Select
Next i
rst.Update
DSet = True
End If

rst.Close
Set rst = Nothing

lblExit:
Exit Function
lblErr:
If Err.Number = 3265 Then 'FieldName Is Noting
MsgBox "FieldName: [" & ExprS(i) & "] Is Noting ! ", vbCritical
ElseIf Err.Number = -2147352571 Then 'FieldValue <> FieldType Tip
MsgBox "FieldValue: [" & ExprS(i + 1) & "] Is Error ! ", vbCritical
Else 'ohter error
MsgBox Err.Number & Err.Description
End If
End Function
'增加了更多容错处理

Access软件网QQ交流群 (群号:483923997) 　　　　　　Access源码网店