MS Access在数据表中显示vba选择查询

Question

学习如何在ms access vba中使用SQL。我已经了解了select查询是如何工作的，但它们不会像通过access表单构建的那样立即输出到数据表。我需要复杂的，如果其他链，以获得我想要的，所以我必须使用vba路线。我尝试过其他问题中提出的几种方法，但它们并没有像我想要的那样奏效。

我正在为一个按钮创建代码，该按钮根据复选框显示带有特定元素的数据表。下面的代码将只显示Select的元素。

我尝试了.QueryDef方法

Dim qd As QueryDef
Set qd = CurrentDb.CreateQueryDef("")

With qd
    .ReturnsRecords = True
    .sql = "SELECT * FROM EXPORT_CERTIFICATION WHERE EXPORT_CERTIFICATION.CertificationStatus = 'Certified'"
End With

这是有效的，但只在我输入查询名称时才起作用，而不是在空的时候。它创建了一个新的查询，它可以做我想做的事情，但是之后按钮就什么都不做了。

我是否应该创建一个表来接受输出，并尝试将该表设置为由sql语句创建的记录集？我不想要另一张桌子，因为这只是用来看的。

下面是我当前尝试的更简单的解决方案的完整代码

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")

Answer 1

就个人而言，我使用以下代码来显示记录集。

与达伦的回答一样，我创建了一个表单，命名为frmDynDS，将默认视图设置为数据表视图，并使用以下代码向其添加了255个控件(窗体在设计视图中运行时运行)：

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

然后，我将以下代码添加到表单的模块中：

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

然后，我在一个公共模块中获得了以下代码：

Public Sub DisplayRS(rs As Object)
    Dim f As New Form_frmDynDS
    f.LoadRS rs
    f.Visible = True
    Set f.Myself = f
End Sub

在设置了所有这些之后，显示记录集非常简单。只需做以下几点：

DisplayRS CurrentDb.OpenRecordset("SELECT * FROM EXPORT_CERTIFICATION WHERE EXPORT_CERTIFICATION.CertificationStatus = 'Certified'")

这将打开窗体，使适当数量的控件可见，设置标题，调整单元格宽度以容纳标题，然后将控件绑定到记录集。窗体将一直保存到关闭，并且可以与此代码同时打开多个记录集。

请注意，在运行此代码时，不能在记录集中使用参数，因为它会在筛选/排序时崩溃。

Answer 2

我建议使用预先构建的表单和查询来实现您在这里试图达到的目的。

但是，说到这一点，您正在学习如何在VBA中使用SQL，下面是如何使用代码(其他方法可能是非常可能的)：

• 创建基本窗体并添加每个记录的正确控件数。
• 将窗体(在其他选项卡中)的Has Module属性设置为Yes。
• 将Default View设置为DataSheet。
• 将表单保存为MyForm。

添加一个VBA代码模块并添加以下代码：

Sub Test()

    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset
    Dim frm As Form_MyForm

    Set qd = CurrentDb.CreateQueryDef("", "PARAMETERS Stats Text(255); " & _
                                          "SELECT * FROM EXPORT_CERTIFICATION " & _
                                          "WHERE CertificationStatus = Stats")

    qd.Parameters("Stats") = "Certified"

    Set rs = qd.OpenRecordset

    Set frm = New Form_MyForm

    'The record source for the form
    Set frm.Recordset = rs

    'The record source fields attached to each control.
    frm.Text0 = "FieldA"
    frm.Text2 = "Field"
    frm.Text3 = "CertificationStatus"

    frm.Visible = True

    Debug.Assert False 'Form will disappear when code ends, so pause here.

End Sub

将出现包含记录集的窗体。