access-VBA编程 第四章 数据输入、查询、计算、连接2

时　间:2009-02-17 08:29:18
作　者:UMVsoft整理   ID：1445  城市：上海
摘　要:VBA
正　文:

第四章 数据输入、查询、计算、连接2

为列表框定数据源
Dim str3 As String
str3 = "Select jhd_mx_jiage.wp_leibie AS 类别, jhd_mx_jiage.wp_migceg AS 名称, jhd_mx_jiage.wp_xighao AS 型号, jhd_mx_jiage.jhmx_danwei AS 单位, jhd_mx_jiage.jhmx_danjia AS 单价FROM jhd_mx_jiage " & " where jhd_mx_jiage.wp_leibie='" & Listjhlb & "'"
Me.Listjhwp.RowSource = str3
Me.Listjhwp.Requery
为组合框、子窗体设置数据源
下面的示例将组合框的 RowSourceType 属性设为“Table/Query”，然后将 RowSource 属性设为“雇员列表”查询。
Forms!Employees!cmboNames.RowSourceType = "Table/Query"
Forms!Employees!cmboNames.RowSource = "EmployeeList"
一：
Dim str1 As String
str1 = "Select ziyuag.zy_daihao, ziyuag.zy_mima,ziyuag.zy_ziwu,ziyuag.zy_xigmig FROM ziyuag " & " where zy_daihao='" & Text8dldh & "'and zy_mima='" & Text10dlmm & "'"
Me.Child6zy.Form.RecordSource = str1
Me.Child6zy.Requery
二：
子窗体.FORM.recordsourse="Select ziyuag.zy_daihao, ziyuag.zy_mima,ziyuag.zy_ziwu,ziyuag.zy_xigmig FROM ziyuag " & " where zy_daihao='" & Text8dldh & "'and zy_mima='" & Text10dlmm & "'"
三：
Private Sub Command38_Click()
Dim sjy As String
Dim pd As Integer
pd = True
sjy = "Select 病历明细表.* FROM 病历明细表"
If Not IsNull(Text0) Then
If pd Then
sjy = sjy & " where 姓名 like '" & Text0 & "'"
pd = False
Else
sjy = sjy & " and 姓名 like '" & Text0 & "'"
End If
End If
If Not IsNull(Text1) And Not IsNull(Text2) Then
sjy = sjy & " where 时间 between #" & Text1 & "# and #" & Text2 & "#"
pd = False
Else

str2 = str2 & " and 时间 between #" & Text1 & "# and #" & Text2 & "#"
End If
If Not IsNull(Text3) Then
If pd Then
sjy = sjy & " where 姓名 like '" & Text3 & "'"
pd = False
Else
sjy = sjy & " and 姓名 like '" & Text3 & "'"
End If
End If
Me.子窗体.RowSource = sjy
Me.Requery
End Sub
为主窗体、报表设数据源
使用RecordSource 属性可以指定窗体或报表的数据源。String 型，可读写。
一：
Dim sjy As String
sjy = "Select 名单.* FROM 名单" & " where 姓名 like '*" & List101 & "*'"
Me.RecordSource = sjy
Requery
二：
me.RecordSource = "名单"
用其他ACCESS的表作为本ACCESS 窗体的数据源
来源：ACCESS中国 Trynew
在Sql语句中的表名前加上数据库名就行了，下面语句动态引用当前目录的另一MDB文件的表做数据源：
Private Sub Form_Load()
Me.RecordSource = "Select 表1.* FROM [" & CurrentProject.Path & "\db1.mdb" & "].表1;"
End Sub
用VBA编程把Excel表中数据追加到Access表中
Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "temp", "c:\temp.xls", yes
End Sub
VB语句删除记录：
For I = 1 To 20
SQL = "Delete 订单明细ID FROM 订单明细 Where 订单明细ID=" & I
DoCmd.RunSQL SQL
Next I
或：
CurrentProject.Connection.Execute "Delete * FROM要删除记录的表"
插入／删除一条记录
新建：DoCmd.RunCommand acCmdRecordsGoToNew
删除：DoCmd.RunCommand acCmdDeleteRecord
清空表记录的方法
1、CurrentDb().Execute "delete * from 表名"
2、docmd.runsql "SQL语句"
3，RunSQL "Delete * From 表名"
用代码实现对数据修改或增加的取消
在窗体中修改数据时，关闭窗体，数据已经修改，这样很容易产生错误数据.
可采用如下方法解决:
在窗体更新前判断:
Private Sub FORM_BeforeUpdate(Cancel As Integer)
If MsgBox("保存吗?", vbYesNo, Me.Caption) <> vbYes Then
Cancel = True
End If
End Sub
' 去除系统的报错信息:
Private Sub FORM_Error(DataErr As Integer, Response As Integer)
Response = acDataErrContinue
End Sub
检查数据是否被修改，无则退出，有则询问是否保存
'在窗体的字段的“属性”“事件”“更新后”的右边输入“=NoAllowSave()”，
'在窗体的“打开”事件中代码“allowSave = False”
'定义模块
Option Compare Database
Option Explicit
Public allowSave As Boolean
Public Function NoAllowSave()
allowSave = True
End Function
“退出”按钮的单击事件代码
If allowSave = True Then
If MsgBox("当前数据已经被修改，是否保存？", vbYesNo + vbQuestion, "请选择...") = vbYes Then
Else
Me.Undo
End If
End If
DoCmd.Close
定义记录集
Dim rst As New ADODB.Recordset
打开记录集
rst.Open "Select 语句, 关键字 FROM 结果语句表", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
两子窗体之间字段赋值：
Forms!aaa!bbb.Form!bb = Forms!aaa!ccc.Form!cc
确定所显示的当前记录的记录编号。
下面的示例显示如何使用 Currentrecord 属性来确定所显示的当前记录的记录编号。在通用过程Currentformrecord 中将当前记录的编号值赋给变量 Lngrecordnum。
Sub CurrentFormRecord(frm As Form)
Dim lngrecordnum As Long
lngrecordnum = frm.CurrentRecord 'CurrentRecord是当前记录号
End Sub
读取最后一条记录
dlast("字段名","表名")
在字段默认值中用此函数能使该字段的新纪录显示上一条记录该字段的值

上一节 下一节