带参数的交叉表出报表

时　间:2019-07-27 13:01:58
作　者:罗勇胜   ID：71482  城市：佛山
摘　要:带参数的交叉表出报表，字段不确定，通过参数赋值，打开recordset，从recordset取字段名来获得解决的一个方法。
正　文:

问   题:

带参数的交叉表出报表，字段不确定，原来使用querydef取字段名的方法似乎行不通。

解   决:

参数赋值，打开recordset，从recordset取字段名，然后绑定

Private Sub Report_Open(cancel As Integer)

    If Not isDebug Then
        On Error Resume Next
    End If
  
    Me.RecordSource = "日报气进出及其钢瓶和余气按日记"
    
    Dim db As DAO.Database
    Dim q As QueryDef

    Set db = CurrentDb
     'Between [Forms]![窗体起始日期和客户条件输入]![txtStartDate] And [Forms]![窗体起始日期和客户条件输入]![txtEndDate]
     
    Set q = db.QueryDefs(Me.RecordSource)
    q.Parameters("[Forms]![窗体起始日期和客户条件输入]![txtStartDate]") = [Forms]![窗体起始日期和客户条件输入]![txtStartDate]
    q.Parameters("[Forms]![窗体起始日期和客户条件输入]![txtEndDate]") = [Forms]![窗体起始日期和客户条件输入]![txtEndDate]
     
    Dim r As Recordset
    Set r = q.OpenRecordset
    Dim i As Integer
    For i = r.Fields.Count - 1 To 0 Step -1
       Select Case r.Fields(i).Name
            Case "进50KG液相"
                进50KG液相.ControlSource = "进50KG液相"
            Case "进50KG气相"
                进50KG气相.ControlSource = "进50KG气相"
            Case "进15KG钢瓶"
                进15KG钢瓶.ControlSource = "进15KG钢瓶"
            Case "进5KG钢瓶"
                进5KG钢瓶.ControlSource = "进5KG钢瓶"
            Case "进2KG钢瓶"
                进2KG钢瓶.ControlSource = "进2KG钢瓶"
            Case "出50KG液相"
                出50KG液相.ControlSource = "出50KG液相"
            Case "出50KG气相"
                出50KG气相.ControlSource = "出50KG气相"
            Case "出15KG钢瓶"
                出15KG钢瓶.ControlSource = "出15KG钢瓶"
            Case "出5KG钢瓶"
                出5KG钢瓶.ControlSource = "出5KG钢瓶"
            Case "出2KG钢瓶"
                出2KG钢瓶.ControlSource = "出2KG钢瓶"
            Case Else
       End Select
       Next i
    Set q = Nothing
    r.Close
    Set r = Nothing
End Sub