PLEASE HELP: Refresh columns displayed in a report from a quer
in [Reports]
|
From: sam on 28 May 2010 18:17 Hi Marshall, Thanks for helping In the following code you gave: With Forms!theform Me.txtSelect1.ControlSource = .txtSelect1 . . . End With Me.txtSelect1.ControlSource is refering to the column header in the report? and Forms!theform.txtSelect1 is the field from the form? If I have the right understanding, once I do the above code: then I open the report with: DoCmd.OpenReport "Student Report", acViewReport, , strWhere1 & strWhere2 Thanks again. "Marshall Barton" wrote: > sam wrote: > >I have a report whose record source is a query, the query consists of > >dynamic Select and Where clause and so the columns are user driven (based on > >what user selects on a form) > > > >I am able to display the data to the report, however I am not able to > >refresh the columns in the report, For eg: > > > >My SQL is: > > > >strSQL = Select Student_Id, Student_FName, Student_LName & strSelect1 & > >strSelect2 & strSelect3 FROM Students_Table WHRE strWhere1, strWhere2, > >strWhere3 > > > >Now, I am able to display the specific columns in the query based on what > >users select in addition with Student_Id, Student_FName, Student_LName > >columns, However I am not able to do the same with the report, I am not able > >to display the additional columns that user selects in the form in the report > >(strSelect1 & strSelect2 & strSelect3) > > > >I have assigned this command to a button to generate the report: > > > >DoCmd.OpenReport "Student Report", acViewReport, , strWhere1 & strWhere2 & > >strWhere3 > > Assuming the strSelect# strings contain a leading comma and > the strWhere# strings conatain " AND " in all but the first > or last string, I guess you question is how to bind report > text boxes to the specifisl fields. If so, use the report's > Open event to do it: > > With Forms!theform > Me.txtSelect1.ControlSource = .txtSelect1 > . . . > End With > > -- > Marsh > MVP [MS Access] > . >
From: Marshall Barton on 28 May 2010 19:27 sam wrote: >In the following code you gave: > > With Forms!theform > Me.txtSelect1.ControlSource = .txtSelect1 > . . . > End With > >Me.txtSelect1.ControlSource is refering to the column header in the report? Me.txtSelect1 is the name of the report text box that you want to bind to the field a user specified in the form text/combo box, also named txtSelect1. The ControlSource property is where you need to put the name of the field with the values yo want the text box to display. Since I have no idea what kind of headers you are using nor what you want them to display, I did not try to comment on that. >and Forms!theform.txtSelect1 is the field from the form? Yes, that is the form text box where users specify the name of a field that you put in the report's record source query. >If I have the right understanding, once I do the above code: then I open the >report with: DoCmd.OpenReport "Student Report", acViewReport, , strWhere1 & >strWhere2 That may or may not be correct depending on what you are putting in the strWhere strings. The commas look to be wrong to me. The end result should look something like: DoCmd.OpenReport "Student Report", acViewPreview, , strWhereAll with strWhereAll containing something along these lines: thisnumberfield=123 And thattextfield="ABC" -- Marsh MVP [MS Access]
From: Sam on 30 May 2010 19:32 Hi Marshall, I am struggling with this for a while now: My issue: I want to generate dynamic reports based on dynamic sql queries, where "Select" and "Where" Clause changes with user selection on a user form in access: I am able to generate the dynamic reports now, BUT the issue I am having now is that the reports are displayed in separate pages and, I want to display them in a tabular format. Can you PLEASE help me with this? The results are display like this, all on separate pages: ID: 1 Name: tom City: New York Age: 26 ID: 2 Name: Jim City: New York Age: 28 ID: 3 Name: Chris City: New York Age: 32 I want to display the results like this: ID Name City Age 1 Tom New York 26 2 Jim New York 28 3 Chris New York 32 Here is my code so far: Private Sub GenerateReport_Click() Dim rpt As Report, rs As Recordset, db As Object, fld As Object, stSql As Object, lngTop As Long, lngLeft As Long Dim txtNew As Object, lblNew As Object 'Create the report Set rpt = CreateReport rpt.RecordSource = "Report_Query" ' Open SQL query as a recordset Set db = CurrentDb Set rs = db.OpenRecordset("Report_Query") 'Create Label Title Set lblNew = CreateReportControl(rpt.Name, acLabel, _ acPageHeader, , "Title", 0, 0) lblNew.FontBold = True lblNew.FontSize = 12 lblNew.SizeToFit ' Create corresponding label and text box controls for each field. For Each fld In rs.Fields ' Create new text box control and size to fit data. Set txtNew = CreateReportControl(rpt.Name, acTextBox, acDetail _ , , fld.Name, lngLeft + 2500, lngTop) txtNew.SizeToFit ' Create new label control and size to fit data. Set lblNew = CreateReportControl(rpt.Name, acLabel, acHeader, _ txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height) lblNew.SizeToFit ' Increment top value for next control lngTop = lngTop + txtNew.Height + 25 Next ' Create datestamp in Footer Set lblNew = CreateReportControl(rpt.Name, acLabel, _ acPageFooter, , Now(), 0, 0) ' Create page numbering on footer Set txtNew = CreateReportControl(rpt.Name, acTextBox, _ acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0) txtNew.SizeToFit DoCmd.OpenReport rpt.Name, acViewPreview Thanks in advance "Marshall Barton" wrote: > sam wrote: > >In the following code you gave: > > > > With Forms!theform > > Me.txtSelect1.ControlSource = .txtSelect1 > > . . . > > End With > > > >Me.txtSelect1.ControlSource is refering to the column header in the report? > > Me.txtSelect1 is the name of the report text box that you > want to bind to the field a user specified in the form > text/combo box, also named txtSelect1. The ControlSource > property is where you need to put the name of the field with > the values yo want the text box to display. > > Since I have no idea what kind of headers you are using nor > what you want them to display, I did not try to comment on > that. > > >and Forms!theform.txtSelect1 is the field from the form? > > Yes, that is the form text box where users specify the name > of a field that you put in the report's record source query. > > >If I have the right understanding, once I do the above code: then I open the > >report with: DoCmd.OpenReport "Student Report", acViewReport, , strWhere1 & > >strWhere2 > > That may or may not be correct depending on what you are > putting in the strWhere strings. The commas look to be > wrong to me. The end result should look something like: > > DoCmd.OpenReport "Student Report", acViewPreview, , > strWhereAll > > with strWhereAll containing something along these lines: > thisnumberfield=123 And thattextfield="ABC" > > -- > Marsh > MVP [MS Access] > . >
From: Larry Linson on 30 May 2010 20:32 Looks as if you have some section of your report where you have selected "Force New Page" either Before or After. I'm not sure what, exactly you want to see, but try "continuous forms view" with all the fields you are displaying moved to a single line or two. -- Larry Linson, Microsoft Office Access MVP Co-author: "Microsoft Access Small Business Solutions", published by Wiley Access newsgroup support is alive and well in USENET comp.databases.ms-access "Sam" <Sam(a)discussions.microsoft.com> wrote in message news:6F73A690-9D9F-41D2-93FC-6CA8DA15CEB9(a)microsoft.com... > Hi Marshall, I am struggling with this for a while now: > > My issue: I want to generate dynamic reports based on dynamic sql > queries, > where "Select" and "Where" Clause changes with user selection on a user > form > in access: > > I am able to generate the dynamic reports now, BUT the issue I am having > now > is that the reports are displayed in separate pages and, I want to display > them in a tabular format. Can you PLEASE help me with this? > The results are display like this, all on separate pages: > > ID: 1 > Name: tom > City: New York > Age: 26 > > ID: 2 > Name: Jim > City: New York > Age: 28 > > ID: 3 > Name: Chris > City: New York > Age: 32 > > I want to display the results like this: > ID Name City Age > 1 Tom New York 26 > 2 Jim New York 28 > 3 Chris New York 32 > > Here is my code so far: > Private Sub GenerateReport_Click() > > Dim rpt As Report, rs As Recordset, db As Object, fld As Object, stSql As > Object, lngTop As Long, lngLeft As Long > Dim txtNew As Object, lblNew As Object > > 'Create the report > Set rpt = CreateReport > rpt.RecordSource = "Report_Query" > > ' Open SQL query as a recordset > Set db = CurrentDb > Set rs = db.OpenRecordset("Report_Query") > > 'Create Label Title > Set lblNew = CreateReportControl(rpt.Name, acLabel, _ > acPageHeader, , "Title", 0, 0) > lblNew.FontBold = True > lblNew.FontSize = 12 > lblNew.SizeToFit > > ' Create corresponding label and text box controls for each field. > For Each fld In rs.Fields > > ' Create new text box control and size to fit data. > Set txtNew = CreateReportControl(rpt.Name, acTextBox, acDetail _ > , , fld.Name, lngLeft + 2500, lngTop) > txtNew.SizeToFit > > ' Create new label control and size to fit data. > Set lblNew = CreateReportControl(rpt.Name, acLabel, acHeader, _ > txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height) > lblNew.SizeToFit > > ' Increment top value for next control > lngTop = lngTop + txtNew.Height + 25 > Next > > ' Create datestamp in Footer > Set lblNew = CreateReportControl(rpt.Name, acLabel, _ > acPageFooter, , Now(), 0, 0) > > ' Create page numbering on footer > Set txtNew = CreateReportControl(rpt.Name, acTextBox, _ > acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - > 1000, 0) > txtNew.SizeToFit > > DoCmd.OpenReport rpt.Name, acViewPreview > > > Thanks in advance > > > > "Marshall Barton" wrote: > >> sam wrote: >> >In the following code you gave: >> > >> > With Forms!theform >> > Me.txtSelect1.ControlSource = .txtSelect1 >> > . . . >> > End With >> > >> >Me.txtSelect1.ControlSource is refering to the column header in the >> >report? >> >> Me.txtSelect1 is the name of the report text box that you >> want to bind to the field a user specified in the form >> text/combo box, also named txtSelect1. The ControlSource >> property is where you need to put the name of the field with >> the values yo want the text box to display. >> >> Since I have no idea what kind of headers you are using nor >> what you want them to display, I did not try to comment on >> that. >> >> >and Forms!theform.txtSelect1 is the field from the form? >> >> Yes, that is the form text box where users specify the name >> of a field that you put in the report's record source query. >> >> >If I have the right understanding, once I do the above code: then I open >> >the >> >report with: DoCmd.OpenReport "Student Report", acViewReport, , >> >strWhere1 & >> >strWhere2 >> >> That may or may not be correct depending on what you are >> putting in the strWhere strings. The commas look to be >> wrong to me. The end result should look something like: >> >> DoCmd.OpenReport "Student Report", acViewPreview, , >> strWhereAll >> >> with strWhereAll containing something along these lines: >> thisnumberfield=123 And thattextfield="ABC" >> >> -- >> Marsh >> MVP [MS Access] >> . >>
From: Marshall Barton on 30 May 2010 19:45
You have put a lot of effort (and learned some significant things) that, unfortunately, should NOT be used for what you are trying to accomplish. The CreateReport and CreateReportControl are intended for programmers to create their own DESIGN time wizards. You may have thought that's a capability you want to provide to your users, BUT users are operating at run time, not design time, so CreateReport and CreateReportControl are best left on the sidelines. Instead of that, you should create a basic report with enough text boxes for the fields users might need. Name the text boxes as usual for the fields that will always be there (eg. ID, lastname, etc) and the text boxes that will display user selected fields tctSelect1, txtSelect2, ... Then you can use code like I posted earlier to bind the optional text boxes to the user selected fields in your constructed SQL statement. A very important point is that all the code that sets properties in the report (RecordSource, ControlSource, etc) needs to be in the report's Open event procedure. If you have difficulty wrapping your head around this approach, please try to ask specific questions about individual aspects. Otherwise we will be going back and forth while I try to guess what you are struggling with and replying with general lectures about dynamic reports. -- Marsh MVP [MS Access] Sam wrote: >My issue: I want to generate dynamic reports based on dynamic sql queries, >where "Select" and "Where" Clause changes with user selection on a user form >in access: > >I am able to generate the dynamic reports now, BUT the issue I am having now >is that the reports are displayed in separate pages and, I want to display >them in a tabular format. Can you PLEASE help me with this? >The results are display like this, all on separate pages: > >ID: 1 >Name: tom >City: New York >Age: 26 > >ID: 2 >Name: Jim >City: New York >Age: 28 > >ID: 3 >Name: Chris >City: New York >Age: 32 > >I want to display the results like this: >ID Name City Age >1 Tom New York 26 >2 Jim New York 28 >3 Chris New York 32 > >Here is my code so far: >Private Sub GenerateReport_Click() > >Dim rpt As Report, rs As Recordset, db As Object, fld As Object, stSql As >Object, lngTop As Long, lngLeft As Long >Dim txtNew As Object, lblNew As Object > >'Create the report > Set rpt = CreateReport > rpt.RecordSource = "Report_Query" > >' Open SQL query as a recordset > Set db = CurrentDb > Set rs = db.OpenRecordset("Report_Query") > >'Create Label Title > Set lblNew = CreateReportControl(rpt.Name, acLabel, _ > acPageHeader, , "Title", 0, 0) > lblNew.FontBold = True > lblNew.FontSize = 12 > lblNew.SizeToFit > >' Create corresponding label and text box controls for each field. > For Each fld In rs.Fields > >' Create new text box control and size to fit data. > Set txtNew = CreateReportControl(rpt.Name, acTextBox, acDetail _ > , , fld.Name, lngLeft + 2500, lngTop) > txtNew.SizeToFit > > ' Create new label control and size to fit data. > Set lblNew = CreateReportControl(rpt.Name, acLabel, acHeader, _ >txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height) > lblNew.SizeToFit > >' Increment top value for next control > lngTop = lngTop + txtNew.Height + 25 > Next > >' Create datestamp in Footer > Set lblNew = CreateReportControl(rpt.Name, acLabel, _ > acPageFooter, , Now(), 0, 0) > >' Create page numbering on footer > Set txtNew = CreateReportControl(rpt.Name, acTextBox, _ > acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - >1000, 0) > txtNew.SizeToFit > >DoCmd.OpenReport rpt.Name, acViewPreview > > >"Marshall Barton" wrote: >> sam wrote: >> >In the following code you gave: >> > >> > With Forms!theform >> > Me.txtSelect1.ControlSource = .txtSelect1 >> > . . . >> > End With >> > >> >Me.txtSelect1.ControlSource is refering to the column header in the report? >> >> Me.txtSelect1 is the name of the report text box that you >> want to bind to the field a user specified in the form >> text/combo box, also named txtSelect1. The ControlSource >> property is where you need to put the name of the field with >> the values yo want the text box to display. >> >> Since I have no idea what kind of headers you are using nor >> what you want them to display, I did not try to comment on >> that. >> >> >and Forms!theform.txtSelect1 is the field from the form? >> >> Yes, that is the form text box where users specify the name >> of a field that you put in the report's record source query. >> >> >If I have the right understanding, once I do the above code: then I open the >> >report with: DoCmd.OpenReport "Student Report", acViewReport, , strWhere1 & >> >strWhere2 >> >> That may or may not be correct depending on what you are >> putting in the strWhere strings. The commas look to be >> wrong to me. The end result should look something like: >> >> DoCmd.OpenReport "Student Report", acViewPreview, , >> strWhereAll >> >> with strWhereAll containing something along these lines: >> thisnumberfield=123 And thattextfield="ABC" |