 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / May 2008Multi list box for a chart
I have created a multi list box for a report and I can get the report to run with the items selected in the multi list box. I cannot however get the code to do the same thing for a chart. Is there something basic I am missing or will the code need to be totally re-written? I am extremely new to working with Visual Basic. I expect you are using code that loops through the selected items from the list box to build a where condition for your report. A chart control is similar to a subreport in that you can't easily apply a filter to its Row Source property other than Link Master/Child. I usually base the Chart on a saved query. This allows me to use a little DAO code to update the saved query's SQL property prior to opening the report. Assuming your chart's Row Source is based on the query "qtotDeptEmps". Dim strSQL as String strSQL = "SELECT Dept, Count(EmployeeID) As NumEmps " & _ "FROM tblEmployees " & _ "WHERE " & strWhereFromListBoxItems & " " & _ "GROUP BY Dept " & _ "ORDER BY Dept;" CurrentDb.QueryDefs("qtotDeptEmps").SQL = strSQL  SignatureDuane Hookom Microsoft Access MVP > I have created a multi list box for a report and I can get the report to run > with the items selected in the multi list box. I cannot however get the code > to do the same thing for a chart. Is there something basic I am missing or > will the code need to be totally re-written? I am extremely new to working > with Visual Basic. I have a similar problem. In my case, the multilist box is combined with another field and is used in a stored query. The query then drives the chart report. When I run the stored query manually, it prompts for the two values and runs fine. When I run the chart/report manually, I get an error the the field(s) from the multilist field is not recognized. I suspect that is why running the code results in nothing. I do something nearly identical in a standard (non-chart) report and that works fine. Is there something unique about a chart that the logic is different? Thanks. > I expect you are using code that loops through the selected items from the > list box to build a where condition for your report. A chart control is [quoted text clipped - 20 lines] > > will the code need to be totally re-written? I am extremely new to working > > with Visual Basic. "it prompts for the two values" IMHO don't ever use parameter prompts http://www.tek-tips.com/faqs.cfm?fid=6763. I don't know how you are using the multi-select list box in your chart row source. SignatureDuane Hookom Microsoft Access MVP > I have a similar problem. In my case, the multilist box is combined with > another field and is used in a stored query. The query then drives the chart [quoted text clipped - 35 lines] > > > will the code need to be totally re-written? I am extremely new to working > > > with Visual Basic. I am using a form to provide the parameters to the query as your website suggests. When I mentioned that the two values were prompted, that was during my testing/debugging and running the query "manually" rather than via the form and code process. Ultimately, the end-user will be using the form to provide the values. Essentially, my flow is to use the form to provide the values to a stored query which pulls and summarizes the data, then feeds the chart report. I use this process with other standard reports, but for some reason it does not seem to work with a chart. When used with a chart, I get the error I mentioned earlier. Thanks. > "it prompts for the two values" IMHO don't ever use parameter prompts > http://www.tek-tips.com/faqs.cfm?fid=6763. [quoted text clipped - 41 lines] > > > > will the code need to be totally re-written? I am extremely new to working > > > > with Visual Basic. Tell us about the Row Source of your chart control. Does it have a reference to a control on a form? Is the Row Source a crosstab query? Have you set any Query->Parameters in your source? SignatureDuane Hookom Microsoft Access MVP > I am using a form to provide the parameters to the query as your website > suggests. When I mentioned that the two values were prompted, that was [quoted text clipped - 55 lines] > > > > > will the code need to be totally re-written? I am extremely new to working > > > > > with Visual Basic. The Chart Row Source statement is: TRANSFORM Sum([qryStrongly Agree Chart].[Count Of Survey]) AS [SumOfCount Of Survey] SELECT [qryStrongly Agree Chart].QACode AS Expr1 FROM [qryStrongly Agree Chart] GROUP BY [qryStrongly Agree Chart].QACode PIVOT [qryStrongly Agree Chart].YrQtr; The Row Source of the chart was created by the report/chart creation wizard. All references to the controls on the form are in the stored query (qryStrongly Agree Chart) which is collecting and summarizing the data. One of those controls is a MultiList box, the other is a combobox control. The statement for the stored query is: SELECT DISTINCTROW Year(Survey.SurveyDate)*4+DatePart('q',Survey.SurveyDate)-1 AS Expr1, TargetAreas.TargetParent, TargetAreas.TargetDesc, [SurveyYear] & "Q" & [SurveyQuarter] AS YrQtr, "Q" & CStr([QuestCode]) & "A" & CStr([AnswerCode]) AS QACode, Format$(Survey.SurveyDate,'\Qq yyyy') AS [SurveyDate By Quarter], Survey.SurveyArea, Survey.SurveyQuarter, Survey.SurveyYear, Survey.QuestCode, Survey.AnswerCode, Count(*) AS [Count Of Survey] FROM TargetAreas INNER JOIN Survey ON TargetAreas.TargetNo = Survey.SurveyArea GROUP BY Year(Survey.SurveyDate)*4+DatePart('q',Survey.SurveyDate)-1, TargetAreas.TargetParent, TargetAreas.TargetDesc, [SurveyYear] & "Q" & [SurveyQuarter], "Q" & CStr([QuestCode]) & "A" & CStr([AnswerCode]), Format$(Survey.SurveyDate,'\Qq yyyy'), Survey.SurveyArea, Survey.SurveyQuarter, Survey.SurveyYear, Survey.QuestCode, Survey.AnswerCode HAVING (((TargetAreas.TargetParent)=[Forms].[Chart Report Criteria].[USurvArea]) AND ((Survey.AnswerCode)=4) AND ((Year([Survey].[SurveyDate])*4+DatePart('q',[Survey].[SurveyDate])-1)>=(Year([forms].[Chart Report Criteria].[USurvYr])*4+(DatePart('q',Now())-1)-1)-7)) ORDER BY [SurveyYear] & "Q" & [SurveyQuarter], Survey.SurveyArea, Survey.QuestCode, Survey.AnswerCode; If I run the stored query "manually", I get the prompting for both of the form controls and the query runs fine. When I run the chart "manually", I get error messages that the Microsoft Jet database engine does not recognize the form controls as valid field names. When I click OK on that message, another pops up about sending data to the OLE Server. When I click OK there, I get a blank chart, but I do get page headings. I get the same results when running thru code. Hopefully, this will help. Thanks. > Tell us about the Row Source of your chart control. Does it have a reference > to a control on a form? Is the Row Source a crosstab query? Have you set any [quoted text clipped - 59 lines] > > > > > > will the code need to be totally re-written? I am extremely new to working > > > > > > with Visual Basic. The chart's Row Source is a crosstab query. All crosstab queries must have the Query->Parameters data types set. You can do this either in the crosstab or better yet in [qryStrongly Agree Chart]. Select Query->Parameters and enter: [Forms].[Chart Report Criteria].[USurvArea] ?datatype [forms].[Chart Report Criteria].[USurvYr] ?datatype SignatureDuane Hookom Microsoft Access MVP > The Chart Row Source statement is: > [quoted text clipped - 107 lines] > > > > > > > will the code need to be totally re-written? I am extremely new to working > > > > > > > with Visual Basic. Thank you very much for the help so far. I did as you suggested and put the Query Parameters in the stored query. Now, when I run the stored query "manually" I get the prompts for the two fields, as expected. I did notice that the stored query now has the following line added to the front of the SQL: PARAMETERS [Forms].[Chart Report Criteria].[USurvArea] Text ( 255 ), [Forms].[Chart Report Criteria].[USurvYr] Short; I still have the parameters as field criteria in the stored query (qryStrongly Agree Chart). When I run the report "manually", I get the same two field prompts, but they are repeated--same names each twice. I double-checked to make sure I did not add the Query Parameters to the crosstab and I did not. Only when running the report do I get the double prompts; running the stored query only shows one set of prompts. The odd thing is that if I enter values for only one set of prompts, I still get a blank report/chart. If I enter the same values into both sets of prompts, I get a complete report. Also, I see that when I close the Design View for the report, I am prompted (once) for these two fields. Thanks again. > The chart's Row Source is a crosstab query. All crosstab queries must have > the Query->Parameters data types set. You can do this either in the crosstab [quoted text clipped - 114 lines] > > > > > > > > will the code need to be totally re-written? I am extremely new to working > > > > > > > > with Visual Basic. I believe I have it nailed. I had to remove the field criteria that used one of the form controls. Doing so does not allow the report to be run "manually" as I like to do for testing, but it allows the report to run properly from the code. That is what is important to me now anyway. Thanks again for the help. > Thank you very much for the help so far. I did as you suggested and put the > Query Parameters in the stored query. Now, when I run the stored query [quoted text clipped - 141 lines] > > > > > > > > > will the code need to be totally re-written? I am extremely new to working > > > > > > > > > with Visual Basic. Thanks for your help. Where is the best place to insert the code? (Is it the "on-click" event of the command button in the form where the criteria is selected.) > I expect you are using code that loops through the selected items from the > list box to build a where condition for your report. A chart control is [quoted text clipped - 20 lines] > > will the code need to be totally re-written? I am extremely new to working > > with Visual Basic. You should run the code to change the SQL any time prior to opening the report. If you have a command button that opens the report, I would place it immediately prior to the DoCmd.OpenReport... SignatureDuane Hookom Microsoft Access MVP > Thanks for your help. Where is the best place to insert the code? (Is it the > "on-click" event of the command button in the form where the criteria is [quoted text clipped - 24 lines] > > > will the code need to be totally re-written? I am extremely new to working > > > with Visual Basic. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.