修改代码 Private Sub Command1_Click() '确认修改 For I = 0 To 8 If Text1(I).Text = "" Then MsgBox "你没有输入第" & I + 1 & "个文本框的数据,请输入!", 16, "错误提示!" Exit Sub End If Next I If IsDate(Text1(3).Text) = False Then MsgBox "你输入的生产时间不是日期,请重新输入!", 16, "错误提示!" Exit Sub End If If IsDate(Text1(4).Text) = False Then MsgBox "你输入的销售时间不是日期,请重新输入!", 16, "错误提示!" Exit Sub End If If IsNumeric(Text1(7).Text) = False Then MsgBox "你输入的数量不是数字,请重新输入!", 16, "错误提示!" Exit Sub End If Adodc1.RecordSource = "Select * From 销售表 Where ID=" & AID & "" Adodc1.Refresh Adodc1.Recordset.Fields("名称") = Text1(0).Text Adodc1.Recordset.Fields("型号") = Text1(1).Text Adodc1.Recordset.Fields("批次") = Text1(2).Text Adodc1.Recordset.Fields("生产时间") = Text1(3).Text Adodc1.Recordset.Fields("销售时间") = Text1(4).Text Adodc1.Recordset.Fields("销售人员") = Text1(5).Text Adodc1.Recordset.Fields("购买厂家") = Text1(6).Text Adodc1.Recordset.Fields("数量") = Val(Text1(7).Text) 'Adodc1.Recordset.Fields("编码") = Text1(8).Text '商品编码不能修改 Adodc1.Recordset.Update MsgBox "祝贺你!这个记录已经修改!", 64, "修改成功!" Unload Me MSH控件操作实例.Show End Sub 添加代码 Private Sub Command2_Click() '确认添加 For I = 0 To 8 If Text1(I).Text = "" Then MsgBox "你没有输入第" & I + 1 & "个文本框的数据,请输入!", 16, "错误提示!" Exit Sub End If Next I If IsDate(Text1(3).Text) = False Then MsgBox "你输入的生产时间不是日期,请重新输入!", 16, "错误提示!" Exit Sub End If If IsDate(Text1(4).Text) = False Then MsgBox "你输入的销售时间不是日期,请重新输入!", 16, "错误提示!" Exit Sub End If If IsNumeric(Text1(7).Text) = False Then MsgBox "你输入的数量不是数字,请重新输入!", 16, "错误提示!" Exit Sub End If Adodc1.RecordSource = "Select * From 销售表" Adodc1.Refresh Adodc1.Recordset.AddNew Adodc1.Recordset.Fields("名称") = Text1(0).Text Adodc1.Recordset.Fields("型号") = Text1(1).Text Adodc1.Recordset.Fields("批次") = Text1(2).Text Adodc1.Recordset.Fields("生产时间") = Text1(3).Text Adodc1.Recordset.Fields("销售时间") = Text1(4).Text Adodc1.Recordset.Fields("销售人员") = Text1(5).Text Adodc1.Recordset.Fields("购买厂家") = Text1(6).Text Adodc1.Recordset.Fields("数量") = Val(Text1(7).Text) Adodc1.Recordset.Fields("编码") = Text1(8).Text Adodc1.Recordset.Fields("编码0") = Val(Label1.Caption) Adodc1.Recordset.Update MsgBox "祝贺你!一条新的记录已经存储!", 64, "添加成功!" Unload Me MSH控件操作实例.Show End Sub 删除代码 Private Sub Command3_Click() '确认删除 If AID = 0 Then MsgBox "你没有选择要删除的记录,请点击上面列表中需要删除的记录!", 16, "错误提示!" Exit Sub End If If MsgBox("你确实要删除这个记录吗?", 33, "删除确认!") = 1 Then Adodc1.RecordSource = "Select * From 销售表 Where ID=" & AID & "" Adodc1.Refresh Adodc1.Recordset.Delete MsgBox "祝贺你!这个记录已经删除!", 64, "删除成功!" Unload Me MSH控件操作实例.Show End If End Sub 启动代码Private Sub Form_Load() For I = 0 To 8 Text1(I).Text = "" Next I Label1.Caption = "" Adodc1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & App.Path & "\新建数据库.accdb;Jet OLEDB:Database Password=;" Adodc1.RecordSource = "Select * From 销售表" Adodc1.Refresh Dim SST As Integer SST = Adodc1.Recordset.RecordCount MSHFlexGrid1.Clear MSHFlexGrid1.Visible = True With MSHFlexGrid1 .Cols = 10 .Rows = SST + 1 .TextMatrix(0, 0) = "ID" .TextMatrix(0, 1) = "名称" .TextMatrix(0, 2) = "型号" .TextMatrix(0, 3) = "批次" .TextMatrix(0, 4) = "生产时间" .TextMatrix(0, 5) = "销售时间" .TextMatrix(0, 6) = "销售人员" .TextMatrix(0, 7) = "购买厂家" .TextMatrix(0, 8) = "数量" .TextMatrix(0, 9) = "编码" .ColWidth(0) = 600 .ColWidth(1) = 1200 .ColWidth(2) = 1000 .ColWidth(3) = 800 .ColWidth(4) = 1000 .ColWidth(5) = 1000 .ColWidth(6) = 800 .ColWidth(7) = 1400 .ColWidth(8) = 600 .ColWidth(9) = 800 For I = 1 To SST .TextMatrix(I, 0) = Adodc1.Recordset.Fields("ID") .TextMatrix(I, 1) = Adodc1.Recordset.Fields("名称") .TextMatrix(I, 2) = Adodc1.Recordset.Fields("型号") .TextMatrix(I, 3) = Adodc1.Recordset.Fields("批次") .TextMatrix(I, 4) = Adodc1.Recordset.Fields("生产时间") .TextMatrix(I, 5) = Adodc1.Recordset.Fields("销售时间") .TextMatrix(I, 6) = Adodc1.Recordset.Fields("销售人员") .TextMatrix(I, 7) = Adodc1.Recordset.Fields("购买厂家") .TextMatrix(I, 8) = Adodc1.Recordset.Fields("数量") .TextMatrix(I, 9) = Adodc1.Recordset.Fields("编码") Adodc1.Recordset.MoveNext Next I End With End Sub 连接代码 Private Sub MSHFlexGrid1_Click() If Val(MSHFlexGrid1.TextMatrix(MSHFlexGrid1.Row, 0)) > 0 Then Dim PPID As Long PPID = Val(MSHFlexGrid1.TextMatrix(MSHFlexGrid1.Row, 0)) Adodc1.RecordSource = "Select * From 销售表 Where ID=" & PPID & "" Adodc1.Refresh AID = Adodc1.Recordset.Fields("ID") Text1(0).Text = Adodc1.Recordset.Fields("名称") Text1(1).Text = Adodc1.Recordset.Fields("型号") Text1(2).Text = Adodc1.Recordset.Fields("批次") Text1(3).Text = Adodc1.Recordset.Fields("生产时间") Text1(4).Text = Adodc1.Recordset.Fields("销售时间") Text1(5).Text = Adodc1.Recordset.Fields("销售人员") Text1(6).Text = Adodc1.Recordset.Fields("购买厂家") Text1(7).Text = Adodc1.Recordset.Fields("数量") Text1(8).Text = Adodc1.Recordset.Fields("编码") Label1.Caption = Adodc1.Recordset.Fields("编码0") End If End Sub 够全面了吧
非常简单,重新刷新另一个表的界面就可以。 哦刚刚理解错了,在修改的同时修改另一个表的记录就可以: Adodc1.RecordSource = "Select * From 销售表1 Where ID=" & AID & "" '第一个表的修改部分 Adodc1.Refresh Adodc1.Recordset.Fields("名称") = Text1(0).Text Adodc1.Recordset.Fields("型号") = Text1(1).Text Adodc1.Recordset.Fields("批次") = Text1(2).Text Adodc1.Recordset.Fields("生产时间") = Text1(3).Text Adodc1.Recordset.Update '更新第一个表的记录 Adodc1.RecordSource = "Select * From 销售表2 Where ID=" & AID & "" '第二个表的修改部分 Adodc1.Refresh Adodc1.Recordset.Fields("名称") = Text1(0).Text Adodc1.Recordset.Fields("型号") = Text1(1).Text Adodc1.Recordset.Fields("批次") = Text1(2).Text Adodc1.Recordset.Update '更新第二个表的记录 MsgBox "祝贺你!这二个表的记录已经修改!", 64, "修改成功!" Unload Me MSH控件操作实例.Show
'一、首先添加一个模块,写上以下通用声明和Sub main():把IP和用户或密码服务名换掉,我的模块是sybase和ORACLE Public conn1 As ADODB.Connection '通用(声明) Public conn2 As ADODB.Connection '通用(声明) Public conn3 As ADODB.Connection '通用(声明) Dim rs1, rs2, rs3, rs4, rs5, rs6, rs7, rs8, rs9, rs10 As New ADODB.Recordset Sub gz_akxt() Set conn1 = New ADODB.Connection conn1.ConnectionString = "Provider=Sybase.ASEOLEDBProvider.2;Initial Catalog=ssfk;Password=tdhmaster;User ID=sa;Persist Security Info=True;Server Name=IP;Server Port Address=5000;Character Set=iso_1;Language=us_english;Application Name=ssfc;Raise Error Behavior=Warnings Only;Print Statement Behavior=Warnings Only;Connect TimeOut=300;WorkStation ID=ssfk" 'conn.Open End SubSub gz_akqz() Set conn3 = New ADODB.Connection conn3.ConnectionString = "Provider=OraOLEDB.Oracle;Password=spqzsa;User ID=spqz;Data Source=""(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=IP)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SPQZ)))"";Persist Security Info=True" 'conn.Open End Sub '二、在窗体上拉一个MSHFlexGrid1、一个Text1、Command1,把统计的SQL语句放到Text1中(把Text1属性可以显示多行) Private Sub Command1_Click() call gz_akqz If Text1.Text <> "" Then conn1.Open Set rs30 = conn1.Execute(Text1.Text) Set MSHFlexGrid1.DataSource = rs30 MSHFlexGrid1.ColWidth(0) = 0 conn1.Close End If End Sub ’如果想把SQL放到代码中就把conn1.Execute(Text1.Text) 的Text1.Text改成"select * from TEST"即可
然后set mshflexgrid1.recordset=rs 就可以
修改代码
Private Sub Command1_Click()
'确认修改
For I = 0 To 8
If Text1(I).Text = "" Then
MsgBox "你没有输入第" & I + 1 & "个文本框的数据,请输入!", 16, "错误提示!"
Exit Sub
End If
Next I
If IsDate(Text1(3).Text) = False Then
MsgBox "你输入的生产时间不是日期,请重新输入!", 16, "错误提示!"
Exit Sub
End If
If IsDate(Text1(4).Text) = False Then
MsgBox "你输入的销售时间不是日期,请重新输入!", 16, "错误提示!"
Exit Sub
End If
If IsNumeric(Text1(7).Text) = False Then
MsgBox "你输入的数量不是数字,请重新输入!", 16, "错误提示!"
Exit Sub
End If
Adodc1.RecordSource = "Select * From 销售表 Where ID=" & AID & ""
Adodc1.Refresh
Adodc1.Recordset.Fields("名称") = Text1(0).Text
Adodc1.Recordset.Fields("型号") = Text1(1).Text
Adodc1.Recordset.Fields("批次") = Text1(2).Text
Adodc1.Recordset.Fields("生产时间") = Text1(3).Text
Adodc1.Recordset.Fields("销售时间") = Text1(4).Text
Adodc1.Recordset.Fields("销售人员") = Text1(5).Text
Adodc1.Recordset.Fields("购买厂家") = Text1(6).Text
Adodc1.Recordset.Fields("数量") = Val(Text1(7).Text)
'Adodc1.Recordset.Fields("编码") = Text1(8).Text '商品编码不能修改
Adodc1.Recordset.Update
MsgBox "祝贺你!这个记录已经修改!", 64, "修改成功!"
Unload Me
MSH控件操作实例.Show
End Sub
添加代码
Private Sub Command2_Click()
'确认添加
For I = 0 To 8
If Text1(I).Text = "" Then
MsgBox "你没有输入第" & I + 1 & "个文本框的数据,请输入!", 16, "错误提示!"
Exit Sub
End If
Next I
If IsDate(Text1(3).Text) = False Then
MsgBox "你输入的生产时间不是日期,请重新输入!", 16, "错误提示!"
Exit Sub
End If
If IsDate(Text1(4).Text) = False Then
MsgBox "你输入的销售时间不是日期,请重新输入!", 16, "错误提示!"
Exit Sub
End If
If IsNumeric(Text1(7).Text) = False Then
MsgBox "你输入的数量不是数字,请重新输入!", 16, "错误提示!"
Exit Sub
End If
Adodc1.RecordSource = "Select * From 销售表"
Adodc1.Refresh
Adodc1.Recordset.AddNew
Adodc1.Recordset.Fields("名称") = Text1(0).Text
Adodc1.Recordset.Fields("型号") = Text1(1).Text
Adodc1.Recordset.Fields("批次") = Text1(2).Text
Adodc1.Recordset.Fields("生产时间") = Text1(3).Text
Adodc1.Recordset.Fields("销售时间") = Text1(4).Text
Adodc1.Recordset.Fields("销售人员") = Text1(5).Text
Adodc1.Recordset.Fields("购买厂家") = Text1(6).Text
Adodc1.Recordset.Fields("数量") = Val(Text1(7).Text)
Adodc1.Recordset.Fields("编码") = Text1(8).Text
Adodc1.Recordset.Fields("编码0") = Val(Label1.Caption)
Adodc1.Recordset.Update
MsgBox "祝贺你!一条新的记录已经存储!", 64, "添加成功!"
Unload Me
MSH控件操作实例.Show
End Sub
删除代码
Private Sub Command3_Click()
'确认删除
If AID = 0 Then
MsgBox "你没有选择要删除的记录,请点击上面列表中需要删除的记录!", 16, "错误提示!"
Exit Sub
End If
If MsgBox("你确实要删除这个记录吗?", 33, "删除确认!") = 1 Then
Adodc1.RecordSource = "Select * From 销售表 Where ID=" & AID & ""
Adodc1.Refresh
Adodc1.Recordset.Delete
MsgBox "祝贺你!这个记录已经删除!", 64, "删除成功!"
Unload Me
MSH控件操作实例.Show
End If
End Sub
启动代码Private Sub Form_Load()
For I = 0 To 8
Text1(I).Text = ""
Next I
Label1.Caption = ""
Adodc1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & App.Path & "\新建数据库.accdb;Jet OLEDB:Database Password=;"
Adodc1.RecordSource = "Select * From 销售表"
Adodc1.Refresh
Dim SST As Integer
SST = Adodc1.Recordset.RecordCount
MSHFlexGrid1.Clear
MSHFlexGrid1.Visible = True
With MSHFlexGrid1
.Cols = 10
.Rows = SST + 1
.TextMatrix(0, 0) = "ID"
.TextMatrix(0, 1) = "名称"
.TextMatrix(0, 2) = "型号"
.TextMatrix(0, 3) = "批次"
.TextMatrix(0, 4) = "生产时间"
.TextMatrix(0, 5) = "销售时间"
.TextMatrix(0, 6) = "销售人员"
.TextMatrix(0, 7) = "购买厂家"
.TextMatrix(0, 8) = "数量"
.TextMatrix(0, 9) = "编码"
.ColWidth(0) = 600
.ColWidth(1) = 1200
.ColWidth(2) = 1000
.ColWidth(3) = 800
.ColWidth(4) = 1000
.ColWidth(5) = 1000
.ColWidth(6) = 800
.ColWidth(7) = 1400
.ColWidth(8) = 600
.ColWidth(9) = 800
For I = 1 To SST
.TextMatrix(I, 0) = Adodc1.Recordset.Fields("ID")
.TextMatrix(I, 1) = Adodc1.Recordset.Fields("名称")
.TextMatrix(I, 2) = Adodc1.Recordset.Fields("型号")
.TextMatrix(I, 3) = Adodc1.Recordset.Fields("批次")
.TextMatrix(I, 4) = Adodc1.Recordset.Fields("生产时间")
.TextMatrix(I, 5) = Adodc1.Recordset.Fields("销售时间")
.TextMatrix(I, 6) = Adodc1.Recordset.Fields("销售人员")
.TextMatrix(I, 7) = Adodc1.Recordset.Fields("购买厂家")
.TextMatrix(I, 8) = Adodc1.Recordset.Fields("数量")
.TextMatrix(I, 9) = Adodc1.Recordset.Fields("编码")
Adodc1.Recordset.MoveNext
Next I
End With
End Sub
连接代码
Private Sub MSHFlexGrid1_Click()
If Val(MSHFlexGrid1.TextMatrix(MSHFlexGrid1.Row, 0)) > 0 Then
Dim PPID As Long
PPID = Val(MSHFlexGrid1.TextMatrix(MSHFlexGrid1.Row, 0))
Adodc1.RecordSource = "Select * From 销售表 Where ID=" & PPID & ""
Adodc1.Refresh
AID = Adodc1.Recordset.Fields("ID")
Text1(0).Text = Adodc1.Recordset.Fields("名称")
Text1(1).Text = Adodc1.Recordset.Fields("型号")
Text1(2).Text = Adodc1.Recordset.Fields("批次")
Text1(3).Text = Adodc1.Recordset.Fields("生产时间")
Text1(4).Text = Adodc1.Recordset.Fields("销售时间")
Text1(5).Text = Adodc1.Recordset.Fields("销售人员")
Text1(6).Text = Adodc1.Recordset.Fields("购买厂家")
Text1(7).Text = Adodc1.Recordset.Fields("数量")
Text1(8).Text = Adodc1.Recordset.Fields("编码")
Label1.Caption = Adodc1.Recordset.Fields("编码0")
End If
End Sub
够全面了吧
哦刚刚理解错了,在修改的同时修改另一个表的记录就可以:
Adodc1.RecordSource = "Select * From 销售表1 Where ID=" & AID & "" '第一个表的修改部分
Adodc1.Refresh
Adodc1.Recordset.Fields("名称") = Text1(0).Text
Adodc1.Recordset.Fields("型号") = Text1(1).Text
Adodc1.Recordset.Fields("批次") = Text1(2).Text
Adodc1.Recordset.Fields("生产时间") = Text1(3).Text
Adodc1.Recordset.Update '更新第一个表的记录
Adodc1.RecordSource = "Select * From 销售表2 Where ID=" & AID & "" '第二个表的修改部分
Adodc1.Refresh
Adodc1.Recordset.Fields("名称") = Text1(0).Text
Adodc1.Recordset.Fields("型号") = Text1(1).Text
Adodc1.Recordset.Fields("批次") = Text1(2).Text
Adodc1.Recordset.Update '更新第二个表的记录
MsgBox "祝贺你!这二个表的记录已经修改!", 64, "修改成功!"
Unload Me
MSH控件操作实例.Show
Public conn1 As ADODB.Connection '通用(声明)
Public conn2 As ADODB.Connection '通用(声明)
Public conn3 As ADODB.Connection '通用(声明)
Dim rs1, rs2, rs3, rs4, rs5, rs6, rs7, rs8, rs9, rs10 As New ADODB.Recordset
Sub gz_akxt()
Set conn1 = New ADODB.Connection
conn1.ConnectionString = "Provider=Sybase.ASEOLEDBProvider.2;Initial Catalog=ssfk;Password=tdhmaster;User ID=sa;Persist Security Info=True;Server Name=IP;Server Port Address=5000;Character Set=iso_1;Language=us_english;Application Name=ssfc;Raise Error Behavior=Warnings Only;Print Statement Behavior=Warnings Only;Connect TimeOut=300;WorkStation ID=ssfk"
'conn.Open
End SubSub gz_akqz()
Set conn3 = New ADODB.Connection
conn3.ConnectionString = "Provider=OraOLEDB.Oracle;Password=spqzsa;User ID=spqz;Data Source=""(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=IP)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=SPQZ)))"";Persist Security Info=True"
'conn.Open
End Sub
'二、在窗体上拉一个MSHFlexGrid1、一个Text1、Command1,把统计的SQL语句放到Text1中(把Text1属性可以显示多行)
Private Sub Command1_Click()
call gz_akqz
If Text1.Text <> "" Then
conn1.Open
Set rs30 = conn1.Execute(Text1.Text)
Set MSHFlexGrid1.DataSource = rs30
MSHFlexGrid1.ColWidth(0) = 0
conn1.Close
End If
End Sub
’如果想把SQL放到代码中就把conn1.Execute(Text1.Text) 的Text1.Text改成"select * from TEST"即可