通过表单上的多个组合框控制Access SQL查询 [英] Controlling an Access SQL query from multiple combo boxes on a form

问题描述

在各种半相关的教程中，我试图弄清楚如何将驻留在表单上的组合框字段发送到查询中，这使我陷入了混乱.

I've got myself in a right mess following all kinds of semi-relevant tutorials trying to figure out how to send combo box fields residing on a form to a query.

我正在使用查询生成联系人详细信息的子集，从中可以为邮寄广告系列制作标签.我希望组合框为null或结合使用，因此我可以根据选择的组合进行过滤. 我已经使组合框进行了查找以检索其值，并找到了有关使用IS NULL的很好的教程，但是我不了解VBA必需创建查看结果"和清除表单"命令按钮.

I'm using the query to generate subsets of contact details from which to make labels for mailshot campaigns. I'd like the comboboxes to be either null or used in conjunction, so I can filter on combinations of choices. I've got my comboboxes doing a lookup to retrieve their values and found a good tutorial on working with IS NULL, but I don't understand the VBA necessary create 'view results' and 'clear form' command buttons.

这些是查询字段条件中引用的我的组合框的名称:

These are the names of my comboboxes as referenced in the query fields criteria:

[Forms]![SearchForm]![cboStatus]
[Forms]![SearchForm]![cboNewsletter]

这些字段分别称为状态和描述.

These fields are called Status and Description respectively.

我正在过滤的查询称为

qryFilter

这些是我的命令"按钮的名称:

These are the names of my Command buttons:

cmdResults 
cmdClear

cmdResults应该将每个组合框值发送给查询，无论它们是否为null或已选择，但我无法使其正常工作，并且应该使组合框为null.

cmdResults should send each of the combo box values to the query whether they are null or selected, but I cant get it working and clear should make the comboboxes null.

我希望将其扩展为包括更多条件，但我希望它首先生效！

I'd be looking to expand this to include more criteria but I want to get it working first!

感谢您的帮助，在此先感谢您， 罗布

Any help is appreciated, thanks in advance, Rob

试图改编帕特里克(Patrick)的这段代码:

Tried to adapt this code from Patrick:

Private Sub cmdResults_Click()
 Dim tsSql As String
 tsSql = "SELECT * FROM qryAll WHERE "

If cboNewsletter <> "" And Not IsNull(cboNewsletter) Then
 tsSql = tsSql & "qryCorrespondence.NID = " & cboNewsletter & " "
    If (cboStatus <> "" And Not IsNull(cboStatus)) Then
    tsSql = tsSql & " AND "
    End If
 End If

If cboStatus <> "" And Not IsNull(cboStatus) Then
  tsSql = tsSql & "tblCustomers.Status = " & cboStatus & " "
End If

Dim rs As New ADODB.Recordset
rs.Open tsSql, CurrentProject.AccessConnection, 3, 3
End Sub

尽管最后一行给我一个错误，但它在调试器中突出显示了以下行:

The last line gives me an error though, it highlights the following line in the debugger:

    rs.open tsSql, CurrentProject.AccessConnection, 3, 3,

并在WHERE子句中指出语法错误

And says syntax error in WHERE clause

有什么建议吗?

推荐答案

这是按表格查询"界面的典型情况.我的做法是在窗体的模块中有一个编写WHERE子句的子例程，如下所示:

This is a typical situation for a Query By Form interface. My practice is to have a subroutine in the form's module that writes the WHERE clause, something like this:

  Private Function GetWhere() As String
    Dim strTemp As String

    If Not IsNull(Me!cboStatus) Then
       strTemp = strTemp & " AND tblCustomers.Status = " & Chr(34) & Me!cmbStatus & Chr(34)
    End If
    If Not IsNull(Me!cboNewsletter) Then
       strTemp = strTemp & " AND qryCorrespondence.NID = " & Chr(34) & Me!cboNewsletter & Chr(34)
    End If
    strTemp = Mid(strTemp, 6)
    If Len(strTemp) > 0 Then
       GetWhere = " WHERE " & strTemp
    End If
  End Function

(上面的代码假定qryAll的SELECT语句中有tblCustomers.Status和qryCorrespondence.NID可用)

(the code above assumes that tblCustomers.Status and qryCorrespondence.NID are available in the SELECT statement of qryAll)

在cmdResults的OnClick()事件中，您可以通过以下方式使用它:

In the OnClick() event of cmdResults, you would use it this way:

  Dim strSQL As String

  strSQL = "SELECT * FROM qryAll"
  strSQL = strSQL & GetWhere()

  [do with strSQL whatever you want]

如果两个组合框均未选择值，则上面的代码将返回所有记录.

The above code would return all records if neither combo box has a value selected.

对于您的cmbClear，您只有将两个组合框设置为Null的代码.

For your cmbClear, you'd just have code that sets the two combo boxes to Null.

这篇关于通过表单上的多个组合框控制Access SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！