数据库大作业常用技巧

1.如果一个表两个字段分别是用户名和密码，想制作登陆界面，如下，登陆按钮代码：

If Me.txtPassword = DLookup(“密码”, “读者表”, “用户名='” & Me.txtUserName & “‘”) Then

Me.Visible = False ‘隐藏登录窗体

‘strUserName = Me.cboUserID

DoCmd.OpenForm “管理系统界面” ‘打开主窗体

Else

MsgBox “密码不正确，请重新输入！”, vbInformation ‘提示重新输入密码

Me.txtPassword.SetFocus ‘将焦点移到密码框

End If

2.关闭当前窗体，且打开另一个窗体

Private Sub Com_Btn_Click()

DoCmd.Close

DoCmd.OpenForm “主窗体”

End Sub

3.一个文本框，一个按钮，查询出相应内容，在文本框输入一个姓名，可以查询到该姓名对应的记录

Private Sub Btn_So_Click()

strNewRecord=”select * from [读者表] where [姓名]=’ ” & Me!Text0.value& ” ‘ ”

Me.RecordSource=strNewRecord

End Sub

4.下述代码可以查询读者表里总记录条数

Dim cnn As Object

Dim rs As Object

Set cn = CreateObject(“ADODB.Connection”)

Set rs = CreateObject(“ADODB.Recordset”)

Set cnn = CurrentProject.Connection

rs.Open “select * from 读者表”, cnn, 1, 3

Debug.Print “教师共” & rs.RecordCount ‘在立即窗口中显示结果

rs.Filter = “文化程度=’高中’ ” ‘添加了筛选条件

Do Until rs.EOF

Debug.Print rs(“姓名”), rs(“文化程度”)

rs.MoveNext

Loop

Debug.Print “男教师共” & rs.RecordCount ‘在立即窗口中显示结果

rs.Close

cnn.Close

Set rs = Nothing

Set cnn = Nothing