麻辣家族討論版版 - Powered by Discuz! Board

Private Sub clearNwCmd6_Click()
Call comBindCmd56
mLoadFile.mConstrCls = mconStr
With mLoadFile
Set .mShtCls = upDataSht1
.pDataSource = mDataBasePath
If .DataSourceExisted(dataPath) = True Then 'dataPath 為 combobox 指定資料庫名稱
'Call .LoadDataSource(dataPath, mData1) ', multiPageNo)
Call .clearDataNWSource(dataPath, getAppTxt5.Text)
Else
MsgBox "指定報單號碼資料不存在"
End If
End With
End Sub
Sub comBindCmd56()
With upDataSht1
getAppTxt5.Value = .Range("b2").Value
End With
dataPath = dataBaseComb2.Value '先由comBoBox1取出資料庫名稱
Select Case dataPath
Case Is = "TRANCBS"
mDataBasePath = "D:\trancbs\database\cbsData.mdb"
mconStr = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
mLoadFile.mSqlCls = "SELECT 項次_INV主檔.MSGCODE,項次_INV主檔.SERSNO,項次_INV主檔.項次號碼,項次_INV主檔.數量,項次_INV主檔.UNITAMT,項次_INV主檔.報單淨重 FROM 項次_INV主檔 WHERE 項次_INV主檔.MSGCODE AND SERSNO= '" & getAppTxt5.Text & "' ORDER BY 項次_INV主檔.項次號碼 ASC" '測試
End Select
End Sub
Public Function clearDataNWSource(ByVal dataPath As String, ByVal mAppNo As String)
Application.ScreenUpdating = False
On Error GoTo ErrHandle
Set mCon = New ADODB.Connection
Select Case dataPath
Case Is = "MYSQL"
With mCon
.Open mconStr
End With
Case Else
With mCon
.Open mconStr & mDataSource
End With
End Select
Set mRst = New ADODB.Recordset
With mRst
.ActiveConnection = mCon
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockPessimistic
.Source = mSql
.Open
End With
If Not mRst.EOF Then
mData1 = mRst.GetRows
End If
's = mRst.Fields.Count
With mSht
mRow = .Range("c" & .Rows.Count).End(xlUp).Row
End With
For m = 2 To mRow
cStr1 = mSht.Cells(m, 1)
cStr2 = mSht.Cells(m, 2)
cStr3 = mSht.Cells(m, 3)
cStr4 = mSht.Cells(m, 4)
cStr5 = mSht.Cells(m, 5)
mRst.MoveFirst
Do Until mRst.EOF = True
mStr1 = mRst.Fields("MSGCODE")
mStr2 = mRst.Fields("SERSNO")
mStr3 = mRst.Fields("項次號碼")
mStr4 = mRst.Fields("數量")
mStr5 = mRst.Fields("UNITAMT")
If cStr1 = mRst.Fields("MSGCODE") And cStr2 = mRst.Fields("SERSNO") And cStr3 = mRst.Fields("項次號碼") And cStr4 = mRst.Fields("數量") And cStr5 = mRst.Fields("UNITAMT") Then
mRst.Fields("報單淨重").Value = """" '出現錯誤執行至此即跳至ErrHandle
mRst.Update
Exit Do
End If
mRst.MoveNext
Loop
Next
mRst.Close
mCon.Close
Set mRst = Nothing
Set mCon = Nothing
MsgBox "已完成新增指定簽審文號" & vbCrLf & "報單號碼：" & mAppNo & "至資料庫內"
Exit Function
ErrHandle:
LastErrNumber = Err.Number
MsgBox Err.Number
Debug.Print Err.Number
LastErrDescription = Err.Description
MsgBox Err.Description
Debug.Print Err.Description
If LastErrNumber = 13 Then '如果遇到陣列 ERR 時，直接改由 MDATA1 陣列取出資料內容
mErr mData1, dataPath
End If
Err.Clear
End Function

複製代碼