错误的RecordCount与SQL视图数据源过滤表上 [英] Wrong RecordCount on Filtered Form with SQL View DataSource

问题描述

我使用的是 Access2010项目作为前端，指的是 MS SQL Server 2008的作为后端。在我的Access项目存在形式frmKlientenÜbersicht。这种形式有一个视图abfKlientenÜbersicht作为数据源。

I am using an Access2010 project as frontend, referring to a MS SQL Server 2008 as backend. Within my Access project there is form frmKlientenÜbersicht. This form has a view abfKlientenÜbersicht as dataSource.

现在我想用这个code得到的记录显示在我的表格了当前数量：

Now I am trying to get the current number of records showing up in my form by using this code:

Private Sub Form_Current()
    Debug.Print "Form_Current: " & anzahlDatensätze
    lblAnzahlDatensätze.Caption = anzahlDatensätze & " Klient(en)"
End Sub

Private Function anzahlDatensätze() As Integer
    Dim rs As Recordset
    Set rs = Me.RecordsetClone
    rs.MoveLast
    anzahlDatensätze = rs.RecordCount
End Function

这正常工作，直到我使用一些过滤器。如果我的使用任何过滤器在我的形式，在多项纪录保持不变！

This works fine until I am using some filters. If I am using any filter on my form, the number of records stays unchanged!

• 在我有什么改变来获得当前记录数显示出来（如过滤或没有）？
• 什么是为什么我的code没有显示正确的记录数的原因
？

编辑：根据给定的意见，并回答我试着设置计数（[pkKlient] 到一个文本框（见新图），并试图 DCOUNT （*，abfKlientenÜbersicht，me.Filter）从内部VBA code。

According to the given comments and answers I tried setting Count([pkKlient] onto a textbox (see new pic) and tried DCount("*", "abfKlientenÜbersicht", me.Filter) from within VBA Code.

很遗憾，似乎在 filterClause无效在使用它作为DCOUNT参数值。 （见PIC的filterClause）。

Unfortunatelly it seems that the filterClause is not valid when using it as parameter value for DCount. (see pic for filterClause).

正如你可以看到计数（..）不会导致一个正确的数字 - 和filterClause（受访问产生的!! 的）似乎并没有被有效使用的 DCOUNT（..）

As you can see count(..) does not result in a correct number - and the filterClause (generated by access!!) seems not to be valid for use by DCount(..)

如果有人想尝试一下，在您自己，只是创建一个ADP，添加一个窗体，添加视图，表单数据源是一个视图，设置一个过滤器，并尝试获取的记录数？！

If someone wants to try it, on your own, just create an ADP, add a form, add a view, form dataSource is a view, set a filter, and try to get the number of records?!!

期待着任何意见/答案/提示！

推荐答案

一些注意事项，还有很多事情，可能与此出问题，就很难甚至被称为测试。然而，它返回正确的计数我。

Some notes, there are a dozen things that could go wrong with this, it could hardly even be called tested. However, it was returning the correct count for me.

显然，表格过滤器只是隐藏的记录，而这将适用于一个真正的过滤器。但是，你需要让格式转换成正确的形状为一个有效的过滤器。最后，一​​个WHERE语句可能会更容易。

Apparently, the form filter just hides records, whereas this will apply a real filter. However, you need to get the format into the right shape for a valid filter. In the end, a WHERE statement would probably be easier.

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

    With Me.Recordset
        ''Filter
        If ApplyType = 1 Then
            ''Very, very roughly. Remove form name, use single quotes
            ''You will need a lot more code for safety
            sfilter = Replace(Me.Filter, "[" & Me.Name & "].", "")
            sfilter = Replace(sfilter, """", "'")

            .Filter = sfilter 

            MsgBox "Recordset : " & Me.Recordset.RecordCount & vbCrLf _
            & "Filtered : " & .RecordCount

        Else
            ''Remove filter - ApplyType 0
            .Filter = ""
        End If

    End With

End Sub

其他注意到类似的情况

您还可以设置一个文本框的东西在这些线路上：

You can also set a textbox to something on these lines:

=IIf([FilterOn]=True,DCount("id","ATable",
 Replace(Replace([Filter],"[" & [Name] & "].",""),"""","'")),Count([id]))

（除去休息的线，这是化妆品）

(Remove the break in the line, it is cosmetic)

这篇关于错误的RecordCount与SQL视图数据源过滤表上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！