PLEASE HELP: Refresh columns displayed in a report from a quer
in [Reports]
|
From: sam on 31 May 2010 18:59 Hi Marshall, I do think the same. I learned a lot in this process. Thanks to you for providing the directions! I have a couple questions: 1: I have put the code in form_load procedure, Is this a better place to execute the code than form_open procedure? 2: I have basically put about 10 extra columns in the report, so now the column display is driven by user selection on the form, BUT is there a way to hide the columns(column header) that are not selected by the user? 3: Also, we are hard coding the column locations to the fields in the form, What if the user selects the 6th column value to be displayed and not the 5th or the 4th? The location of the columns is fixed here, so there would be a huge gap between 3rd column and 6th column. Is there a fix for this? Thanks in advance "Marshall Barton" wrote: > 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" > . >
From: Marshall Barton on 1 Jun 2010 09:43 sam wrote: >I have a couple questions: >1: I have put the code in form_load procedure, Is this a better place to >execute the code than form_open procedure? Whoa! We had better be talking about reports here, not a form. A report opened in preview or normal view (only useful ways) does not have/use a load event so you must use the REPORT's OPEN event. > >2: I have basically put about 10 extra columns in the report, so now the >column display is driven by user selection on the form, BUT is there a way to >hide the columns(column header) that are not selected by the user? See below. > >3: Also, we are hard coding the column locations to the fields in the form, >What if the user selects the 6th column value to be displayed and not the 5th >or the 4th? >The location of the columns is fixed here, so there would be a huge gap >between 3rd column and 6th column. Is there a fix for this? You should make the user selectable text boxes and their associated header labels in the report invisible. Then use code in the open event to set the label captions and the text box's control source and make them visible at the same time. Because you so not know a priori which field will be bound to which report text box, the textboxes should be named in a uniform fashion (eg. txtSelect1, txtSelect2, ... and lblSelect1, lblSelect2, ...). With this approach, the code in the report's Open event could be vaguely like: Dim k As Integer With Forms!theform If Not IsNull(.thistextbox) Then k = k + 1 Me("txtSelect" & k).ControlSource = .thistextbox Me("txtSelect" & k).Visible = True Me("lblSelect" & k).Caption = .thistextbox Me("lblSelect" & k).Visible = True End If If Not IsNull(.thattextbox) Then k = k + 1 Me("txtSelect" & k).ControlSource = .thattextbox Me("txtSelect" & k).Visible = True Me("lblSelect" & k).Caption = .thattextbox Me("lblSelect" & k).Visible = True End If . . . > >"Marshall Barton" wrote: > >> 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. >> >> >> 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" >> . >> -- Marsh MVP [MS Access]
From: sam on 4 Jun 2010 15:03 Hi Marshall, This worked out perfect! Thanks a LOT! Do you know anything about storing sub routines and SQL queries as column values and executing them through VBA? Hope I made it clear "Marshall Barton" wrote: > sam wrote: > >I have a couple questions: > >1: I have put the code in form_load procedure, Is this a better place to > >execute the code than form_open procedure? > > Whoa! We had better be talking about reports here, not a > form. A report opened in preview or normal view (only > useful ways) does not have/use a load event so you must use > the REPORT's OPEN event. > > > >2: I have basically put about 10 extra columns in the report, so now the > >column display is driven by user selection on the form, BUT is there a way to > >hide the columns(column header) that are not selected by the user? > > See below. > > > >3: Also, we are hard coding the column locations to the fields in the form, > >What if the user selects the 6th column value to be displayed and not the 5th > >or the 4th? > >The location of the columns is fixed here, so there would be a huge gap > >between 3rd column and 6th column. Is there a fix for this? > > You should make the user selectable text boxes and their > associated header labels in the report invisible. Then use > code in the open event to set the label captions and the > text box's control source and make them visible at the same > time. > > Because you so not know a priori which field will be bound > to which report text box, the textboxes should be named in a > uniform fashion (eg. txtSelect1, txtSelect2, ... and > lblSelect1, lblSelect2, ...). With this approach, the code > in the report's Open event could be vaguely like: > > Dim k As Integer > With Forms!theform > If Not IsNull(.thistextbox) Then > k = k + 1 > Me("txtSelect" & k).ControlSource = .thistextbox > Me("txtSelect" & k).Visible = True > Me("lblSelect" & k).Caption = .thistextbox > Me("lblSelect" & k).Visible = True > End If > If Not IsNull(.thattextbox) Then > k = k + 1 > Me("txtSelect" & k).ControlSource = .thattextbox > Me("txtSelect" & k).Visible = True > Me("lblSelect" & k).Caption = .thattextbox > Me("lblSelect" & k).Visible = True > End If > . > . > . > > > > >"Marshall Barton" wrote: > > > >> 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. > >> > >> > >> 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" > >> . > >> > > -- > Marsh > MVP [MS Access] > . >
From: Marshall Barton on 4 Jun 2010 17:13
sam wrote: >This worked out perfect! Thanks a LOT! Good to hear that you were able to figure out the details and get it working. The amount of code could be reduced by naming the user selectable text boxes on the form similar to the way they are named in the report. Then you could use a loop instead of having a block of code for each form text box. > >Do you know anything about storing sub routines and SQL queries as column >values and executing them through VBA? VBA code must be compiled before it can be executed. Because that is a design time action, you can not put code anywhere other than in a module. You can use the Eval function to evaluate a text string that is a valid expression. The expression has the same limitations as a text box expression (eg. no VBA variables). However, you can use public functions you have created in a standard module in an expression. If you can figure out a way to do what you want by just running a function, then you can specify the name of a function in a Text table field and use a line of code in an appropriate event to call the function: Eval(rs!somefield & "()") OTOH, SQL statements in a text string can be executed, so you can put those in a table. The code to run an action query could be: db.Execute rs!fieldwithSQLstatement Or, for Select queries, you would be more likely to want to use them as a form's record source: Me.RecordSource = rs!fieldwithSQLstatement -- Marsh MVP [MS Access] |