📄 main_htgl_spinf.frm
字号:
TabIndex = 30
Top = 180
Width = 960
End
Begin VB.CommandButton ComSave
BackColor = &H00C0C0C0&
Caption = "保存[&S]"
Height = 360
Left = 3885
Style = 1 'Graphical
TabIndex = 29
Top = 180
Width = 960
End
End
End
Attribute VB_Name = "main_htgl_spinf"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim i, m As Integer '定义整型变量
Dim rs1 As New ADODB.Recordset '定义数据集对象
Private nod As Node
Public Sub tree_change() '定义添加树状列表的函数
Dim key, text, bh As String
Dim nod As Node
Sql = "select * from 商品基础信息表 order by 商品编号"
Call FunAdo(Adodc1, Sql)
Adodc1.Refresh
If Adodc1.Recordset.RecordCount > 0 Then
Adodc1.Recordset.MoveFirst
Do While Adodc1.Recordset.EOF = False
If Len(Trim(Adodc1.Recordset.Fields("商品编号"))) = 2 Then
key = Trim(Adodc1.Recordset.Fields("本级名称")) & "*"
text = "(" & Trim(Adodc1.Recordset.Fields("商品编号")) & ")" & Trim(Adodc1.Recordset.Fields("本级名称"))
Set Node1 = TreeView1.Nodes.Add(, , key, text, 1) '设置TreeView1控件的节点 文本 和 图标（另外：1 为ImageList控件图标的索引值）
End If
If Len(Trim(Adodc1.Recordset.Fields("商品编号"))) = 4 Then
key = Trim(Adodc1.Recordset.Fields("本级名称")) & "*"
text = "(" & Trim(Adodc1.Recordset.Fields("商品编号")) & ")" & Trim(Adodc1.Recordset.Fields("本级名称"))
Set Node2 = TreeView1.Nodes.Add(Node1.Index, tvwChild, key, text, 2) '设置TreeView1控件的节点 文本 和 图标（另外：1 为ImageList控件图标的索引值）
End If
If Len(Trim(Adodc1.Recordset.Fields("商品编号"))) = 7 Then
key = Trim(Adodc1.Recordset.Fields("本级名称")) & "*"
text = "(" & Trim(Adodc1.Recordset.Fields("商品编号")) & ")" & Trim(Adodc1.Recordset.Fields("本级名称"))
Set Node3 = TreeView1.Nodes.Add(Node2.Index, tvwChild, key, text, 3) '设置TreeView1控件的节点 文本 和 图标（另外：1 为ImageList控件图标的索引值）
End If
Adodc1.Recordset.MoveNext
Loop
End If
End Sub
Private Sub DataGrid1_Click()
On Error Resume Next
Text1(9).text = Trim(Adodc3.Recordset.Fields(1))
Text1(10).text = Trim(Adodc3.Recordset.Fields(3))
Text1(11).text = Trim(Adodc3.Recordset.Fields(6))
Text1(12).text = Trim(Adodc3.Recordset.Fields(8))
DataGrid1.Visible = False
ComSave.SetFocus
End Sub
Private Sub DataGrid1_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
On Error Resume Next
Text1(9).text = Trim(Adodc3.Recordset.Fields(1))
Text1(10).text = Trim(Adodc3.Recordset.Fields(3))
Text1(11).text = Trim(Adodc3.Recordset.Fields(6))
Text1(12).text = Trim(Adodc3.Recordset.Fields(8))
DataGrid1.Visible = False
ComSave.SetFocus
End If
End Sub
Private Sub Form_Activate()
'添加商品级别列表
Combo1.AddItem ("1级"): Combo1.AddItem ("2级"): Combo1.AddItem ("3级")
Combo1.ListIndex = 0
'打开连接
rs1.Open "select * from 商品基础信息表 order by 商品级别,商品编号", cnn, adOpenKeyset, adLockOptimistic
If rs1.RecordCount > 0 Then
For i = 1 To 12
If rs1.Fields(i) <> "" Then Text1(i).text = rs1.Fields(i)
Text1(i).Enabled = False
Next i
End If
rs1.Close '关闭数据集对象
Call tree_change '调用函数
ComSave.Enabled = False
Me.Caption = Me.Caption & " " & frm_main.St1.Panels(3).text
End Sub
Private Sub Form_Load()
Dim sqlStr1 As String
Sql = "select * From 商品基础信息表 "
sqlStr = "select * From 商品基础信息表 "
sqlStr1 = "select * from 供应商信息表"
Call FunAdo(Adodc1, Sql)
Call FunAdo(Adodc2, sqlStr)
Call FunAdo(Adodc3, sqlStr1)
End Sub
Private Sub Form_Unload(Cancel As Integer)
frm_main.Enabled = True
End Sub
Private Sub Text1_KeyDown(Index As Integer, KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn And Index < 11 Then Text1(Index + 1).SetFocus '回车获得焦点
If KeyCode = vbKeyReturn And Index = 11 Then ComSave.SetFocus
If KeyCode = vbKeyUp And Index > 1 Then Text1(Index - 1).SetFocus
If KeyCode = 34 Then
DataGrid1.Visible = True
DataGrid1.SetFocus
End If
End Sub
Private Sub Text1_KeyPress(Index As Integer, KeyAscii As Integer)
If Text1(9).text <> "" And Text1(9).Enabled = True Then
Sql = "select * from 供应商信息表 where 供应商全称 like '" & Trim(Text1(9).text) & "%'"
Call FunAdo(Adodc3, Sql)
Adodc3.Refresh
If Adodc3.Recordset.RecordCount > 0 Then
DataGrid1.Visible = True
End If
End If
End Sub
Private Sub TreeView1_NodeClick(ByVal Node As MSComctlLib.Node)
Text1(3).text = Left(TreeView1.SelectedItem.key, Len(TreeView1.SelectedItem.key) - 1) '赋值给text1(3).text
Sql = "select * from 商品基础信息表 where 本级名称='" + Trim(Text1(3).text) + "'order by 商品编号"
Call FunAdo(Adodc1, Sql)
Adodc1.Refresh
If Adodc1.Recordset.RecordCount <> 0 Then
For i = 1 To 12
Text1(i).text = ""
If Adodc1.Recordset.Fields(i) <> "" Then Text1(i).text = Trim(Adodc1.Recordset.Fields(i))
Next i
If Trim(Adodc1.Recordset.Fields("商品级别")) = "1级" Then
Combo1.Clear
'添加商品级别列表
Combo1.AddItem ("1级"): Combo1.AddItem ("2级")
Combo1.ListIndex = 0
End If
If Trim(Adodc1.Recordset.Fields("商品级别")) = "2级" Then
Combo1.Clear
'添加商品级别列表
Combo1.AddItem ("1级"): Combo1.AddItem ("2级"): Combo1.AddItem ("3级")
Combo1.ListIndex = 0
End If
Combo1.text = Trim(Adodc1.Recordset.Fields("商品级别")) '赋值给combo1.text
End If
End Sub
Private Sub ComAdd_Click()
Dim bh As Integer '定义一个整型变量
m = 0
If Combo1.text = "1级" Then
Sql = "select * from 商品基础信息表 where 商品级别='" + Combo1.text + "'order by 商品级别,商品编号"
Call FunAdo(Adodc1, Sql)
Adodc1.Refresh
If Adodc1.Recordset.RecordCount > 0 Then
If Not Adodc1.Recordset.EOF Then Adodc1.Recordset.MoveLast
If Adodc1.Recordset.Fields("本级编号") <> "" Then
bh = Trim(Adodc1.Recordset.Fields("本级编号")) + 1
Text1(1).text = Format(bh, "00")
End If
Else
Text1(1).text = "01"
End If
Text1(2).text = Text1(1).text
End If
If Combo1.text = "2级" Then
Sql = "select * from 商品基础信息表 where 商品级别='" + Combo1.text + "'and 商品编号 Like '" + Trim(Text1(2).text) + "'+'_____'order by 商品编号"
Call FunAdo(Adodc1, Sql)
Adodc1.Refresh
If Adodc1.Recordset.RecordCount > 0 Then
If Not Adodc1.Recordset.EOF Then Adodc1.Recordset.MoveLast
If Adodc1.Recordset.Fields("本级编号") <> "" Then
bh = Trim(Adodc1.Recordset.Fields("本级编号")) + 1
Text1(1).text = Format(bh, "00")
Text1(2).text = Left(Trim(Adodc1.Recordset.Fields("商品编号")), 2) & Text1(1).text
End If
Else
Text1(1).text = "01"
Text1(2).text = Trim(Text1(2).text) & "01"
End If
End If
If Combo1.text = "3级" Then
Sql = "select * from 商品基础信息表 where 商品级别='" + Combo1.text + "'and 商品编号 Like '" + Trim(Text1(2).text) + "'+'_____'order by 商品编号"
Call FunAdo(Adodc1, Sql)
Adodc1.Refresh
If Adodc1.Recordset.RecordCount > 0 Then
If Not Adodc1.Recordset.EOF Then Adodc1.Recordset.MoveLast
If Adodc1.Recordset.Fields("本级编号") <> "" Then
bh = Trim(Adodc1.Recordset.Fields("本级编号")) + 1
'使用Format函数将数据格式为“0.00”格式
Text1(1).text = Format(bh, "000")
Text1(2).text = Left(Trim(Adodc1.Recordset.Fields("商品编号")), 4) & Text1(1).text
End If
Else
Text1(1).text = "001"
Text1(2).text = Trim(Text1(2).text) & "001"
End If
End If
'清空输入框中的内容
For i = 3 To 12
Text1(i).Enabled = True
Text1(i).text = ""
Next i
'设置控件状态
ComSave.Enabled = True: ComEsc.Enabled = True: ComAdd.Enabled = False
ComModify.Enabled = False: ComDelete.Enabled = False
Text1(3).SetFocus
End Sub
Private Sub ComModify_Click() '修改商品信息
m = 1
If Adodc1.Recordset.RecordCount <> 0 Then
For i = 3 To 12
Text1(i).Enabled = True
Next i
ComSave.Enabled = True: ComEsc.Enabled = True: ComAdd.Enabled = False
ComModify.Enabled = False: ComDelete.Enabled = False
Else
MsgBox ("没有要修改的数据!")
End If
End Sub
Private Sub ComDelete_Click() '删除商品信息
If Adodc1.Recordset.RecordCount > 0 Then
a = MsgBox("您确实要删除这条数据吗?", vbYesNo + vbQuestion)
If a = vbYes Then
If Len(Trim(Adodc1.Recordset.Fields(2).Value)) = 2 Then
sqlStr = "select * from 商品基础信息表 where 商品编号 like '" & Trim(Adodc1.Recordset.Fields(1)) & "%'"
Call FunAdo(Adodc2, sqlStr)
Adodc2.Refresh
If Adodc2.Recordset.RecordCount > 0 Then
For i = 0 To Adodc2.Recordset.RecordCount - 1
Adodc2.Recordset.Delete
Adodc2.Recordset.MoveNext
Next i
End If
End If
If Len(Trim(Adodc1.Recordset.Fields(2).Value)) = 4 Then
sqlStr = "select * from 商品基础信息表 where 商品编号 like '" & Trim(Adodc1.Recordset.Fields(2)) & "%'"
Call FunAdo(Adodc2, sqlStr)
Adodc2.Refresh
If Adodc2.Recordset.RecordCount > 0 Then
For i = 0 To Adodc2.Recordset.RecordCount - 1
Adodc2.Recordset.Delete
Adodc2.Recordset.MoveNext
Next i
End If
End If
If Len(Trim(Adodc1.Recordset.Fields(2).Value)) = 7 Then
sqlStr = "select * from 商品基础信息表 where 商品编号 like '" & Trim(Adodc1.Recordset.Fields(2)) & "%'"
Call FunAdo(Adodc2, sqlStr)
Adodc2.Refresh
If Adodc2.Recordset.RecordCount > 0 Then
Adodc2.Recordset.Delete
End If
End If
TreeView1.Nodes.Clear
Call tree_change '调用函数
Sql = "select * from 商品基础信息表"
Call FunAdo(Adodc1, Sql)
Adodc1.Refresh
If Adodc1.Recordset.RecordCount <> 0 Then
For i = 1 To 12
If Adodc1.Recordset.Fields(i) <> "" Then Text1(i).text = Adodc1.Recordset.Fields(i)
Next i
Combo1.text = Adodc1.Recordset.Fields("商品级别")
End If
ComSave.Enabled = False: ComEsc.Enabled = False: ComAdd.Enabled = True
ComModify.Enabled = True: ComDelete.Enabled = True
End If
Else
MsgBox ("没有要删除的数据!")
End If
End Sub
Private Sub ComSave_Click() '保存商品信息
If m = 0 Then
Set rs1 = New ADODB.Recordset
rs1.Open "select * from 商品基础信息表 where 本级名称='" & Trim(Text1(3).text) & "' and 商品级别='" & Trim(Combo1.text) & "'", cnn, adOpenStatic, adLockReadOnly
If rs1.RecordCount > 0 Then
MsgBox "本级名称不能重复！", vbOKOnly + vbCritical
Text1(3).text = ""
Text1(3).SetFocus
rs1.Close
Exit Sub
Else: rs1.Close
End If
rs1.Open "select * from 商品基础信息表 where 商品编号='" & Trim(Text1(2).text) & "'order by 商品编号", cnn, adOpenKeyset, adLockOptimistic
If rs1.RecordCount > 0 Then
Dim a As String '定义一个字符串变量
a = MsgBox("您确实要修改这条数据吗?", vbYesNo)
If a = vbYes Then
For i = 1 To 6
If Text1(1).text <> "" Then rs1.Fields(i) = Trim(Text1(i).text) '赋值给rs1.fields(i)
Text1(i).Enabled = False
Next i
rs1.Fields(7) = Val(Trim(Text1(7).text))
For i = 8 To 12
If Text1(1).text <> "" Then rs1.Fields(i) = Trim(Text1(i).text)
Text1(i).Enabled = False
Next i
rs1.Fields("商品级别") = Combo1.text
rs1.Update
Adodc1.Refresh
End If
Else
If Text1(1).text <> "" And Text1(2).text <> "" Then
'添加商品信息
rs1.AddNew
For i = 1 To 6
If Text1(1).text <> "" Then rs1.Fields(i) = Trim(Text1(i).text)
Text1(i).Enabled = False
Next i
rs1.Fields(7) = Val(Trim(Text1(7).text))
For i = 8 To 12
If Text1(1).text <> "" Then rs1.Fields(i) = Trim(Text1(i).text)
Text1(i).Enabled = False
Next i
rs1.Fields("商品级别") = Combo1.text
rs1.Update '更新数据库
Adodc1.Refresh
Else
MsgBox "请填写完整的信息"
End If
End If
Else
rs1.Open "select * from 商品基础信息表 where 商品编号='" & Trim(Text1(2).text) & "'order by 商品编号", cnn, adOpenKeyset, adLockOptimistic
If rs1.RecordCount > 0 Then
a = MsgBox("您确实要修改这条数据吗?", vbYesNo)
If a = vbYes Then
For i = 1 To 6
If Text1(1).text <> "" Then rs1.Fields(i) = Trim(Text1(i).text) '赋值给rs1.fields(i)
Text1(i).Enabled = False
Next i
rs1.Fields(7) = Val(Trim(Text1(7).text))
For i = 8 To 12
If Text1(1).text <> "" Then rs1.Fields(i) = Trim(Text1(i).text)
Text1(i).Enabled = False
Next i
rs1.Fields("商品级别") = Combo1.text
rs1.Update
Adodc1.Refresh
End If
Else
If Text1(1).text <> "" And Text1(2).text <> "" Then
'添加商品信息
rs1.AddNew
For i = 1 To 6
If Text1(1).text <> "" Then rs1.Fields(i) = Trim(Text1(i).text)
Text1(i).Enabled = False
Next i
rs1.Fields(7) = Val(Trim(Text1(7).text))
For i = 8 To 12
If Text1(1).text <> "" Then rs1.Fields(i) = Trim(Text1(i).text)
Text1(i).Enabled = False
Next i
rs1.Fields("商品级别") = Combo1.text
rs1.Update '更新数据库
Adodc1.Refresh
Else
MsgBox "请填写完整的信息"
End If
End If
End If
rs1.Close '关闭数据集对象
TreeView1.Nodes.Clear
Call tree_change '调用函数
'设置控件状态
ComSave.Enabled = False: ComEsc.Enabled = False: ComAdd.Enabled = True
ComModify.Enabled = True: ComDelete.Enabled = True
End Sub
Private Sub ComEsc_Click() '取消操作
If Not Adodc1.Recordset.BOF Then
Adodc1.Recordset.MoveFirst
For i = 1 To 12
If Adodc1.Recordset.Fields(i) <> "" Then Text1(i).text = Adodc1.Recordset.Fields(i)
Next i
Combo1.text = Adodc1.Recordset.Fields("商品级别")
End If
For i = 3 To 12
Text1(i).Enabled = False
Next i
ComSave.Enabled = False: ComEsc.Enabled = False: ComAdd.Enabled = True
ComModify.Enabled = True: ComDelete.Enabled = True: DataGrid1.Visible = False
End Sub
Private Sub ComExit_Click()
frm_main.Enabled = True
Unload Me
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -