Dim txtSQLlingru As String
Dim txtSQLkucun As String
Dim txtSQLruku As String
Dim txtSQLname As String
Dim mrclingru As ADODB.Recordset
Dim mrckucun As ADODB.Recordset
Dim mrcruku As ADODB.Recordset
Dim mrcname As ADODB.Recordset
Dim MsgText As String
'定义
txtSQLlingru = "select * from lingru"
Set mrclingru = ExecuteSQL(txtSQLlingru, MsgText)
'打开
If mrclingru.BOF Then
Text1.SetFocus
Else
mrclingru.MoveFirst
Do While Not mrclingru.EOF
r = Trim(mrclingru.Fields(1))
txtSQLkucun = "select * from kucun"
Set mrckucun = ExecuteSQL(txtSQLkucun, MsgText)
If mrckucun.BOF Then
kc = 0
Else
mrckucun.MoveLast
kc = Trim(mrckucun.Fields(0))
End If
mrckucun.Close
txtSQLname = "select * from kucun where name='" & r & "'"
Set mrcname = ExecuteSQL(txtSQLname, MsgText)
If mrcname.EOF Then
txtSQLkucun = "select * from kucun"
Set mrckucun = ExecuteSQL(txtSQLkucun, MsgText)
If mrckucun.BOF Then
mrckucun.AddNew
mrckucun.Fields(0) = Trim(kc + 1)
mrckucun.Fields(1) = Trim(mrclingru.Fields(1))
mrckucun.Fields(2) = Trim(mrclingru.Fields(2))
mrckucun.Fields(3) = Trim(mrclingru.Fields(4))
mrckucun.Update
Else
mrckucun.MoveLast
mrckucun.AddNew
mrckucun.Fields(0) = Trim(kc + 1)
mrckucun.Fields(1) = Trim(mrclingru.Fields(1))
mrckucun.Fields(2) = Trim(mrclingru.Fields(2))
mrckucun.Fields(3) = Trim(mrclingru.Fields(4))
mrckucun.Update
'mrckucun.MoveNext
End If
mrckucun.Close
Else
txtSQLkucun = "select * from kucun"
Set mrckucun = ExecuteSQL(txtSQLkucun, MsgText)
If Trim(mrckucun.Fields(1)) = r Then
mrckucun.Fields(2) = Val(Trim(mrckucun.Fields(2)) + Val(Trim(mrclingru.Fields(2))))
mrckucun.Update
End If
mrckucun.Close
mrcname.Close
End If
mrclingru.Update
mrclingru.MoveNext
Loop
End If
mrclingru.Close
'导入kucun表
txtSQLlingru = "select * from lingru"
Set mrclingru = ExecuteSQL(txtSQLlingru, MsgText)
If mrclingru.BOF Then
Text1.SetFocus
Else
mrclingru.MoveFirst
Do While Not mrclingru.EOF
r = Trim(mrclingru.Fields(1))
txtSQLruku = "select * from ruku"
Set mrcruku = ExecuteSQL(txtSQLruku, MsgText)
If mrcruku.BOF Then
kc = 0
Else
mrcruku.MoveLast
kc = Trim(mrcruku.Fields(0))
End If
mrcruku.Close
'找出id
txtSQLname = "select * from ruku where name='" & r & "'"
Set mrcname = ExecuteSQL(txtSQLname, MsgText)
If mrcname.EOF Then
txtSQLruku = "select * from ruku"
Set mrcruku = ExecuteSQL(txtSQLruku, MsgText)
If mrcruku.BOF Then
mrcruku.AddNew
mrcruku.Fields(0) = Trim(kc + 1)
mrcruku.Fields(1) = Trim(mrclingru.Fields(1))
mrcruku.Fields(2) = Trim(mrclingru.Fields(2))
mrcruku.Fields(3) = Trim(mrclingru.Fields(3))
mrcruku.Fields(4) = Trim(mrclingru.Fields(4))
mrcruku.Update
Else
mrcruku.MoveLast
mrcruku.AddNew
mrcruku.Fields(0) = Trim(kc + 1)
mrcruku.Fields(1) = Trim(mrclingru.Fields(1))
mrcruku.Fields(2) = Trim(mrclingru.Fields(2))
mrcruku.Fields(3) = Trim(mrclingru.Fields(3))
mrcruku.Fields(4) = Trim(mrclingru.Fields(4))
mrcruku.Update
mrcruku.MoveNext
End If
mrcruku.Close
Else
txtSQLruku = "select * from ruku"
Set mrcruku = ExecuteSQL(txtSQLruku, MsgText)
If Trim(mrcruku.Fields(1)) = Trim(r) Then
mrcruku.Fields(2) = Val(Trim(mrcruku.Fields(2))) + Val(Trim(mrclingru.Fields(2)))
mrcruku.Fields(3) = Val(Trim(mrcruku.Fields(3))) + Val(Trim(mrclingru.Fields(3)))
mrcruku.Update
End If
mrcruku.Close
End If
'mrclingru.Update
mrclingru.MoveNext
Loop
mrcname.Close
End If
mrclingru.Close
'导入ruku表
txtSQLlingru = "select * from lingru"
Set mrclingru = ExecuteSQL(txtSQLlingru, MsgText)
If mrclingru.EOF Then
Text1.SetFocus
Else
mrclingru.MoveFirst
Do While Not mrclingru.EOF
mrclingru.Delete
mrclingru.Update
mrclingru.MoveNext
Loop
End If
'释放lingru表 mrclingru.Close
Text1.Text = " "
Text2.Text = " "
Text3.Text = " "Unload Me
Public Function ExecuteSQL(ByVal SQL As String, MsgString As String) As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
'rst.CursorLocation = adUseClient
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & "query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & " 条记录 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function
目的:
lingru(临时表)的数据和kucun(库存表)以及ruku(入库)的数据进行处理
但处理第一行可以,
清高手指点!!!!!急急!!!
在线等!!!
Dim txtSQLkucun As String
Dim txtSQLruku As String
Dim txtSQLname As String
Dim mrclingru As ADODB.Recordset
Dim mrckucun As ADODB.Recordset
Dim mrcruku As ADODB.Recordset
Dim mrcname As ADODB.Recordset
Dim MsgText As String
'定义
txtSQLlingru = "select * from lingru"
Set mrclingru = ExecuteSQL(txtSQLlingru, MsgText)
'打开
If mrclingru.BOF Then
Text1.SetFocus
Else
mrclingru.MoveFirst
Do While Not mrclingru.EOF
r = Trim(mrclingru.Fields(1))
txtSQLkucun = "select * from kucun"
Set mrckucun = ExecuteSQL(txtSQLkucun, MsgText)
If mrckucun.BOF Then
kc = 0
Else
mrckucun.MoveLast
kc = Trim(mrckucun.Fields(0))
End If
mrckucun.Close
txtSQLname = "select * from kucun where name='" & r & "'"
Set mrcname = ExecuteSQL(txtSQLname, MsgText)
If mrcname.EOF Then
txtSQLkucun = "select * from kucun"
Set mrckucun = ExecuteSQL(txtSQLkucun, MsgText)
If mrckucun.BOF Then
mrckucun.AddNew
mrckucun.Fields(0) = Trim(kc + 1)
mrckucun.Fields(1) = Trim(mrclingru.Fields(1))
mrckucun.Fields(2) = Trim(mrclingru.Fields(2))
mrckucun.Fields(3) = Trim(mrclingru.Fields(4))
mrckucun.Update
Else
mrckucun.MoveLast
mrckucun.AddNew
mrckucun.Fields(0) = Trim(kc + 1)
mrckucun.Fields(1) = Trim(mrclingru.Fields(1))
mrckucun.Fields(2) = Trim(mrclingru.Fields(2))
mrckucun.Fields(3) = Trim(mrclingru.Fields(4))
mrckucun.Update
'mrckucun.MoveNext
End If
mrckucun.Close
Else
txtSQLkucun = "select * from kucun"
Set mrckucun = ExecuteSQL(txtSQLkucun, MsgText)
If Trim(mrckucun.Fields(1)) = r Then
mrckucun.Fields(2) = Val(Trim(mrckucun.Fields(2)) + Val(Trim(mrclingru.Fields(2))))
mrckucun.Update
End If
mrckucun.Close
mrcname.Close
End If
mrclingru.Update
mrclingru.MoveNext
Loop
End If
mrclingru.Close
'导入kucun表
txtSQLlingru = "select * from lingru"
Set mrclingru = ExecuteSQL(txtSQLlingru, MsgText)
If mrclingru.BOF Then
Text1.SetFocus
Else
mrclingru.MoveFirst
Do While Not mrclingru.EOF
r = Trim(mrclingru.Fields(1))
txtSQLruku = "select * from ruku"
Set mrcruku = ExecuteSQL(txtSQLruku, MsgText)
If mrcruku.BOF Then
kc = 0
Else
mrcruku.MoveLast
kc = Trim(mrcruku.Fields(0))
End If
mrcruku.Close
'找出id
txtSQLname = "select * from ruku where name='" & r & "'"
Set mrcname = ExecuteSQL(txtSQLname, MsgText)
If mrcname.EOF Then
txtSQLruku = "select * from ruku"
Set mrcruku = ExecuteSQL(txtSQLruku, MsgText)
If mrcruku.BOF Then
mrcruku.AddNew
mrcruku.Fields(0) = Trim(kc + 1)
mrcruku.Fields(1) = Trim(mrclingru.Fields(1))
mrcruku.Fields(2) = Trim(mrclingru.Fields(2))
mrcruku.Fields(3) = Trim(mrclingru.Fields(3))
mrcruku.Fields(4) = Trim(mrclingru.Fields(4))
mrcruku.Update
Else
mrcruku.MoveLast
mrcruku.AddNew
mrcruku.Fields(0) = Trim(kc + 1)
mrcruku.Fields(1) = Trim(mrclingru.Fields(1))
mrcruku.Fields(2) = Trim(mrclingru.Fields(2))
mrcruku.Fields(3) = Trim(mrclingru.Fields(3))
mrcruku.Fields(4) = Trim(mrclingru.Fields(4))
mrcruku.Update
mrcruku.MoveNext
End If
mrcruku.Close
Else
txtSQLruku = "select * from ruku"
Set mrcruku = ExecuteSQL(txtSQLruku, MsgText)
If Trim(mrcruku.Fields(1)) = Trim(r) Then
mrcruku.Fields(2) = Val(Trim(mrcruku.Fields(2))) + Val(Trim(mrclingru.Fields(2)))
mrcruku.Fields(3) = Val(Trim(mrcruku.Fields(3))) + Val(Trim(mrclingru.Fields(3)))
mrcruku.Update
End If
mrcruku.Close
End If
'mrclingru.Update
mrclingru.MoveNext
Loop
mrcname.Close
End If
mrclingru.Close
'导入ruku表
txtSQLlingru = "select * from lingru"
Set mrclingru = ExecuteSQL(txtSQLlingru, MsgText)
If mrclingru.EOF Then
Text1.SetFocus
Else
mrclingru.MoveFirst
Do While Not mrclingru.EOF
mrclingru.Delete
mrclingru.Update
mrclingru.MoveNext
Loop
End If
'释放lingru表 mrclingru.Close
Text1.Text = " "
Text2.Text = " "
Text3.Text = " "Unload Me
Public Function ExecuteSQL(ByVal SQL As String, MsgString As String) As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
'rst.CursorLocation = adUseClient
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & "query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & " 条记录 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function
目的:
lingru(临时表)的数据和kucun(库存表)以及ruku(入库)的数据进行处理
但处理第一行可以,
清高手指点!!!!!急急!!!
在线等!!!
r
在公共模块里定义了!