数据库设计
一、StuInfo 表
字段名称 数据类型 说明
stuNo 文本 工号
Name 文本 姓名
Sex 文本 性别
Tel 文本 电话
Specialty 文本 部门
Class 文本 职务
born 文本 出生年月
idNo 文本 身份证号
inTime 文本 入职时间
comeFrom 文本 来源地
二、SubInfo 表
字段名称 数据类型 说明
stuNo 文本 工号
Name 文本 姓名
Specialty 文本 部门
Class 文本 职务
Subject 文本 近 5 年销售收入
Score 数字 平均销售收入
panish 文本 处分
三、userinfo 表
字段名称 数据类型 说明
username 文本 用户名
password 文本 密码
panish 文本 处分
Access 数据库设计图
程序设计说明
一、程序代码
Option Explicit
Private Sub Command1_Click()
Dim SQL As String
Dim RS_LOGIN As New ADODB.Recordset
If Trim(Text1.Text) = "" Or Trim(Text2.Text) = "" Then
MsgBox "输入的用户或者密码不能为空,请重新输入!", vbOKOnly + vbExclamation
Text1.SetFocus
Else
SQL = "SELECT*FROM USERINFO WHERE USERNAME ='" & Text1.Text & "'"
RS_LOGIN.Open SQL, CONN, adOpenKeyset, adLockPessimistic
If RS_LOGIN.EOF = True Then
MsgBox "系统没有该用户,请重新输入!", vbOKOnly + vbExclamation
Text1.SetFocus
Else
If Trim(Text2.Text) = Trim(RS_LOGIN.Fields(1)) Then
USERNAME = Trim(Text1.Text)
Unload Me
MDIForm1.Show
Else
MsgBox "输入用户密码有误,请重新输入!", vbOKOnly + vbExclamation
Text2.Text = ""
Text2.SetFocus
End If
End If
End If
End Sub
Private Sub Command2_Click()
Unload Me
End Sub
Private Sub Form_Load()
Dim CONNECTIONSTRING As String
CONNECTIONSTRING = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;" & _
"DATA SOURCE=.\STUREPORT.MDB"
CONN.Open CONNECTIONSTRING
End Sub
Private Sub Command1_Click()
If Combo1.Text = "工号" Then
Adodc1.RecordSource = "select * from subinfo where stuno='" & Trim(Text1.Text) & "'"
Adodc1.Refresh
Exit Sub
ElseIf Combo1.Text = "姓名" Then
Adodc1.RecordSource = "select * from subinfo where name='" & Trim(Text1.Text) & "'"
Adodc1.Refresh
Exit Sub
ElseIf Combo1.Text = "处分" Then
Adodc1.RecordSource = "select * from panish where sex= '" & Trim(Text1.Text) & "'"
Adodc1.Refresh
Exit Sub
ElseIf Combo1.Text = "部门" Then
Adodc1.RecordSource = "select * from subinfo where specialty like '%" & Trim(Text1.Text) &
"%'"
Adodc1.Refresh
Exit Sub
ElseIf Combo1.Text = "职务" Then
Adodc1.RecordSource = "select * from subinfo where class like '%" & Trim(Text1.Text) & "%'"
Adodc1.Refresh
Exit Sub
ElseIf Combo1.Text = "近 5 年销售收入" Then
Adodc1.RecordSource = "select * from subinfo where subject like '%" & Trim(Text1.Text) & "%'"
Adodc1.Refresh
Exit Sub
ElseIf Combo1.Text = "平均销售收入" Then
Adodc1.RecordSource = "select * from subinfo where score like '%" & Trim(Text1.Text) & "%'"
Adodc1.Refresh
Exit Sub
End If
End Sub
Private Sub Command2_Click()
Unload Me
End Sub
Private Sub Form_Load()
Combo1.AddItem "工号"
Combo1.AddItem "姓名"
Combo1.AddItem "性别"
Combo1.AddItem "部门"
Combo1.AddItem "职务"
Combo1.AddItem "近 5 年销售收入"
Combo1.AddItem "平均销售收入"
End Sub
Private Sub Command1_Click()
Adodc1.Recordset.MoveFirst
End Sub
Private Sub Command2_Click()
Adodc1.Recordset.MovePrevious
Command2.Enabled = True
Command3.Enabled = True
If Adodc1.Recordset.BOF Then
Adodc1.Recordset.MoveFirst
Command2.Enabled = False
Command3.Enabled = True
End If
End Sub
Private Sub Command3_Click()
Adodc1.Recordset.MoveNext
Command2.Enabled = True
Command3.Enabled = True
If Adodc1.Recordset.EOF Then
Adodc1.Recordset.MoveLast
Command3.Enabled = False
Command2.Enabled = True
End If
End Sub
Private Sub Command4_Click()
Adodc1.Recordset.MoveLast
End Sub
Private Sub Command5_Click()
If Command5.Caption = "修改记录" Then
Command5.Caption = "保存记录"