A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

PLEASE HELP: Refresh columns displayed in a report from a query ou



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 05:49 PM posted to microsoft.public.access.reports
Sam
external usenet poster
 
Posts: 855
Default 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  
Old May 28th, 2010, 08:17 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old May 28th, 2010, 11:17 PM posted to microsoft.public.access.reports
Sam
external usenet poster
 
Posts: 855
Default 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  
Old May 29th, 2010, 12:27 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old May 31st, 2010, 12:32 AM posted to microsoft.public.access.reports
Sam
external usenet poster
 
Posts: 855
Default 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  
Old May 31st, 2010, 12:45 AM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old May 31st, 2010, 01:32 AM posted to microsoft.public.access.reports
Larry Linson
external usenet poster
 
Posts: 3,112
Default 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  
Old May 31st, 2010, 11:59 PM posted to microsoft.public.access.reports
Sam
external usenet poster
 
Posts: 855
Default 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  
Old June 1st, 2010, 02:43 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old June 4th, 2010, 08:03 PM posted to microsoft.public.access.reports
Sam
external usenet poster
 
Posts: 855
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.