请参考:https://docs.microsoft.com/zh-cn/office/vba/api/overview/access
1.下述代码可以查询读者表里总记录条数
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
2.如果一个表两个字段分别是用户名和密码,想制作登陆界面,如下,登陆按钮代码:
If Me.txtPassword = DLookup("密码", "读者表", "用户名='" & Me.txtUserName & "'") Then
Me.Visible = False '隐藏登录窗体
DoCmd.OpenForm "管理系统界面" '打开主窗体
Else
MsgBox "密码不正确,请重新输入!", vbInformation '提示重新输入密码
Me.txtPassword.SetFocus '将焦点移到密码框
End If
3.关闭当前窗体,且打开另一个窗体
Private Sub Com_Btn_Click()
DoCmd.Close
DoCmd.OpenForm "主窗体"
End Sub
4.一个文本框,一个按钮,查询出相应内容,在文本框输入一个姓名,可以查询到该姓名对应的记录
Private Sub Btn_So_Click()
strNewRecord="select * from [读者表] where [姓名]='"& Me!Text0.value & "' "
Me.RecordSource=strNewRecord
End Sub
如果仅使登录的用户打开的窗体,看到的是自己的记录的话,需要在窗体间传递参数,可以这样实现,
第一,在数据库中创建一个模块,在模块中定义一个全局变量:
Public x$
第二,在登录成功的代码中为X赋值:
x=Me.user
第三,在将要打开的窗体的加载事件中设置新的记录源:
Me.RecordSource="select * from [读者表] where [姓名]='" & x & "'"
5.通过两个文本框向图书表中添加一条记录
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
If Me.Text2 <> "" And Me.Text4 <> "" Then
rs.AddNew
rs("编号") = Me.Text2
rs("书名") = Me.Text4
rs.Update
MsgBox "添加成功"
Else
MsgBox "编号或者书名没有填写"
End If
'释放系统数据集
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing