.ColMask(4) = cellNumeric .ColMask(5) = cellNumeric .ColMask(6) = cellNumeric '如果有大量数据要添加时,可以把Additem方法的第二个参数设为False,全部添加完了 '再调用Refresh方法,这样可以提高速度,如果设置为True,则不需要调用Refresh方法 .Rows = 4 .Refresh End With End SubSub txt_change() Select Case in_col Case 1 '取得物品名称及计算物品金额 Dim in_dm As String Dim In_Sql As Stringin_dm = Trim$(Grid1.Text(in_row, 1))In_Sql = "select * from t_wp where t_dm='" & in_dm & "'" rs.Source = In_Sql rs.Open If rs.RecordCount <> 0 Then rs.MoveFirst If rs.RecordCount = 1 Then Grid1.Text(in_row, 2) = rs("t_wp") Grid1.Text(in_row, 3) = rs("t_dw") End If rs.Close 'MsgBox in_sql'取得最后库存的单价 '当列数为4时,计算6的值 Case 4 Grid1.Text(in_row, 4) = ""
'按先进先出法求出出库单价 Case 5 If Grid1.Text(in_row, 5) <> "" Then In_Sql = "SELECT TOP 1 * From t_rk WHERE (t_dm = '" & Trim$(Grid1.Text(in_row, 1)) & "') AND (t_sl >= " & Trim$(Grid1.Text(in_row, 5)) & ") ORDER BY t_id ASC" rs.Source = In_Sql rs.Open If rs.RecordCount > 0 Then Grid1.Text(in_row, 4) = rs("t_dj")'MsgBox In_SqlElse MsgBox "库存不够!请重新输入" Grid1.Text(in_row, 5) = ""End If rs.Close End If If Grid1.Text(in_row, 4) <> "" Then If Grid1.Text(in_row, 5) <> "" Then Grid1.Text(in_row, 6) = Grid1.Text(in_row, 4) * Grid1.Text(in_row, 5) Else Grid1.Text(in_row, 6) = Grid1.Text(in_row, 4) End If Else Grid1.Text(in_row, 6) = Grid1.Text(in_row, 5) End If End Select End Sub ''出库数据保存过程 Sub DataSave() Dim OutI As Integer Dim StrUser, StrSection, StrUse, StrCertificate, StrDm, StrWp, StrDw, StrDj, StrMony, StrSl As String Dim In_Sql, Tem_Date As StringStrUser = Trim$(OutUser.Text) StrUse = Trim$(OutUse.Text) StrCertificate = Trim$(OutCertificate.Text) StrSection = Trim$(OutSection.Text)If StrUser <> "" And StrSection <> "" And StrUse <> "" And StrCertificate <> "" Then RowCount = Grid1.RowsFor OutI = 1 To RowCount StrDm = Trim$(Grid1.Text(OutI, 1)) StrWp = Trim$(Grid1.Text(OutI, 2)) StrDw = Trim$(Grid1.Text(OutI, 3)) StrDj = Trim$(Grid1.Text(OutI, 4)) StrSl = Trim$(Grid1.Text(OutI, 5)) StrMony = Trim$(Grid1.Text(OutI, 6))'检查该row的物品是否存在及库存是否足够 If StrWp <> "" And StrMony <> "" ThenIn_Sql = "SELECT TOP 1 * From t_rk WHERE (t_dm = '" & StrDm & "') AND (t_sl >= " & StrSl & ") ORDER BY t_id ASC" rs.Source = In_Sql rs.Open If rs.RecordCount > 0 Thendbcn.BeginTrans dbcn.Execute "insert into t_ck_day (t_wp,t_dm,t_dw,t_dj,t_sl,t_mony,t_dh,t_section,t_use,t_user) values('" & StrWp & "','" & StrDm & "','" & StrDw & "'," & StrDj & "," & StrSl & "," & StrMony & ",'" & StrCertificate & "','" & StrSection & "','" & StrUse & "','" & StrUser & "')" dbcn.CommitTrans'计算库存 If rs("t_sl") = StrSl Then dbcn.BeginTrans dbcn.Execute "delete from t_rk where t_id=" & rs("t_id") dbcn.CommitTransMsgBox "已删除" Else Tem_Date = rs("t_sl") - StrSl dbcn.BeginTrans dbcn.Execute "update t_rk set t_sl=" & Tem_Date & "where t_id=" & rs("t_id") dbcn.CommitTrans MsgBox "已更新" End Ifrs.Close Else MsgBox OutI & "行物品库存不够!" rs.Close End If End If Next OutI MsgBox "数据已保存 :) !!" Call gridshow Else MsgBox "请仔细填完表格" End IfEnd Sub不知有没有用啊
Public dbcn As New ADODB.Connection
dbcn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=office"
dbcn.CursorLocation = adUseClient
dbcn.Open
以下是一个窗口里的应用,添加删除的都有啊
Private Sub Form_Load()rs.ActiveConnection = dbcn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockBatchOptimisticCall gridshow
End Sub
Private Sub Grid1_CellChange(Row As Long, Col As Long)
in_row = Row
in_col = Col
Call txt_change
End Sub
Sub gridshow()
Dim i As Integerrs.Source = "select * from t_section"
rs.Open
If rs.RecordCount > 0 Then
rs.MoveFirst
End If
While Not rs.EOF
OutSection.AddItem rs("t_sectionname")
rs.MoveNext
Wend
rs.Close
OutUser.Text = ""
OutCertificate.Text = ""
OutUse.Text = ""
OutSection.Text = ""
With Grid1
.NewFile
.Cols = 6
.Appearance = [3D]
.DisplayFocusRect = False
.BackColor2 = &HE0E0E0
.ColWidth(0) = 20
.ColWidth(1) = 120
.ColWidth(2) = 100
.ColWidth(3) = 70
.ColWidth(4) = 70
.ColWidth(5) = 70
.ColWidth(5) = 70
.Text(0, 1) = "物品代码"
.Text(0, 2) = "物品名称"
.Text(0, 3) = "单位"
.Text(0, 4) = "单价"
.Text(0, 5) = "数量"
.Text(0, 6) = "金额"
For i = 1 To 5
.CellAlignment(0, i) = cellCenterCenter '居中对齐
Next i
.ColType(1) = cellTextBox '文本框
.ColType(2) = cellTextBox '组合框
.ColType(3) = cellTextBox '文本框
.ColType(4) = cellTextBox '复选框
.ColType(5) = cellTextBox
.ColType(6) = cellTextBox
.ColMask(4) = cellNumeric
.ColMask(5) = cellNumeric
.ColMask(6) = cellNumeric
'如果有大量数据要添加时,可以把Additem方法的第二个参数设为False,全部添加完了
'再调用Refresh方法,这样可以提高速度,如果设置为True,则不需要调用Refresh方法
.Rows = 4
.Refresh
End With
End SubSub txt_change()
Select Case in_col
Case 1
'取得物品名称及计算物品金额
Dim in_dm As String
Dim In_Sql As Stringin_dm = Trim$(Grid1.Text(in_row, 1))In_Sql = "select * from t_wp where t_dm='" & in_dm & "'"
rs.Source = In_Sql
rs.Open
If rs.RecordCount <> 0 Then rs.MoveFirst
If rs.RecordCount = 1 Then
Grid1.Text(in_row, 2) = rs("t_wp")
Grid1.Text(in_row, 3) = rs("t_dw")
End If
rs.Close
'MsgBox in_sql'取得最后库存的单价
'当列数为4时,计算6的值
Case 4
Grid1.Text(in_row, 4) = ""
'按先进先出法求出出库单价
Case 5
If Grid1.Text(in_row, 5) <> "" Then
In_Sql = "SELECT TOP 1 * From t_rk WHERE (t_dm = '" & Trim$(Grid1.Text(in_row, 1)) & "') AND (t_sl >= " & Trim$(Grid1.Text(in_row, 5)) & ") ORDER BY t_id ASC"
rs.Source = In_Sql
rs.Open
If rs.RecordCount > 0 Then
Grid1.Text(in_row, 4) = rs("t_dj")'MsgBox In_SqlElse
MsgBox "库存不够!请重新输入"
Grid1.Text(in_row, 5) = ""End If
rs.Close
End If If Grid1.Text(in_row, 4) <> "" Then
If Grid1.Text(in_row, 5) <> "" Then
Grid1.Text(in_row, 6) = Grid1.Text(in_row, 4) * Grid1.Text(in_row, 5)
Else
Grid1.Text(in_row, 6) = Grid1.Text(in_row, 4)
End If
Else
Grid1.Text(in_row, 6) = Grid1.Text(in_row, 5)
End If
End Select
End Sub
''出库数据保存过程
Sub DataSave()
Dim OutI As Integer
Dim StrUser, StrSection, StrUse, StrCertificate, StrDm, StrWp, StrDw, StrDj, StrMony, StrSl As String
Dim In_Sql, Tem_Date As StringStrUser = Trim$(OutUser.Text)
StrUse = Trim$(OutUse.Text)
StrCertificate = Trim$(OutCertificate.Text)
StrSection = Trim$(OutSection.Text)If StrUser <> "" And StrSection <> "" And StrUse <> "" And StrCertificate <> "" Then
RowCount = Grid1.RowsFor OutI = 1 To RowCount
StrDm = Trim$(Grid1.Text(OutI, 1))
StrWp = Trim$(Grid1.Text(OutI, 2))
StrDw = Trim$(Grid1.Text(OutI, 3))
StrDj = Trim$(Grid1.Text(OutI, 4))
StrSl = Trim$(Grid1.Text(OutI, 5))
StrMony = Trim$(Grid1.Text(OutI, 6))'检查该row的物品是否存在及库存是否足够
If StrWp <> "" And StrMony <> "" ThenIn_Sql = "SELECT TOP 1 * From t_rk WHERE (t_dm = '" & StrDm & "') AND (t_sl >= " & StrSl & ") ORDER BY t_id ASC"
rs.Source = In_Sql
rs.Open
If rs.RecordCount > 0 Thendbcn.BeginTrans
dbcn.Execute "insert into t_ck_day (t_wp,t_dm,t_dw,t_dj,t_sl,t_mony,t_dh,t_section,t_use,t_user) values('" & StrWp & "','" & StrDm & "','" & StrDw & "'," & StrDj & "," & StrSl & "," & StrMony & ",'" & StrCertificate & "','" & StrSection & "','" & StrUse & "','" & StrUser & "')"
dbcn.CommitTrans'计算库存
If rs("t_sl") = StrSl Then
dbcn.BeginTrans
dbcn.Execute "delete from t_rk where t_id=" & rs("t_id")
dbcn.CommitTransMsgBox "已删除"
Else
Tem_Date = rs("t_sl") - StrSl
dbcn.BeginTrans
dbcn.Execute "update t_rk set t_sl=" & Tem_Date & "where t_id=" & rs("t_id")
dbcn.CommitTrans
MsgBox "已更新"
End Ifrs.Close
Else
MsgBox OutI & "行物品库存不够!"
rs.Close
End If
End If
Next OutI
MsgBox "数据已保存 :) !!"
Call gridshow
Else
MsgBox "请仔细填完表格"
End IfEnd Sub不知有没有用啊