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

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

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
 
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.
 
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
 
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 said:
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 said:
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.


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.


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"
Click to expand...
Click to expand...
 
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 said:
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]

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 said:
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"
Click to expand...
Click to expand...
.
Click to expand...
 
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 said:
sam said:
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?
Click to expand...

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?
Click to expand...

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?
Click to expand...

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 said:
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


:
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"
.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top