使用VBA在Access 2010中的窗体上显示记录集 [英] Displaying a recordset on a form in Access 2010 using VBA

问题描述

我在Access 2010中开发数据检索应用程序，用户通过选择列表框条目来选择要查看哪些表，列和行。 VBA代码根据这些选择生成一个SQL语句，然后从中创建一个ADBDB.Recordset对象。

如何在Access中显示记录集记录？没有任何网格控件在Access 2010中工作，并且该子窗体并不是为此目的而设计的。有人可以推荐另一种策略吗？您可以将SELECT语句另存为命名查询，然后以数据表的形式打开查询。它不是一个真正的表单，但有点像表格。

 调用DatasheetFromSql（strSql）
 
公共Sub DatasheetFromSql（ByVal pSql As String）
 Const cstrQuery As String =qryDiscardMe
 Dim db As DAO.Database 
 Dim qdf As DAO.QueryDef 
 Dim strMsg As String 
 
 On Error GoTo ErrorHandler 
 
 Set db = CurrentDb 
 db.QueryDefs.Delete cstrQuery 
 Set qdf = db.CreateQueryDef（cstrQuery，pSql）
 DoCmd.OpenQuery cstrQuery，acReadOnly 
 
 ExitHere：
 On Error GoTo 0 
 Set qdf = Nothing 
 Set db = Nothing 
 Exit Sub 
 
 ErrorHandler：
选择案例错误编号
案例3265'在此集合中找不到项目。 '
恢复下一个
案例否
 strMsg =错误& Err.Number& 程序中的（& Err.description _ $ b $&）DatasheetFromSql
 MsgBox strMsg 
 GoTo ExitHere 
 End Select 
 End Sub 
  

我以只读方式打开查询。如果你想允许用户编辑自定义查询返回的数据，我不会推荐这种方法。相反，我会投入HK1提供的方法，因为它可以更好地控制用户数据变化。

将查询打开为数据表，您可以使用Screen.ActiveDatasheet检查其属性。至少有一些方法也适用于您。例如，您可以调整/重新定位数据表，如下所示：

  Screen.ActiveDatasheet.Move Left：= 0，Top：= 0 ，Width：=（4 * 1440），Height：=（3 * 1440）
  

缇（1440缇/英寸），使宽度为4英寸，高3英寸，并将其移动到Access窗口的左上角。

I'm developing a data retrieval application in Access 2010 in which the user chooses which table, columns, and rows to view by selecting listbox entries. The VBA code generates a SQL statement from these choices and then creates an ADBDB.Recordset object from this.

How can I display the recordset records in Access? None of the grid controls work in Access 2010 and the subform just isn't designed for this purpose. Can someone recommend another strategy?

解决方案

You could save the SELECT statement as a named query, then open the query as a datasheet. It's not really a form, but somewhat form-like.

Call DatasheetFromSql(strSql)

Public Sub DatasheetFromSql(ByVal pSql As String)
    Const cstrQuery As String = "qryDiscardMe"
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strMsg As String

On Error GoTo ErrorHandler

    Set db = CurrentDb
    db.QueryDefs.Delete cstrQuery
    Set qdf = db.CreateQueryDef(cstrQuery, pSql)
    DoCmd.OpenQuery cstrQuery, , acReadOnly

ExitHere:
    On Error GoTo 0
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 3265 ' Item not found in this collection. '
        Resume Next
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.description _
            & ") in procedure DatasheetFromSql"
        MsgBox strMsg
        GoTo ExitHere
    End Select
End Sub

I opened the query read-only. If you want to allow users to edit the data returned by their custom queries, I would not recommend this approach. Instead I would invest the effort in the approach HK1 offered because it can support better control of the user data changes.

With the query opened as a datasheet, you can use Screen.ActiveDatasheet to inspect its properties. At least some of the methods are also available to you. For example you could resize/reposition the datasheet like this:

Screen.ActiveDatasheet.Move Left:=0, Top:=0, Width:=(4 * 1440), Height:=(3 * 1440)

The units are twips (1440 twips / inch), so that would make the width 4 in., height 3 in., and move it to the upper left corner of the Access window.

这篇关于使用VBA在Access 2010中的窗体上显示记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！