Private Sub CommandButton1_Click()
If p = False Then
ListBox1.ListIndex = 0
Me!ListBox1.Selected(0) = False
p = True
End If
Dim com5 As New Ani5
Dim sql As String
sql = "select * from scoredata"
Dim kdata
kdata = com5.dataload(sql, "persondb", "12345678")
Dim listr As Integer
Dim j As Integer
listr = UBound(kdata, 1)
ListBox1.Clear
For j = 0 To listr - 1 Step 1
ListBox1.AddItem kdata(j, 0)
ListBox1.List(j, 1) = kdata(j, 1)
ListBox1.List(j, 2) = kdata(j, 2)
ListBox1.List(j, 3) = kdata(j, 3)
ListBox1.List(j, 4) = kdata(j, 4)
ListBox1.List(j, 5) = kdata(j, 5)
Next j
If p = True Then
Me!ListBox1.Selected(0) = True
ListBox1.SetFocus
p = False
End If
End Sub
Private Sub CommandButton2_Click()
'Me!ListBox1.Selected(j) = False
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
End Sub
=======================
Private Sub ListBox1_Change()
Dim j As Integer
j = ListBox1.ListIndex
If p = True Then
TextBox1.Text = ListBox1.List(j, 0)
TextBox2.Text = ListBox1.List(j, 1)
TextBox3.Text = ListBox1.List(j, 2)
TextBox4.Text = ListBox1.List(j, 3)
TextBox5.Text = ListBox1.List(j, 4)
TextBox6.Text = ListBox1.List(j, 5)
End If
End Sub
Private Sub UserForm_Activate()
p = True
End Sub
=============================
1.ListBox1_keyDown(byval keycode as MsForms.ReturnInteger,byval shift as integer)
回傳整數 組合鍵
End Sub
2.ListBox1_keyUp(byval keycode as MsForms.ReturnInteger,byval shift as integer)
回傳整數 組合鍵
End Sub
* keycode 鍵盤ASCII碼
上18
左37 右39
下40
==================================================================
1.ListBox1_Change(): 遮罩變動後立即反應事件
變動量 change和ListBox1內部是關聯的
2.ListBox1_Click(): mouse點擊ListBox1記錄列所觸發事件 VBA error
點擊 click和ListBox1內部是關聯的
3.兩者若同時存在已change為優先
===========================================
Dim p As Boolean
Dim p2 As Boolean
Private Sub CommandButton1_Click()
p2 = False
If p = False Then
ListBox1.ListIndex = 0
Me!ListBox1.Selected(0) = False
p = True
End If
Dim com5 As New Ani5
Dim sql As String
sql = "select * from scoredata"
Dim kdata
kdata = com5.dataload(sql, "persondb", "12345678")
Dim listr As Integer
Dim j As Integer
listr = UBound(kdata, 1)
ListBox1.Clear
For j = 0 To listr - 1 Step 1
ListBox1.AddItem kdata(j, 0)
ListBox1.List(j, 1) = kdata(j, 1)
ListBox1.List(j, 2) = kdata(j, 2)
ListBox1.List(j, 3) = kdata(j, 3)
ListBox1.List(j, 4) = kdata(j, 4)
ListBox1.List(j, 5) = kdata(j, 5)
Next j
If p = True Then
Me!ListBox1.Selected(0) = True
ListBox1.SetFocus
p = False
End If
End Sub
Private Sub CommandButton2_Click()
'Me!ListBox1.Selected(j) = False
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
End Sub
=============
Private Sub ListBox1_Change()
Dim j As Integer
j = ListBox1.ListIndex
If p = True Then
TextBox1.Text = ListBox1.List(j, 0)
TextBox2.Text = ListBox1.List(j, 1)
TextBox3.Text = ListBox1.List(j, 2)
TextBox4.Text = ListBox1.List(j, 3)
TextBox5.Text = ListBox1.List(j, 4)
TextBox6.Text = ListBox1.List(j, 5)
End If
If p2 = True Then
TextBox1.Text = ListBox1.List(j, 0)
TextBox2.Text = ListBox1.List(j, 1)
TextBox3.Text = ListBox1.List(j, 2)
TextBox4.Text = ListBox1.List(j, 3)
TextBox5.Text = ListBox1.List(j, 4)
TextBox6.Text = ListBox1.List(j, 5)
End If
End Sub
====================
Private Sub ListBox1_Click()
Dim j As Integer
j = ListBox1.ListIndex
TextBox1.Text = ListBox1.List(j, 0)
TextBox2.Text = ListBox1.List(j, 1)
TextBox3.Text = ListBox1.List(j, 2)
TextBox4.Text = ListBox1.List(j, 3)
TextBox5.Text = ListBox1.List(j, 4)
TextBox6.Text = ListBox1.List(j, 5)
End Sub
==========================================
Private Sub ListBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 38 Then
p2 = True
End If
If KeyCode = 40 Then
p2 = True
End If
End Sub
Private Sub UserForm_Activate()
p = True
p2 = False
End Sub
======================
查詢
1.sql直接寫在表單
2.sql 語法寫在資料庫管理介面，而不寫在表單，為了網路安全保護資料庫
CREATE PROCEDURE `pid2`(IN `id` VARCHAR(50))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
select * from scoredata where 學號=id;
END
CREATE PROCEDURE `pname`(IN `name` VARCHAR(50))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
select * from scoredata where 姓名=name;
END
=======================================
Dim p As Boolean查詢
1.sql直接寫在表單
2.sql 語法寫在資料庫管理介面，而不寫在表單，為了網路安全保護資料庫
CREATE PROCEDURE `pid2`(IN `id` VARCHAR(50))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
select * from scoredata where 學號=id;
END
CREATE PROCEDURE `pname`(IN `name` VARCHAR(50))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
select * from scoredata where 姓名=name;
END
=======================================
Dim p2 As Boolean
Private Sub CommandButton1_Click()
Dim sql As String
sql = "select * from scoredata"
Call loaddata(sql)
End Sub
Private Sub CommandButton2_Click()
'Me!ListBox1.Selected(j) = False
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
End Sub
Private Sub CommandButton3_Click()
On Error GoTo la1
Dim com5 As New Ani5
Dim sql As String
Dim no, name As String
Dim c1, c2, c3, c4 As Integer
no = TextBox1.Text
name = TextBox2.Text
c1 = CInt(TextBox3.Text)
c2 = CInt(TextBox4.Text)
c3 = CInt(TextBox5.Text)
c4 = CInt(TextBox6.Text)
sql = "insert into scoredata values('" & no & "','" & name & "'," & c1 & "," & c2 & "," & c3 & "," & c4 & ")"
com5.modifydata sql:=sql, dn:="persondb", pw:="12345678"
Exit Sub
la1:
MsgBox "學號不可重複或資料不完整"
End Sub
Sub loaddata(ByVal sql As String)
p2 = False
If p = False Then
ListBox1.ListIndex = 0
Me!ListBox1.Selected(0) = False
p = True
End If
Dim com5 As New Ani5
'Dim sql As String
'sql = "select * from scoredata"
Dim kdata
kdata = com5.dataload(sql, "persondb", "12345678")
Dim listr As Integer
Dim j As Integer
listr = UBound(kdata, 1)
ListBox1.Clear
For j = 0 To listr - 1 Step 1
ListBox1.AddItem kdata(j, 0)
ListBox1.List(j, 1) = kdata(j, 1)
ListBox1.List(j, 2) = kdata(j, 2)
ListBox1.List(j, 3) = kdata(j, 3)
ListBox1.List(j, 4) = kdata(j, 4)
ListBox1.List(j, 5) = kdata(j, 5)
Next j
If p = True Then
Me!ListBox1.Selected(0) = True
ListBox1.SetFocus
p = False
End If
End Sub
Private Sub CommandButton4_Click()
Dim id As String
Dim sql As String
If TextBox7.Text <> "" Then
id = TextBox7.Text
sql = "call pid2 ('" & id & "')"
Call loaddata(sql)
Else
MsgBox "請輸入學號資料"
End If
End Sub
Private Sub CommandButton5_Click()
Dim name As String
Dim sql As String
If TextBox8.Text <> "" Then
name = TextBox8.Text
sql = "call pname ('" & name & "')"
Call loaddata(sql)
Else
MsgBox "請輸入姓名資料"
End If
End Sub
Private Sub ListBox1_Change()
Dim j As Integer
j = ListBox1.ListIndex
If p = True Then
TextBox1.Text = ListBox1.List(j, 0)
TextBox2.Text = ListBox1.List(j, 1)
TextBox3.Text = ListBox1.List(j, 2)
TextBox4.Text = ListBox1.List(j, 3)
TextBox5.Text = ListBox1.List(j, 4)
TextBox6.Text = ListBox1.List(j, 5)
End If
If p2 = True Then
TextBox1.Text = ListBox1.List(j, 0)
TextBox2.Text = ListBox1.List(j, 1)
TextBox3.Text = ListBox1.List(j, 2)
TextBox4.Text = ListBox1.List(j, 3)
TextBox5.Text = ListBox1.List(j, 4)
TextBox6.Text = ListBox1.List(j, 5)
End If
End Sub
Private Sub ListBox1_Click()
Dim j As Integer
j = ListBox1.ListIndex
TextBox1.Text = ListBox1.List(j, 0)
TextBox2.Text = ListBox1.List(j, 1)
TextBox3.Text = ListBox1.List(j, 2)
TextBox4.Text = ListBox1.List(j, 3)
TextBox5.Text = ListBox1.List(j, 4)
TextBox6.Text = ListBox1.List(j, 5)
End Sub
Private Sub ListBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 38 Then
p2 = True
End If
If KeyCode = 40 Then
p2 = True
End If
End Sub
Private Sub UserForm_Activate()
p = True
p2 = False
End Sub
沒有留言:
張貼留言