MS Access在数据表中显示VBA选择查询 [英] MS Access displaying vba select query in datasheet
问题描述
学习在ms Access vba中使用SQL.我已经知道选择查询是如何工作的，但它们不会立即输出到像通过访问表单构建的数据表那样的数据表.我需要其他复杂的链来获得想要的东西，所以我不得不使用vba路由. 我尝试了在其他问题中建议的几种方法，但这些方法并没有达到我想要的效果.
Learning to use SQL inside ms access vba. I've gotten how select queries work but they don't immediately output to a datasheet like one built through the access forms. I needed complicated if else chains to get what I wanted so I had to use the vba route. I've tried several methods that have been suggested in other questions but they haven't worked like I want.
我正在为按钮创建代码，该按钮显示基于复选框的带有特定元素的数据表.接下来的代码将仅用于显示Select查询的元素.
I'm creating the code for a button which displays a datasheet with particular elements based on check boxes. the code that follows will just be about displaying the elements of the Select query.
我尝试了.QueryDef方法
I tried the .QueryDef method
Dim qd As QueryDef
Set qd = CurrentDb.CreateQueryDef("")
With qd
.ReturnsRecords = True
.sql = "SELECT * FROM EXPORT_CERTIFICATION WHERE EXPORT_CERTIFICATION.CertificationStatus = 'Certified'"
End With
这有效，但是当我输入查询名称时只有一次，而当它为空时则无效.它创建了一个新查询，该查询可以执行我想要的操作，但是此后该按钮不执行任何操作.
This worked but only once when I put in a query name and not when empty. it created a new query which does what I want but then the button doesn't do anything after that.
我应该创建一个表来接受输出并尝试将该表设置为sql语句创建的记录集吗?我想避免有另一个表，因为这只是用于查看.
should I create a table to accept the output and try to set that table to the recordset created by the sql statement? i'd like to avoid having another table since this is just used for viewing.
这是我目前尝试一个更简单解决方案的完整代码
Here is the full code with my current try at a simpler solution
Private Sub NDC_CERT_VIEW_Click()
Dim StrSQLclause As String
Dim db1 As DAO.Database, qry1 As DAO.QueryDef
Set db1 = CurrentDb()
Set qry1 = db1.QueryDefs("NDC_EXPORT_VIEW")
MsgBox ("Here")
If (Certified_Check And Not Revised_Check And Not Doc_Exceptions_Check And Not Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' "
ElseIf (Not Certified_Check And Revised_Check And Not Doc_Exceptions_Check And Not Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' "
ElseIf (Not Certified_Check And Not Revised_Check And Doc_Exceptions_Check And Not Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null "
ElseIf (Not Certified_Check And Not Revised_Check And Not Doc_Exceptions_Check And Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = '' "
ElseIf (Certified_Check And Revised_Check And Not Doc_Exceptions_Check And Not Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' "
ElseIf (Not Certified_Check And Revised_Check And Doc_Exceptions_Check And Not Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null Or EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' "
ElseIf (Not Certified_Check And Not Revised_Check And Doc_Exceptions_Check And Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' "
ElseIf (Certified_Check And Not Revised_Check And Not Doc_Exceptions_Check And Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' "
ElseIf (Not Certified_Check And Revised_Check And Doc_Exceptions_Check And Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' Or EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null "
ElseIf (Certified_Check And Not Revised_Check And Doc_Exceptions_Check And Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' Or EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null "
ElseIf (Certified_Check And Revised_Check And Not Doc_Exceptions_Check And Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' "
ElseIf (Certified_Check And Revised_Check And Doc_Exceptions_Check And Not Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null Or EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' "
ElseIf (Certified_Check And Revised_Check And Doc_Exceptions_Check And Pending_Check) Then
StrSQLclause = "Select * From EXPORT_NDC_CERTIFICATION Where EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Certified' Or EXPORT_NDC_CERTIFICATION.CertificationStatus = 'Revised' Or EXPORT_NDC_CERTIFICATION.DocumentException Not Is Null Or EXPORT_NDC_CERTIFICATION.CertificationStatus = '' "
Else
MsgBox ("No Status Selected")
Exit Sub
End If
MsgBox (StrSQLclause)
MsgBox ("Here3")
qry1.sql = StrSQLclause
MsgBox ("Here4")
DoCmd.OpenQuery "NDC_EXPORT_VIEW"
MsgBox ("Here6")
推荐答案
我个人使用以下代码显示记录集.
Personally, I use the following code to display recordsets.
就像达伦的答案一样，我创建了一个名为frmDynDS的表单，默认视图设置为数据表视图，并使用以下代码向其中添加了255个控件(在表单处于设计状态时运行)视图):
Like Darren's answer, I have created a form, which I've named frmDynDS, with the default view set to datasheet view, and I've added 255 controls to it using the following code (run while the form is in design view):
Dim i As Long
Dim myCtl As Control
For i = 0 To 254
Set myCtl = Application.CreateControl("frmDynDS", acTextBox, acDetail)
myCtl.Name = "Text" & i
Next i
然后，我将以下代码添加到表单的模块中:
Then, I've added the following code to the form's module:
Public Myself As Object
Public Sub LoadRS(myRS As Object)
'Supports both ADODB and DAO recordsets
Dim i As Long
Dim myTextbox As textbox
Dim fld As Object
i = 0
With myRS
For Each fld In myRS.Fields
Set myTextbox = Me.Controls("Text" & i)
myTextbox.Properties("DatasheetCaption").Value = fld.Name
myTextbox.ControlSource = fld.Name
myTextbox.ColumnHidden = False
myTextbox.columnWidth = -2
i = i + 1
Next fld
End With
For i = i To 254
Set myTextbox = Me.Controls("Text" & i)
myTextbox.ColumnHidden = True
Next i
Set Me.Recordset = myRS
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set Myself = Nothing 'Prevent memory leak
End Sub
然后，我在公共模块中获得了以下代码:"
Then, I've got the following code in a public module:"
Public Sub DisplayRS(rs As Object)
Dim f As New Form_frmDynDS
f.LoadRS rs
f.Visible = True
Set f.Myself = f
End Sub
完成所有这些设置后，显示记录集非常简单.只需执行以下操作:
After you have all this set up, displaying recordsets is very simple. Just do the following:
DisplayRS CurrentDb.OpenRecordset("SELECT * FROM EXPORT_CERTIFICATION WHERE EXPORT_CERTIFICATION.CertificationStatus = 'Certified'")
这将打开表单，显示适当数量的控件，设置标题，调整单元格宽度以容纳标题，然后将控件绑定到记录集.该表单将一直保存到关闭，您可以使用此代码同时打开多个记录集.
This will open up the form, make the appropriate amount of controls visible, set the caption, adjust cell width to accommodate the caption, and then bind the controls to the recordset. The form will persist until closed, and you can open up multiple recordsets simultaneously with this code.
请注意，运行此代码时不能在记录集中使用参数，因为它将在过滤/排序时崩溃.
Do note that you can't use parameters in the recordset when running this code, as it will crash on filtering/sorting.
这篇关于MS Access在数据表中显示VBA选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
