使用treeview树形控件生成目录

Private Sub Form_Load()

Call 生成目录

End Sub

Sub 生成目录()

Dim 节点索引

Set 节点索引 = 目录.Nodes.Add(, , "A", "目录")

节点索引.Sorted = True

Dim 目录rs As Recordset

Set 目录rs = CurrentDb.OpenRecordset("一级表", dbOpenTable)

Do While 目录rs.EOF = False

    Set 节点索引 = 目录.Nodes.Add("A", tvwChild, "B" & 目录rs!一级ID, 目录rs!一级名称)

    节点索引.Sorted = True

目录rs.MoveNext

Loop

目录rs.Close

Set 目录rs = CurrentDb.OpenRecordset("二级表", dbOpenTable)

Do While 目录rs.EOF = False

    Set 节点索引 = 目录.Nodes.Add("B" & 目录rs!一级ID, tvwChild, "C" & 目录rs!二级ID, 目录rs!二级名称)

    节点索引.Sorted = True

目录rs.MoveNext

Loop

目录rs.Close

Set 目录rs = CurrentDb.OpenRecordset("三级表", dbOpenTable)

Do While 目录rs.EOF = False

    Set 节点索引 = 目录.Nodes.Add("C" & 目录rs!二级ID, tvwChild, "D" & 目录rs!三级ID, 目录rs!三级名称)

    节点索引.Sorted = True

目录rs.MoveNext

Loop

目录rs.Close

目录.Nodes(1).Expanded = True

End Sub

Private Sub 目录_NodeClick(ByVal Node As Object)

If Node.Text = "目录" Or Node.Key Like "A*" Or Node.Key Like "B*" Or Node.Key Like "C*" Then

Else

MsgBox Node.Text

End If

End Sub

使用VBA代码引用外部表

Private Sub Form_Close()

Call deltable

End Sub

Private Sub Form_Load()

Call deltable

Call linktable

Me.数据表子窗体.SourceObject = "销售数据表"

End Sub

Public Sub linktable()

Dim dbImport As String

dbImport = CurrentProject.Path & "\data.accdb"

Dim sPassword As String

sPassword = "abc123"

Dim DB As DAO.Database

Set DB = DBEngine.OpenDatabase(Name:=dbImport, Options:=False, ReadOnly:=False, Connect:=";PWD=" & sPassword)

DoCmd.TransferDatabase acLink, "Microsoft Access", dbImport, acTable, "销售数据表", "销售数据表"

    DB.Close

    Set DB = Nothing

End Sub

Public Sub deltable()

On Error Resume Next

Dim deltsql As String

DoCmd.SetWarnings (False)

deltsql = "Drop TABLE 销售数据表"

DoCmd.RunSQL deltsql

End Sub

使用操作查询更新记录

学生成绩管理

Private Sub Command更新_Click()

DoCmd.SetWarnings (False)

Dim update_sql As String

update_sql = "Update 学生成绩表 Set 考试日期=#" & 考试日期 & "#,姓名='" & 姓名 & "',科目='" & 科目 & "',分数=" & 分数 & "  Where  成绩ID=" & 成绩ID

DoCmd.RunSQL update_sql

MsgBox "更新成功"

End Sub

Private Sub Command删除_Click()

If MsgBox("是否删除该记录", vbOKCancel) <> vbOK Then

Exit Sub

End If

DoCmd.SetWarnings (False)

Dim del_sql As String

del_sql = "Delete From 学生成绩表 Where  成绩ID= " & Me.成绩ID

DoCmd.RunSQL del_sql

MsgBox "删除成功"

DoCmd.Close acForm, Me.Name

End Sub

Private Sub Form_Close()

On Error Resume Next

Forms("学生成绩列表").Form.数据表子窗体.Requery

End Sub

Private Sub Form_Load()

Dim search_rs As DAO.Recordset

Dim search_sql As String

search_sql = "Select * From 学生成绩表 Where 成绩ID= " & 成绩IDnum

Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)

If search_rs.EOF = False Then

成绩ID.Value = search_rs!成绩ID.Value

考试日期.Value = search_rs!考试日期.Value

姓名.Value = search_rs!姓名.Value

科目.Value = search_rs!科目.Value

分数.Value = search_rs!分数.Value

End If

search_rs.Close

Set search_rs = Nothing

End Sub

学生成绩列表

Private Sub Command清除_Click()

考试日期.Value = ""

姓名.Value = ""

科目.Value = ""

分数.Value = ""

End Sub

Private Sub Command添加_Click()

On Error GoTo 添加失败错误

If 考试日期 = "" Or IsNull(考试日期) = True Then

MsgBox "考试日期值为空！"

Exit Sub

End If

If 姓名 = "" Or IsNull(姓名) = True Then

MsgBox "姓名值为空！"

Exit Sub

End If

If 科目 = "" Or IsNull(科目) = True Then

MsgBox "科目值为空！"

Exit Sub

End If

If 分数 = "" Or IsNull(分数) = True Then

MsgBox "分数值为空！"

Exit Sub

End If

'================================================================

Dim add_rs As DAO.Recordset

Set add_rs = CurrentDb.OpenRecordset("学生成绩表", dbOpenTable)

With add_rs

.AddNew

!考试日期.Value = 考试日期.Value

!姓名.Value = 姓名.Value

!科目.Value = 科目.Value

!分数.Value = 分数.Value

.Update

.Close

End With

Set add_rs = Nothing

 '================================================================

MsgBox "添加成功！"

Me.数据表子窗体.Requery

Exit Sub

添加失败错误:

MsgBox Err.Description

End Sub

学生成绩数据表

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo 数据更新前提醒_Err

    If (MsgBox("是否保存对记录的修改", 1, "修改记录提醒") = 1) Then

    Else

        DoCmd.RunCommand acCmdUndo

    End If

数据更新前提醒_Exit:

    Exit Sub

数据更新前提醒_Err:

    MsgBox Error$

    Resume 数据更新前提醒_Exit

End Sub

Private Sub 成绩ID_DblClick(Cancel As Integer)

成绩IDnum = Me.成绩ID

DoCmd.OpenForm "学生成绩管理", acNormal

End Sub

模块1

Public 成绩IDnum As Long