excel-中台数据库搭建（三）

2023-08-29
作者 John DD
~5.44K 字

最后一个部分，完成数据修改、和删除

一、part1

功能设计：双击单元格 –> 获取单元格的数据 –> 放入指定的TEXTBOX中 –> 修改数据 –> 提交修改 –> 单元格自动更新，不用重复调用数据库；删除数据（从数据库中删除）

遇到的卡点：

联表增加和删除问题，因为做了多个表，需要多个表同时删除
修改完数据，单元格要自动更新修改后的数据
窗体的设计，这个窗体设计卡的比较久，主要是想不放到更方便的方法，尝试了很多中，最后用的这种暴力方式，果然，最简单的，最暴力的方法是最有效的方法。另外，还想做一个人员分配的修改，琢磨了一下，实现难实现，同时要更新好多个表的ID，就没做了，以后有需求再说。

二、具体实现

窗体设计
双击单元格，弹出弹窗；将默认信息添加到窗体里，这里需要打开worksheet 数据总表 vba

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim selectedRow As Range
    Dim dataRange As Range
    Dim textBoxIndex As Integer
    Dim frm As 数据修改
    Dim columnRange As Range
    
    ' 检查双击的目标是否为单个单元格
    If Target.Cells.Count > 1 Then Exit Sub
    
    ' 获取当前选中单元格所在的行
    Set selectedRow = Target.EntireRow
    
    ' 设置数据范围为选中行中的非空单元格
    'Set dataRange = Intersect(selectedRow, selectedRow.Parent.UsedRange).SpecialCells(xlCellTypeConstants)
    
    ' 设置数据范围为选中行的第3-15列和第17列的数据
    'Set dataRange = Intersect(selectedRow, selectedRow.Parent.Range("C:O,Q:Q")).SpecialCells(xlCellTypeConstants)

    
    ' 设置数据范围为选中行的第3-15列的数据
    'Set dataRange = Intersect(selectedRow, selectedRow.Parent.Range("C:O")).SpecialCells(xlCellTypeConstants)

    
    ' 设置数据范围为选中行的第3-15列的数据，包括空单元格
    Set columnRange = selectedRow.Parent.Range("C:O")
    Set dataRange = Intersect(selectedRow, columnRange)
    
    ' 检查是否存在数据
    If dataRange Is Nothing Then Exit Sub
    
    ' 创建并显示用户窗体
    Set frm = New 数据修改

    ' 将数据依次放入窗体的TextBox中
    textBoxIndex = 1
    For Each cell In dataRange
        frm.Controls("TextBox" & textBoxIndex).Text = cell.Value
        textBoxIndex = textBoxIndex + 1
    Next cell
    frm.Show
    ' 取消双击事件的默认操作
    Cancel = True
End Sub

提交修改后的数据，双击窗体，打开VB

'设置id不可修改
Private Sub UserForm_Initialize()
    ' 在窗体加载时展示数据
    
    Me.TextBox1.Enabled = False
    
End Sub


' 提交数据更新请求
Private Sub CommandButton1_Click()
    Dim sql As String, myDate As String, i As Integer, result As Integer
    Dim fieldValues(1 To 12, 1 To 2) As Variant
    
    '确认弹窗
    result = MsgBox("是否确认提交数据？", vbQuestion + vbYesNo, "确认提交")
    
    ' 根据用户选择的按钮执行不同的操作
    If result = vbYes Then
    
        myDate = ThisWorkbook.Path & "\中台数据库.accdb"
        
        '建立数据库连接
        With cnn
            .Provider = "Microsoft.ACE.OLEDB.16.0;"
            .Open myDate
        End With

        ' 初始化数组，将 TextBox 的值和对应的字段名称存储在数组中
        
        fieldValues(1, 1) = Me.TextBox2.Value
        fieldValues(1, 2) = "省份"
        fieldValues(2, 1) = Me.TextBox3.Value
        fieldValues(2, 2) = "市"
        fieldValues(3, 1) = Me.TextBox4.Value
        fieldValues(3, 2) = "[区/县]"
        fieldValues(4, 1) = Me.TextBox5.Value
        fieldValues(4, 2) = "邮编"
        fieldValues(5, 1) = Me.TextBox6.Value
        fieldValues(5, 2) = "学校名称"
        fieldValues(6, 1) = Me.TextBox7.Value
        fieldValues(6, 2) = "地址信息"
        fieldValues(7, 1) = Me.TextBox8.Value
        fieldValues(7, 2) = "姓名"
        fieldValues(8, 1) = Me.TextBox9.Value
        fieldValues(8, 2) = "职称"
        fieldValues(9, 1) = Me.TextBox10.Value
        fieldValues(9, 2) = "联系方式"
        fieldValues(10, 1) = Me.TextBox11.Value
        fieldValues(10, 2) = "学校级别"
        fieldValues(11, 1) = Me.TextBox12.Value
        fieldValues(11, 2) = "学校性质"
        fieldValues(12, 1) = Me.TextBox13.Value
        fieldValues(12, 2) = "归属人"
        
        ' 构建 SQL 更新语句的 SET 子句
        sql = "UPDATE 数据总表 SET "
        For i = 1 To 12
            sql = sql & fieldValues(i, 2) & "='" & fieldValues(i, 1) & "', "
        Next i
        sql = Left(sql, Len(sql) - 2) ' 移除最后一个逗号和空格
        
        ' 添加 WHERE 子句，根据需要修改
        sql = sql & " WHERE ID=" & Me.TextBox1.Value

        cnn.Execute sql
        
        Call 数据更新(fieldValues)
        
        '释放变量
        cnn.Close
        Set cnn = Nothing
        '关闭窗体
        Unload Me       
    Else
        Unload Me  
    End If
End Sub

单元格数据更新

'单元格数据更新
Private Sub 数据更新(fieldValues As Variant)
    Dim valuesArray() As Variant, i As Integer
    ReDim valuesArray(1 To 12)
    
    For i = 1 To 12
        valuesArray(i) = fieldValues(i, 1)
    Next i
    
    Dim selectedRow As Long
    selectedRow = ActiveCell.row
    
    Dim dataRange As Range
    Set dataRange = Intersect(Rows(selectedRow), Range("d:O")).SpecialCells(xlCellTypeConstants)
    
    dataRange.Value = valuesArray
End Sub

删除数据

'删除按键
Private Sub CommandButton3_Click()
    Dim sql As String, myDate As String, i As Integer, result As Integer
    
    '确认弹窗
    result = MsgBox("是否确认删除数据？", vbQuestion + vbYesNo, "确认删除")
    
    ' 根据用户选择的按钮执行不同的操作
    If result = vbYes Then
    
        myDate = ThisWorkbook.Path & "\中台数据库.accdb"
        
        '建立数据库连接
        With cnn
            .Provider = "Microsoft.ACE.OLEDB.16.0;"
            .Open myDate
        End With
        
        sql = "delete from 数据总表 where id=" & Me.TextBox1       
        cnn.Execute sql
         
        '删除当前选中的行
        Dim selectedRow As Range
        Set selectedRow = Selection.EntireRow
        
        selectedRow.Delete
         
        '释放变量
        cnn.Close
        Set cnn = Nothing
        '关闭窗体
        Unload Me

    End If
End Sub