最近为交叉报表的问题苦恼,记得以前有人发过一个动态设置的,找不到了,思路是设置足够多的字段,然后用代码设置标签和字段数据源,然后隐藏不需要显示的。
我按此思路做了个例子,不知道有没有更好的办法~ 如果有的话,欢迎跟帖!~
主要代码如下:
Private Sub Command0_Click()
On Error GoTo Err_Command0_Click
Dim FieldCount As Integer '字段数
FieldCount = CurrentDb.QueryDefs("score_交叉查询").Fields.Count
Dim stDocName As String '报表名称
Dim Lb As Label
Dim txt As TextBox
stDocName = "score_交叉报表"
DoCmd.OpenReport stDocName, acViewDesign
'-------------------------------------------------------------------------------------------------------------------------------------
'设置报表标签和数据源
Dim i As Integer
For i = 1 To FieldCount - 1
Reports("SCORE_交叉报表").Controls("Label" & i).Caption = CurrentDb.QueryDefs("score_交叉查询").Fields(i).Name '设置标签
Reports("SCORE_交叉报表").Controls("TXT" & i).ControlSource = CurrentDb.QueryDefs("score_交叉查询").Fields(i).Name '更改数据源
Next
'-------------------------------------------------------------------------------------------------------------------------------------
'将没有用到的控件隐藏
Dim n As Integer
Dim INTcount As Integer
INTcount = 9 - FieldCount '9是此报表预先准备的字段数量
For n = 1 To 9
If n < FieldCount Then
Reports("SCORE_交叉报表").Controls("Label" & n).Visible = True
Reports("SCORE_交叉报表").Controls("txt" & n).Visible = True
Else
Reports("SCORE_交叉报表").Controls("Label" & n).Visible = False
Reports("SCORE_交叉报表").Controls("txt" & n).Visible = False
End If
Next
'-----------------------------------------------------------------------------------------------------
DoCmd.Close acReport, "score_交叉报表", acSaveYes
DoCmd.OpenReport "score_交叉报表", acViewPreview
Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click
End Sub
附件点击下载此