第一次机房收费系统 -- 模块篇

男娘i 2023-10-17 17:56 63阅读 0赞

#### 目的 ####

为了方便以后直接调用，减少整体代码量

#### 代码 ####

Public UserName As String                                                       '定义全局变量 UserName
    Public Declare Function SetParent Lib "user32" (ByVal hWndChild As Long, ByVal hWndNewParent As Long) As Long '使子窗体可以正常显示
    Rem：系统启动时，首先执行，保证用户管理
    Sub main()
        Dim fLogin As New UIFormLogin
        fLogin.Show vbModal                                                         '显示登录窗体
        If Not fLogin.OK Then
            'login failed so exit app
            End
        End If
        Unload fLogin
        'set fmainform = New frmMain 判断为是，进入系统，显示主窗体实例。
        MDImain.Show
    End Sub
    
    Rem：公共的数据操作函数，执行各种 SQL 语句。添加函数 ExecuteSQL(SQL 用来存放需要执行的 SQL 语句，MsgString 用来返回执行的提示信息)
    Public Function ExecuteSQL(ByVal SQL As String, MsgString As String) As ADODB.Recordset '传递参数 SQL 传递查询语句，MsgString 传递查询信息
        Dim cnn As ADODB.Connection                                                 '自身以一个数据集对象的形式返回
        Dim rst As ADODB.Recordset                                                  '定义连接
        Dim sTokens() As String                                                     '定义字符串
        On Error GoTo ExecuteSQL_error                                              '异常处理
        sTokens = Split(SQL)                                                        '用 Split 函数产生一个包含各个子串的数组
        Set cnn = New ADODB.Connection                                              '创建连接
        cnn.Open ConnectString                                                      '打开连接
        If InStr("INSET,DELETE,UPDATE", UCase$(sTokens(0))) Then                    '判断字符串中是否含有指定内容
            cnn.Execute SQL                                                         '执行查询语句
            MsgString = sTokens(0) & "query successful"                             '返回查询信息
        Else
            Set rst = New ADODB.Recordset                                           '创建数据集对象
            rst.Open Trim$(SQL), cnn, adOpenKeyset, adLockOptimistic                '返回查询结果
            Set ExecuteSQL = rst                                                    '返回记录集对象
            MsgString = "查询到" & rst.RecordCount & "条记录"
        End If
    ExecuteSQL_exit:
        Set rst = Nothing                                                           '清空记录集对象
        Set cnn = Nothing                                                           '中断连接
        Exit Function
    ExecuteSQL_error:                                                               '错误类型判断
        MsgString = "查询错误：" & Err.Description
        Resume ExecuteSQL_exit
        Exit Function
    End Function
    Public Function ConnectString() As String
        ConnectString = "FileDSN=charge.dsn;UID=sa;PWD=123456"                      '返回一个数据库连接
    End Function
    
    Rem：检查各种文本框内容是否为空，定义 Testtxt 函数
    Public Function Testtxt(txt As String) As Boolean
        If Trim(txt) = "" Then
            Testtxt = False
        Else
            Testtxt = True
        End If
    End Function
    Rem：限制特殊字符输入，定义 Number_KeyPress 函数
    Public Sub Number_KeyPress(KeyAscii As Integer)
        Select Case KeyAscii
        Case 8
        Case Asc("0") To Asc("9")
        Case Else
            KeyAscii = 0
        End Select
    End Sub
    Rem：导出为 Excel 表
    Public Sub Derive(FormName As Form, MSHFlexGridName As MSHFlexGrid)
        Dim TempExcel As Excel.Application                                          '用来创建一个工作薄
        Dim TempSheet As Excel.Worksheet                                            '用来创建一个工作表
        Dim intI As Integer                                                         '设置循环变量
        Dim intJ As Integer
        On Error GoTo Err_Proc
        Set TempExcel = New Excel.Application                                       '实例化为一个对象
        TempExcel.Application.Visible = True
        TempExcel.Workbooks.Add (1)
        
        Set TempSheet = TempExcel.ActiveWorkbook.ActiveSheet
        
        '对行和列进行循环，将数据填入到表中
        For intI = 0 To MSHFlexGridName.Rows - 1
            For intJ = 0 To MSHFlexGridName.Cols - 1
                TempSheet.Cells(intI + 1, intJ + 1) = MSHFlexGridName.TextMatrix(intI, intJ)
            Next intJ
        Next intI
    Err_Proc:
        MsgBox "导出数据失败！", vbExclamation, "提示"
    End Sub
    Rem：限制特殊字符输入，定义 Number1_KeyPress 函数
    Public Sub Number1_KeyPress(KeyAscii As Integer)
        Select Case KeyAscii
        Case 8
        Case Asc("0") To Asc("9")
        Case Else
            KeyAscii = 0
        End Select
    End Sub

#### 思考 ####

在非计算机中，要学会经验复用，学会使用模板，让自己更高效。