EXCEL VBA行政与人力资源管理应用案例详解 第9章.pdf

C H A P T E R 保险基金管理模块的设计 9-1 保险基金管理模块的总体设计 9-2 职工保险基金管理窗体结构设计 9-3 职工保险基金管理窗体程序代码设计 9-4 职工保险基金管理子模块应用示例 9-5 为保险基金管理自定义菜单命令和自定义命令按钮指定宏 行政与人力资源管理应用案例详解 职工保险基金管理是指对职工的各种社会保险和住房公积金的账号、费用进行基本管理。本章将详细介绍保险基金管理模块的制作方法和步骤。9-1 保险基金管理模块的总体设计 本章介绍的保险基金管理模块包括一个子模块，即“职工保险基金管理”。在这个模块中，将职工的各种社会保险和住房公积金的账号和费用进行统一管理。因此，保险基金管理模块或“职工保险基金管理”子模块是以一个名为“职工保险基金信息”的数据表为基础的。“职工保险基金信息”数据表所包含的字段信息如表 9-1 所示。表 9-1 “职工保险基金信息”数据表字段 字段名称 字段类型 字段大小 是否允许为空 说 明 职工编号 文本 5 否 来源于“职工基本信息”数据表 姓名 文本 10 否 来源于“职工基本信息”数据表 性别 文本 1 否 来源于“职工基本信息”数据表 所属部门 文本 10 否 来源于“职工基本信息”数据表 养老保险号 文本 30 否 养老保险费 数字 单精度型 否 失业保险号 文本 30 否 失业保险费 数字 单精度型 否 医疗保险号 文本 30 否 医疗保险费 数字 单精度型 否 工伤保险号 文本 30 否 工伤保险费 数字 单精度型 否 生育保险号 文本 30 否 生育保险费 数字 单精度型 否 住房公积金号 文本 30 否 住房公积金费 数字 单精度型 否 备注 文本 50 是 “职工保险基金信息”数据表由系统自动创建，具体程序代码可参阅第 2 章的有关内容。“职工保险基金管理”子模块由一个“职工保险基金管理”主窗体、一个“按钮查询”子窗体和一个“职工信息输入”子窗体组成，其中“按钮查询”子窗体和“职工信息输入”子窗体在前面的章节中已经作了介绍，下面主要介绍“职工保险基金管理”主窗体的设计。9-2 职工保险基金管理窗体结构设计 326 保险基金管理模块的设计 9“职工保险基金管理”窗体的结构如图 9-1 所示，它与第 8 章介绍的“职工合同管理”窗体的结构大同小异。“职工保险基金管理”窗体由 1 个用户窗体、6 个框架、16 个标签、16 个文本框、1 个 Spreadsheet 控件和 11 个命令按钮组成。用户窗体及各个控件的功能及属性设置如下。图 9-1 “职工保险基金管理”主窗体结构（1）用户窗体：名称属性和 Caption 属性均设置为“职工保险基金管理”。（2）6 个框架：它们用于将不同功能的控件组合在一起。其中 4 个框架的 Caption 属性分别设置为“保险基金信息”、“编辑记录”、“查询浏览记录”和“职工保险清单”；2个框架的 Caption 属性设置为空值，Height 属性设置为 3，并分别放置于标题为“保险基金信息”和“查询浏览记录”的框架内，用于将不同功能的控件分隔成组。（3）标题为“保险基金信息”框架内的控件：在此框架内，有 16 个标签和 16 个文本框，这些控件用于显示或输入职工保险基金信息。16 个标签分别用于对 16 个文本框的功能进行说明，其 Caption 属性的设置情况如图9-1 所示。16 个文本框用于输入或显示职工保险基金信息，其功能说明及属性设置如表 9-2 所示。表 9-2 职工保险基金信息数据控件的功能说明和属性设置 控件类型 控件名称（名称属性）控件功能 文本框 职工编号 显示或输入职工编号 文本框 姓名 显示或输入职工姓名 文本框 性别 显示或输入职工性别 文本框 所属部门 显示或输入职工所属部门 文本框 养老保险号 显示或输入养老保险号 文本框 养老保险费 显示或输入养老保险费 文本框 失业保险号 显示或输入失业保险号 文本框 失业保险费 显示或输入失业保险费 327 行政与人力资源管理应用案例详解文本框 医疗保险号 显示或输入医疗保险号 文本框 医疗保险费 显示或输入医疗保险费 续上表 控件类型 控件名称（名称属性）控件功能 文本框 工伤保险号 显示或输入工伤保险号 文本框 工伤保险费 显示或输入工伤保险费 文本框 生育保险号 显示或输入生育保险号 文本框 生育保险费 显示或输入生育保险费 文本框 住房公积金号 显示或输入住房公积金号 文本框 住房公积金费 显示或输入住房公积金费 文本框 备注 显示或输入备注文字（4）用户窗体上的 11 个命令按钮：分别完成保存、修改、查询浏览记录等功能，其功能说明和属性设置如表 9-3 所示。表 9-3 命令按钮的功能说明和属性设置 名称属性 Caption 属性 功 能 输入新数据 输入新数据 打开“职工信息输入”子窗体，准备输入新职工的保险基金信息 保存 保存 保存保险基金信息记录 更新 更新 更新保险基金信息记录 删除 删除 删除保险基金信息记录 查询 查询 启动一个查询窗口，查询保险基金信息记录 重置窗口 重置窗口 刷新窗口 第一条 第一条 浏览第一条记录 下一条 下一条 浏览下一条记录 上一条 上一条 浏览上一条记录 最末条 最末条 浏览最末条记录 退出 退出系统 关闭“职工保险基金管理”窗体（5）在 Spreadsheet1 控件的单元格 A1 中输入“职工编号”，合并单元格 A1:A2；在单元格 B1 中输入“姓名”，合并单元格 B1:B2；在单元格 C1 中输入“性别”，合并单元格C1:C2；在单元格 D1 中输入“所属部门”，合并单元格 D1:D2；在单元格 E1 中输入“养老保险”，合并单元格 E1:F1；在单元格 G1 中输入“失业保险”，合并单元格 G1:H1；在单元格 I1 中输入“医疗保险”，合并单元格 I1:J1；在单元格 K1 中输入“工伤保险”，合并单元格 K1:L1；在单元格 M1 中输入“生育保险”，合并单元格 M1:N1；在单元格 O1中输入“住房公积金”，合并单元格 O1:P1；在单元格 E2 中输入“保险号”，在单元格F2 中输入“费用”，并将单元格 E2 和 F2 分别复制到单元格 G2 和 H2、I2 和 J2、K2 和L2、M2 和 N2；在单元格 O2 中输入“公积金号”，在单元格 P2 中输入“费用”。选取单元格区域 A1:P2，加粗字体，并居中显示。9-3 职工保险基金管理窗体程序代码设计 328 保险基金管理模块的设计 9 保险基金管理，就是以“人事管理.mdb”数据库中的“职工保险基金信息”数据表为基础，通过“保险基金管理”窗体及有关控件，完成职工的各种社会保险信息和住房公积金资料的添加、修改、删除、显示等功能。下面介绍“职工保险基金管理”主窗体及有关控件的事件程序代码。1定义模块级变量 首先定义下面的模块级变量，它们放在窗体对象程序代码窗口的顶部：Dim cnn As ADODB.Connection Dim rsInsurance As ADODB.Recordset Dim mydata As Variant 2为用户窗体设计 Initialize 事件程序 为用户窗体设置 Initialize 事件，以便在启动“职工保险基金管理”窗体时，系统自动建立与数据库的连接，对有关的数据表进行查询，并设置某些控件的属性。“职工保险基金管理”窗体的 Initialize 事件程序代码如下：Private Sub UserForm_Initialize()mySearchShow=False 窗体显示的数据不是查询出的数据 mydata=Array(职工编号,姓名,性别,所属部门,养老保险号,养老保险费,_ 失业保险号,失业保险费,医疗保险号,医疗保险费,工伤保险号,_ 工伤保险费,生育保险号,生育保险费,住房公积金号,住房公积金费)建立与人事管理数据库的连接 Set cnn=New ADODB.Connection With cnn .Provider=microsoft.jet.oledb.4.0 .Open ThisWorkbook.Path&人事管理.mdb End With Call 保险信息查询 Call 显示保险信息记录清单(rsInsurance)职工编号.Enabled=False:姓名.Enabled=False:性别.Enabled=False 所属部门.Enabled=False End Sub 3为有关控件设计 Change 事件程序 为了防止在有关的文本框和复合框中输入过长的数据，引起保存数据出现错误，应当对这些控件设置 Change 事件，以保证输入数据的长度不超过规定允许的长度。一些控件的 Change 事件程序代码如下：Private Sub 养老保险号_Change()Call TextBoxDataLength(cnn,职工保险基金信息,养老保险号,养老保险号)End Sub Private Sub 失业保险号_Change()Call TextBoxDataLength(cnn,职工保险基金信息,失业保险号,失业保险号)End Sub Private Sub 医疗保险号_Change()Call TextBoxDataLength(cnn,职工保险基金信息,医疗保险号,医疗保险号)End Sub Private Sub 工伤保险号_Change()Call TextBoxDataLength(cnn,职工保险基金信息,工伤保险号,工伤保险号)End Sub Private Sub 生育保险号_Change()Call TextBoxDataLength(cnn,职工保险基金信息,生育保险号,生育保险号)End Sub 329 行政与人力资源管理应用案例详解Private Sub 住房公积金号_Change()Call TextBoxDataLength(cnn,职工保险基金信息,住房公积金号,住房公积金号)End Sub 4为“养老保险费”等 6 个文本框设计 KeyPress 事件 为“养老保险费”等 6 个需要输入费用的文本框设计 KeyPress 事件，以控制用户只能输入输入 09 之间的数字和小数点，程序代码分别如下：Private Sub 养老保险费_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)If(KeyAscii 57)And KeyAscii 46 Then KeyAscii=0:养老保险费SetFocus End If End Sub Private Sub 失业保险费_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)If(KeyAscii 57)And KeyAscii 46 Then KeyAscii=0:失业保险费 SetFocus End If End Sub Private Sub 医疗保险费_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)If(KeyAscii 57)And KeyAscii 46 Then KeyAscii=0:医疗保险费 SetFocus End If End Sub Private Sub 工伤保险费_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)If(KeyAscii 57)And KeyAscii 46 Then KeyAscii=0:工伤保险费 SetFocus End If End Sub Private Sub 生育保险费_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)If(KeyAscii 57)And KeyAscii 46 Then KeyAscii=0:生育保险费 SetFocus End If End Sub Private Sub 住房公积金费_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)If(KeyAscii 57)And KeyAscii 46 Then KeyAscii=0:住房公积金费 SetFocus End If End Sub 5为【输入新数据】按钮设计 Click 事件程序 单击【输入新数据】按钮，系统将打开“职工信息输入”子窗体，以方便用户查找输入某职工的基本信息。在单击【输入新数据】按钮后，系统将检查数据库中是否已经存在了某个职工的保险基金信息记录。【输入新数据】按钮 Click 事件的程序代码如下：Private Sub 输入新数据_Click()On Error Resume Next Call 清除保险窗口数据 职工信息输入.Show 职工编号.Value=Format(职工信息输入.Spreadsheet1.ActiveCell.Value,00000)姓名.Value=职工信息输入.Spreadsheet1.ActiveCell.Offset(0,1).Value 性别.Value=职工信息输入.Spreadsheet1.ActiveCell.Offset(0,2).Value 所属部门.Value=职工信息输入.Spreadsheet1.ActiveCell.Offset(0,3).Value 养老保险号.SetFocus 330 保险基金管理模块的设计 9 判断数据库中是否已经存在了某个职工合同信息 Dim mysql As String Dim rsCheck As ADODB.Recordset Set rsCheck=New ADODB.Recordset mysql=select 职工编号,姓名 from 职工保险基金信息 _&where 职工编号=&职工编号.Value&rsCheck.Open mysql,cnn,adOpenKeyset,adLockOptimistic If rsCheck.RecordCount 0 Then MsgBox 数据库中已经存在了下面的保险记录：&vbCrLf&vbCrLf _&职工编号：&职工编号.Value&vbCrLf _&姓名 ：&姓名.Value&vbCrLf _&vbCrLf&vbCrLf&请重新选择职工！,vbCritical,保险重复错误！Set rsCheck=Nothing 职工信息输入.Show Exit Sub End If Unload 职工信息输入 End Sub 6为【保存】按钮设计 Click 事件程序 单击【保存】按钮，系统就将职工保险基金信息记录保存到“职工保险基金信息”数据表中。在保存数据之前，系统将检查一些数据控件是否已经输入了数据。【保存】按钮Click 事件的程序代码如下：Private Sub 保存_Click()On Error GoTo hhh mySearchShow=False 判断有关的数据是否为空 If TextBoxDataIsBlank(养老保险号,养老保险号)=True Then Exit Sub If TextBoxDataIsBlank(养老保险费,养老保险费)=True Then Exit Sub If TextBoxDataIsBlank(失业保险号,失业保险号)=True Then Exit Sub If TextBoxDataIsBlank(失业保险费,失业保险费)=True Then Exit Sub If TextBoxDataIsBlank(医疗保险号,医疗保险号)=True Then Exit Sub If TextBoxDataIsBlank(医疗保险费,医疗保险费)=True Then Exit Sub If TextBoxDataIsBlank(工伤保险号,工伤保险号)=True Then Exit Sub If TextBoxDataIsBlank(工伤保险费,工伤保险费)=True Then Exit Sub If TextBoxDataIsBlank(生育保险号,生育保险号)=True Then Exit Sub If TextBoxDataIsBlank(生育保险费,生育保险费)=True Then Exit Sub If TextBoxDataIsBlank(住房公积金号,住房公积金号)=True Then Exit Sub If TextBoxDataIsBlank(住房公积金费,住房公积金费)=True Then Exit Sub 将数据添加到数据库中 Call 保险信息查询 rsInsurance.AddNew Call 保存保险信息数据(rsInsurance)刷新保险信息查询及显示 Call 保险信息查询 Call 显示保险信息(rsInsurance)Call 显示保险信息记录清单(rsInsurance)Exit Sub hhh:MsgBox 保存数据出现错误!&vbCrLf&vbCrLf _&错误为:&Err.Description,vbCritical,保存错误 331 行政与人力资源管理应用案例详解End Sub 7为【更新】按钮设计 Click 事件程序 如果用户需要更新某个职工的某条保险基金信息记录，应当首先将该条记录查询出来，然后单击【更新】按钮。【更新】按钮的 Click 事件程序代码如下：Private Sub 更新_Click()Dim res res=MsgBox(准备更新当前职工的保险资料数据！真要更新吗？,_ vbYesNo+vbDefaultButton2+vbQuestion,更新保险资料)If res=vbNo Then Exit Sub If mySearchShow=False Then Call 保存保险信息数据(rsInsurance)Call 保险信息查询 Call 显示保险信息记录清单(rsInsurance)ElseIf mySearchShow=True Then Call 保存保险信息数据(rsSearch)Call 显示保险信息记录清单(rsSearch)End If End Sub 8为【删除】按钮设计 Click 事件程序 如果用户要删除某条保险基金信息记录，应当首先将该条记录查询出来，然后单击【删除】按钮。【删除】按钮的 Click 事件程序代码如下：Private Sub 删除_Click()Dim res res=MsgBox(准备删除当前职工的保险资料数据！真要删除吗？,_ vbYesNo+vbDefaultButton2+vbQuestion,删除保险资料)If res=vbNo Then Exit Sub Dim mysql As String Dim rsDelete As ADODB.Recordset Set rsDelete=New ADODB.Recordset mysql=delete*from 职工保险基金信息 where 职工编号=&职工编号.Value&rsDelete.Open mysql,cnn,adOpenKeyset,adLockOptimistic Set rsDelete=Nothing Call 清除保险窗口数据 Call 保险信息查询 Call 显示保险信息(rsInsurance)Spreadsheet1.ActiveSheet.Unprotect Spreadsheet1.ActiveSheet.Range(A3:Z1000).ClearContents Spreadsheet1.ActiveSheet.Protect Call 显示保险信息记录清单(rsInsurance)End Sub 9为【查询】按钮设计 Click 事件程序 单击【查询】按钮，系统将打开“按钮查询”子窗体，用户可以设置任意查询条件进行查询。【查询】按钮的 Click 事件程序代码如下：Private Sub 查询_Click()On Error Resume Next Call 清除保险窗口数据 332 保险基金管理模块的设计 9 按钮查询.Show End Sub 10为【重置窗口】按钮设计 Click 事件程序 单击【重置窗口】按钮，系统将对数据表进行重新查询，并刷新窗体数据显示。【重置窗口】按钮的 Click 事件程序代码如下：Private Sub 重置窗口_Click()mySearchShow=False Call 保险信息查询 Call 显示保险信息(rsInsurance)Call 显示保险信息记录清单(rsInsurance)Spreadsheet1.Range(A2).Select End Sub 11为 4 个浏览按钮【第一条】、【下一条】、【上一条】和【最末条】按钮设计 Click事件程序 单击 4 个浏览按钮【第一条】、【下一条】、【上一条】和【最末条】按钮，可以依次查看每个保险基金信息记录。这 4 个按钮的 Click 事件程序与前面介绍的有关窗体的 4 个浏览按钮基本相同，只是调用的子程序不同。这里调用的子程序分别如下：Call 显示保险信息(rsInsurance)Call 显示保险信息(rsSearch)12为【退出系统】按钮设计 Click 事件程序 单击【退出系统】按钮，就关闭保险基金管理窗体。【退出系统】按钮的 Click 事件程序代码如下：Private Sub 退出系统_Click()End End Sub 13为 Spreadsheet1 控件设计 Click 事件程序 为 Spreadsheet1 控件设置 Click 事件，可以在单击控件内某行的任一单元格时，就将该行职工的保险基金信息数据显示在各个控件中。Spreadsheet1 控件的 Click 事件程序代码如下：Private Sub Spreadsheet1_Click()On Error GoTo hhh If mySearchShow=False Then rsInsurance.AbsolutePosition=Spreadsheet1.ActiveCell.Row-2 Call 显示保险信息(rsInsurance)ElseIf mySearchShow=True Then rsSearch.AbsolutePosition=Spreadsheet1.ActiveCell.Row-2 Call 显示保险信息(rsSearch)End If Exit Sub hhh:Call 清除保险窗口数据 End Sub 14几个调用的子程序 为了简化程序代码，某些控件的事件程序调用了几个子程序，这几个子程序的程序代 333 行政与人力资源管理应用案例详解码如下。（1）“保险信息查询”子程序 Public Sub 保险信息查询()Dim mysql As String Set rsInsurance=New ADODB.Recordset mysql=select*from 职工保险基金信息 order by 职工编号 rsInsurance.Open mysql,cnn,adOpenKeyset,adLockOptimistic End Sub（2）“保存保险信息数据”子程序 Public Sub 保存保险信息数据(myRs As ADODB.Recordset)Dim i As Integer For i=0 To UBound(mydata)myRs.Fields(i)=Me.Controls(mydata(i).Value Next i myRs.Update Call 显示保险信息记录清单(myRs)End Sub（3）“显示保险信息”子程序 Public Sub 显示保险信息(myRs)Dim i As Integer For i=0 To UBound(mydata)Me.Controls(mydata(i).Value=myRs.Fields(i)Next i End Sub（4）“显示保险信息记录清单”子程序 Public Sub 显示保险信息记录清单(myRs As ADODB.Recordset)On Error Resume Next Spreadsheet1.Application.ActiveSheet.Unprotect Spreadsheet1.Range(A3:BB10000).ClearContents myRs.MoveFirst For i=1 To myRs.RecordCount For j=0 To myRs.Fields.Count-1 Spreadsheet1.Cells(i+2,j+1)=myRs.Fields(j)Next j myRs.MoveNext Next i myRs.MoveFirst Spreadsheet1.Cells.Font.Size=10 Spreadsheet1.Rows.AutoFit Spreadsheet1.Columns.AutoFit Spreadsheet1.Columns(A:A).NumberFormat=00000 Spreadsheet1.Columns(A:A).HorizontalAlignment=xlLeft Spreadsheet1.Columns(F:F).NumberFormat=0.00 Spreadsheet1.Columns(H:H).NumberFormat=0.00 Spreadsheet1.Columns(J:J).NumberFormat=0.00 Spreadsheet1.Columns(L:L).NumberFormat=0.00 Spreadsheet1.Columns(N:N).NumberFormat=0.00 Spreadsheet1.Columns(P:P).NumberFormat=0.00 Spreadsheet1.Application.ActiveSheet.Protect End Sub 334 保险基金管理模块的设计 9（5）“清除保险窗口数据”子程序 Public Sub 清除保险窗口数据()Dim i As Integer For i=0 To UBound(mydata)Me.Controls(mydata(i).Value=Next i End Sub 9-4 职工保险基金管理子模块应用示例 执行自定义菜单【保险基金管理】中的【职工保险基金管理】命令，或单击【合同保险基金管理】自定义工具栏中的【职工保险基金管理】命令按钮，打开“职工保险基金管理”窗体，如图 9-2 所示，用户就可以通过此窗体输入、浏览编辑、查询内部保险基金信息。图 9-2 职工保险基金管理窗口“职工保险基金管理”窗体的使用方法与“职工合同管理”窗体的使用方法相同，此处不再叙述。9-5 为保险基金管理自定义菜单命令和自定义命令按钮指定宏 为了在执行自定义菜单【保险基金管理】中的【职工保险基金管理】菜单命令，或单击【合同保险基金管理】自定义工具栏中的【职工保险基金管理】命令按钮时，打开职工保险基金管理窗体，需要为菜单命令和自定义命令按钮指定宏，宏代码如下所示，它保存在一个名为“职工保险管理程序”的标准模块中：Public Sub 职工保险基金管理窗口()mySearchItems=保险基金管理 职工保险基金管理.Show End Sub 335