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
|
|||
|
|||
PLEASE HELP: Refresh columns displayed in a report from a query ou
Hi All,
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 How can I resolve this? Hope I made it clear. Thanks in advance |
#2
|
|||
|
|||
PLEASE HELP: Refresh columns displayed in a report from a query ou
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] |
#3
|
|||
|
|||
PLEASE HELP: Refresh columns displayed in a report from a quer
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] . |
#4
|
|||
|
|||
PLEASE HELP: Refresh columns displayed in a report from a quer
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] |
#5
|
|||
|
|||
PLEASE HELP: Refresh columns displayed in a report from a quer
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] . |
#6
|
|||
|
|||
PLEASE HELP: Refresh columns displayed in a report from a quer
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" |
#7
|
|||
|
|||
PLEASE HELP: Refresh columns displayed in a report from a quer
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" wrote in message ... 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] . |
#8
|
|||
|
|||
PLEASE HELP: Refresh columns displayed in a report from a quer
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" . |
#9
|
|||
|
|||
PLEASE HELP: Refresh columns displayed in a report from a quer
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 |
#10
|
|||
|
|||
PLEASE HELP: Refresh columns displayed in a report from a quer
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] . |
|
Thread Tools | |
Display Modes | |
|
|