获取数据按钮的代码- Private Sub CommandButton2_Click()
- Dim cnn As Object, rs As Object, sql$, i&, m&, c As Range
- If Len(Range("b3").Value) = 0 Then MsgBox " 请在 B3 单元可输入要查询的姓名", vbCritical + vbOKOnly: Exit Sub
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Application.EnableEvents = False
- Set cnn = CreateObject("adodb.connection")
- Set rs = CreateObject("adodb.recordset")
- cnn.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.Path & "\gkzp.mdb;Jet OLEDB:Database Password=695360052"
- sql = "select 性别,出生日期,报名序号,籍贯,民族,政治面貌,报名时间,健康状况,参加工作时间,最高学历毕业时间,报考类别,移动电话,固定电话,操作员,身份证号,电子邮箱,现工作单位及职务(或家庭住址),报考职位,全日制教育,全日制教育毕业院校及专业,在职教育,在职教育毕业院校及专业,学习工作简历,提供材料,资格审查情况 from gkzp where 姓名='" & [b3] & "'"
- rs.Open sql, cnn, 1, 3
- If rs.RecordCount > 0 Then
- If rs.RecordCount > 1 Then
- ' MsgBox Target.Value & "共有" & rs.RecordCount & "条记录", vbInformation
- For i = 1 To rs.RecordCount
- m = 0
- For Each c In Range("F3,J3,L3,B4,F4,J4,L4,B5,F5,J5,L5,C6,J6,L6,C7,J7,E8,E9,D10,I10,D11,I11,B12,B17,B22")
- c.Value = rs.Fields(m).Value
- m = m + 1
- Next
- Call 取消控件选择
- Call 数据值到控件([b17] & "," & [b22]): [b17] = "": [b22] = ""
- If i < rs.RecordCount Then
- If MsgBox("共有" & rs.RecordCount & "条记录,这是第" & i & "条记录,单击“是”显示下一条,单击“否”退出程序。", vbInformation + vbYesNo, [b3] & "共有" & rs.RecordCount & "条记录") = vbNo Then Exit For
- End If
- rs.MoveNext
- Next
- Else
- For Each c In Range("F3,J3,L3,B4,F4,J4,L4,B5,F5,J5,L5,C6,J6,L6,C7,J7,E8,E9,D10,I10,D11,I11,B12,B17,B22")
- c.Value = rs.Fields(m).Value
- m = m + 1
- Next
- Call 数据值到控件([b17] & "," & [b22]): [b17] = "": [b22] = ""
- MsgBox "呵呵!获取成功!" '提示可要可不要
- End If
- Else
- Range("F3,J3,L3,B4,F4,J4,L4,B5,F5,J5,L5,C6,J6,L6,C7,J7,E8,E9,D10,I10,D11,I11,B12") = ""
- End If
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
- Application.EnableEvents = True
- rs.Close
- Set rs = Nothing
- cnn.Close
- Set cnn = Nothing
- End Sub
复制代码 |