If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
dynamic crosstab report
I've posted a question on form coding, but it's probably the wrong group. At
this point, I'm trying to create a dynamic crosstab report using code I found in "Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. My first four column are static, but the next 6 need to be dynamic (e.g., display 1 or many). Following is the code which gets "stuck" in the following line - Set rst = CurrentDb.OpenRecordset(Me.RecordSource). Don't know if the rest would work, because I don't get very far. Can anyone please help: Option Compare Database Option Explicit Private Sub Report_Open(Cancel As Integer) Dim rst As DAO.Recordset Dim intFields As Integer Dim intControls As Integer Dim N As Integer 'Open a recordset for the crosstab query. Set rst = CurrentDb.OpenRecordset(Me.RecordSource) ' Find the number of text boxes available in the Detail section, minus 4 because we don't count the first 4 row header controls. intControls = Me.Detail.Controls.Count - 4 ' Find the number of fields, minus 4, because we don't count the first 4 row header filed. intFields = rst.Fields.Count - 4 ' We can't use more than intControls number of fields. If intFields intControls Then intFields = intControls End If ' Iterate through report fields to set label captions and field control sources. For N = 1 To intControls If N = intFields Then Me.Controls("Label" & N).Caption = rst.Fields(N).Name Me.Controls("Field" & N).ControlSource = rst.Fields(N).Name Else 'Hide extra controls. Me.Controls("Label" & N).Visible = False Me.Controls("Field" & N).Visible = False End If Next N rst.Close End Sub I really need help. Once I have the dynamic report, I'll go back to the Multiselect list box questions which also gives me trouble. I'm in way over my head and need help. Thanks. |
#2
|
|||
|
|||
dynamic crosstab report
Hi Brigitte,
You are referring to me.recordsouce that assumes that you have placed a crosstabquery in your report. If you haven't placed a recordsource the rst doesn't know what to open. -- Maurice Ausum "Brigitte P" wrote: I've posted a question on form coding, but it's probably the wrong group. At this point, I'm trying to create a dynamic crosstab report using code I found in "Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. My first four column are static, but the next 6 need to be dynamic (e.g., display 1 or many). Following is the code which gets "stuck" in the following line - Set rst = CurrentDb.OpenRecordset(Me.RecordSource). Don't know if the rest would work, because I don't get very far. Can anyone please help: Option Compare Database Option Explicit Private Sub Report_Open(Cancel As Integer) Dim rst As DAO.Recordset Dim intFields As Integer Dim intControls As Integer Dim N As Integer 'Open a recordset for the crosstab query. Set rst = CurrentDb.OpenRecordset(Me.RecordSource) ' Find the number of text boxes available in the Detail section, minus 4 because we don't count the first 4 row header controls. intControls = Me.Detail.Controls.Count - 4 ' Find the number of fields, minus 4, because we don't count the first 4 row header filed. intFields = rst.Fields.Count - 4 ' We can't use more than intControls number of fields. If intFields intControls Then intFields = intControls End If ' Iterate through report fields to set label captions and field control sources. For N = 1 To intControls If N = intFields Then Me.Controls("Label" & N).Caption = rst.Fields(N).Name Me.Controls("Field" & N).ControlSource = rst.Fields(N).Name Else 'Hide extra controls. Me.Controls("Label" & N).Visible = False Me.Controls("Field" & N).Visible = False End If Next N rst.Close End Sub I really need help. Once I have the dynamic report, I'll go back to the Multiselect list box questions which also gives me trouble. I'm in way over my head and need help. Thanks. |
#3
|
|||
|
|||
dynamic crosstab report
On Jul 11, 7:32 am, Brigitte P
wrote: I've posted a question on form coding, but it's probably the wrong group. At this point, I'm trying to create a dynamic crosstab report using code I found in "Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. My first four column are static, but the next 6 need to be dynamic (e.g., display 1 or many). Following is the code which gets "stuck" in the following line - Set rst = CurrentDb.OpenRecordset(Me.RecordSource). Don't know if the rest would work, because I don't get very far. Can anyone please help: Option Compare Database Option Explicit Private Sub Report_Open(Cancel As Integer) Dim rst As DAO.Recordset Dim intFields As Integer Dim intControls As Integer Dim N As Integer 'Open a recordset for the crosstab query. Set rst = CurrentDb.OpenRecordset(Me.RecordSource) ' Find the number of text boxes available in the Detail section, minus 4 because we don't count the first 4 row header controls. intControls = Me.Detail.Controls.Count - 4 ' Find the number of fields, minus 4, because we don't count the first 4 row header filed. intFields = rst.Fields.Count - 4 ' We can't use more than intControls number of fields. If intFields intControls Then intFields = intControls End If ' Iterate through report fields to set label captions and field control sources. For N = 1 To intControls If N = intFields Then Me.Controls("Label" & N).Caption = rst.Fields(N).Name Me.Controls("Field" & N).ControlSource = rst.Fields(N).Name Else 'Hide extra controls. Me.Controls("Label" & N).Visible = False Me.Controls("Field" & N).Visible = False End If Next N rst.Close End Sub I really need help. Once I have the dynamic report, I'll go back to the Multiselect list box questions which also gives me trouble. I'm in way over my head and need help. Thanks. Add this line before the line that crashes: Debug.Print Me.RecordSource Please post what your recordsource is (query, table, SQL string) as how you would open it from code depends on this. -Kris |
#4
|
|||
|
|||
dynamic crosstab report
The debug line results in
qryEventsReportCard_Crosstab which is the right record source listed in the property window as RecordSource for the report. The crosstab runs just fine when I run it from the query window. "krissco" wrote: On Jul 11, 7:32 am, Brigitte P wrote: I've posted a question on form coding, but it's probably the wrong group. At this point, I'm trying to create a dynamic crosstab report using code I found in "Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. My first four column are static, but the next 6 need to be dynamic (e.g., display 1 or many). Following is the code which gets "stuck" in the following line - Set rst = CurrentDb.OpenRecordset(Me.RecordSource). Don't know if the rest would work, because I don't get very far. Can anyone please help: Option Compare Database Option Explicit Private Sub Report_Open(Cancel As Integer) Dim rst As DAO.Recordset Dim intFields As Integer Dim intControls As Integer Dim N As Integer 'Open a recordset for the crosstab query. Set rst = CurrentDb.OpenRecordset(Me.RecordSource) ' Find the number of text boxes available in the Detail section, minus 4 because we don't count the first 4 row header controls. intControls = Me.Detail.Controls.Count - 4 ' Find the number of fields, minus 4, because we don't count the first 4 row header filed. intFields = rst.Fields.Count - 4 ' We can't use more than intControls number of fields. If intFields intControls Then intFields = intControls End If ' Iterate through report fields to set label captions and field control sources. For N = 1 To intControls If N = intFields Then Me.Controls("Label" & N).Caption = rst.Fields(N).Name Me.Controls("Field" & N).ControlSource = rst.Fields(N).Name Else 'Hide extra controls. Me.Controls("Label" & N).Visible = False Me.Controls("Field" & N).Visible = False End If Next N rst.Close End Sub I really need help. Once I have the dynamic report, I'll go back to the Multiselect list box questions which also gives me trouble. I'm in way over my head and need help. Thanks. Add this line before the line that crashes: Debug.Print Me.RecordSource Please post what your recordsource is (query, table, SQL string) as how you would open it from code depends on this. -Kris |
#5
|
|||
|
|||
dynamic crosstab report
Here is the SQL of my underlying query since this seems to be the source of
the bug: PARAMETERS [Forms]![frmMultiselect]![txtUnit] Text ( 255 ), [Forms]![frmMultiselect]![txtEndDate] DateTime, [Forms]![frmMultiselect]![txtStartDate] DateTime; TRANSFORM Count(qryEventsReportCard.EventEvent) AS CountOfEventEvent SELECT qryEventsReportCard.EventEvent, [SumOfSix]/6 AS [6 Mo Avg], [SumOfTwelve]/12 AS [12 Mo Avg], qryEventsReportCard.EventBuilding, Count(qryEventsReportCard.EventEvent) AS [Total Of EventEvent] FROM qryEventsReportCard INNER JOIN qryEventReportCard12_6Sum ON (qryEventReportCard12_6Sum.EventBuilding = qryEventsReportCard.EventBuilding) AND (qryEventsReportCard.EventEvent = qryEventReportCard12_6Sum.EventEvent) WHERE (((qryEventsReportCard.EventDate) Between [Forms]![frmMultiselect]![txtStartDate] And [Forms]![frmMultiselect]![txtEndDate])) GROUP BY qryEventsReportCard.EventEvent, [SumOfSix]/6, [SumOfTwelve]/12, qryEventsReportCard.EventBuilding PIVOT qryEventsReportCard.MonthNumber; I have four static fields "krissco" wrote: On Jul 11, 7:32 am, Brigitte P wrote: I've posted a question on form coding, but it's probably the wrong group. At this point, I'm trying to create a dynamic crosstab report using code I found in "Fixing Access Annoyances" by Phil Mitchell and Evan Callahan. My first four column are static, but the next 6 need to be dynamic (e.g., display 1 or many). Following is the code which gets "stuck" in the following line - Set rst = CurrentDb.OpenRecordset(Me.RecordSource). Don't know if the rest would work, because I don't get very far. Can anyone please help: Option Compare Database Option Explicit Private Sub Report_Open(Cancel As Integer) Dim rst As DAO.Recordset Dim intFields As Integer Dim intControls As Integer Dim N As Integer 'Open a recordset for the crosstab query. Set rst = CurrentDb.OpenRecordset(Me.RecordSource) ' Find the number of text boxes available in the Detail section, minus 4 because we don't count the first 4 row header controls. intControls = Me.Detail.Controls.Count - 4 ' Find the number of fields, minus 4, because we don't count the first 4 row header filed. intFields = rst.Fields.Count - 4 ' We can't use more than intControls number of fields. If intFields intControls Then intFields = intControls End If ' Iterate through report fields to set label captions and field control sources. For N = 1 To intControls If N = intFields Then Me.Controls("Label" & N).Caption = rst.Fields(N).Name Me.Controls("Field" & N).ControlSource = rst.Fields(N).Name Else 'Hide extra controls. Me.Controls("Label" & N).Visible = False Me.Controls("Field" & N).Visible = False End If Next N rst.Close End Sub I really need help. Once I have the dynamic report, I'll go back to the Multiselect list box questions which also gives me trouble. I'm in way over my head and need help. Thanks. Add this line before the line that crashes: Debug.Print Me.RecordSource Please post what your recordsource is (query, table, SQL string) as how you would open it from code depends on this. -Kris |
#6
|
|||
|
|||
dynamic crosstab report
PARAMETERS [Forms]![frmMultiselect]![txtUnit] Text ( 255 ),
[Forms]![frmMultiselect]![txtEndDate] DateTime, [Forms]![frmMultiselect]![txtStartDate] DateTime; I'm not too familiar with DAO but I know that this would bomb in ADO. The problem is the parameters. Comment out these two lines: Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset(Me.RecordSource) Add in this stuff instead: 'ADO objects for dealing w/ parameters in queries Dim rst As ADODB.Recordset Dim cmd As New ADODB.Command Dim param1 As String, param2 As Date, param3 As Date 'Retreive params into variables (easier on the eyes) param1 = [Forms]![frmMultiselect]![txtUnit] param2 = [Forms]![frmMultiselect]![txtEndDate] param3 = [Forms]![frmMultiselect]![txtStartDate] 'Setup the command object Set cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "Select * from " & Me.RecordSource cmd.Parameters.Append cmd.CreateParameter("FirstParam", adDate, adParamInput, Len(param1), param1) cmd.Parameters.Append cmd.CreateParameter("SecondParam", adDate, adParamInput, , param2) cmd.Parameters.Append cmd.CreateParameter("ThirdParam", adDate, adParamInput, , param3) 'Get the recordset Set rst = cmd.Execute() Make sure you have a reference to ADO. You will want to add some cleanup code for the new objects too. I'm sure that this can be done w/ DAO - I'm just not very familiar w/ that technology. -Kris |
#7
|
|||
|
|||
dynamic crosstab report
Oops. I just noticed a type-o. The first adDate should be adChar
cmd.Parameters.Append cmd.CreateParameter("FirstParam", adDate, adParamInput, Len(param1), param1) -Kris |
#8
|
|||
|
|||
dynamic crosstab report
Thanks for trying to help. I don't know much about DAO and don't have ADO
available in my library selection. I tried just to include your recommendations into my existing code which, as expected, didn't work in the DAO version. Why the thing doesn't accept my query as a recordsource obviously has to do with the parameters, and Microsoft KB seems to have a solution that also didn't work for me. They try to accomodate so many possiblities that I got lost. It seems like those dynamic crosstabs reports puzzle many lay developers like me; I'll keep on searching. If all else fails, I let my user dump the query results into Excell and do a macro in Excel to write the report. Clunky, but maybe the only way I know how to do this. I'm telling my facility for a long time that they should hire a professional, maybe they listen to me this time Thanks for your efforts. Brigitte P. "krissco" wrote: Oops. I just noticed a type-o. The first adDate should be adChar cmd.Parameters.Append cmd.CreateParameter("FirstParam", adDate, adParamInput, Len(param1), param1) -Kris |
Thread Tools | |
Display Modes | |
|
|