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
0
Utf
5/28/2010 4:49:01 PM
access.reports 4434 articles. 0 followers. Follow

10 Replies
1172 Views

Similar Articles

[PageSpeed] 46

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]
0
Marshall
5/28/2010 7:17:38 PM
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]
> .
> 
0
Utf
5/28/2010 10:17:01 PM
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]
0
Marshall
5/28/2010 11:27:22 PM
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]
> .
> 
0
Utf
5/30/2010 11:32:01 PM
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"
0
Marshall
5/30/2010 11:45:06 PM
Looks as if you have some section of your report where you have selected 
"Force New Page" either Before or After.

I'm not sure what, exactly you want to see, but try "continuous forms view" 
with all the fields you are displaying moved to a single line or two.

-- 
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET 
comp.databases.ms-access


"Sam" <Sam@discussions.microsoft.com> wrote in message 
news:6F73A690-9D9F-41D2-93FC-6CA8DA15CEB9@microsoft.com...
> Hi Marshall, I am struggling with this for a while now:
>
> My issue:  I want to generate dynamic reports based on dynamic sql 
> queries,
> where "Select" and "Where" Clause changes with user selection on a user 
> form
> in access:
>
> I am able to generate the dynamic reports now, BUT the issue I am having 
> now
> is that the reports are displayed in separate pages and, I want to display
> them in a tabular format. Can you PLEASE help me with this?
> The results are display like this, all on separate pages:
>
> ID: 1
> Name: tom
> City: New York
> Age: 26
>
> ID: 2
> Name: Jim
> City: New York
> Age: 28
>
> ID: 3
> Name: Chris
> City: New York
> Age: 32
>
> I want to display the results like this:
> ID Name City Age
> 1 Tom New York 26
> 2 Jim New York 28
> 3 Chris New York 32
>
> Here is my code so far:
> Private Sub GenerateReport_Click()
>
> Dim rpt As Report, rs As Recordset, db As Object, fld As Object, stSql As
> Object, lngTop As Long, lngLeft As Long
> Dim txtNew As Object, lblNew As Object
>
> 'Create the report
>     Set rpt = CreateReport
>     rpt.RecordSource = "Report_Query"
>
> ' Open SQL query as a recordset
>     Set db = CurrentDb
>     Set rs = db.OpenRecordset("Report_Query")
>
> 'Create Label Title
>     Set lblNew = CreateReportControl(rpt.Name, acLabel, _
>     acPageHeader, , "Title", 0, 0)
>     lblNew.FontBold = True
>     lblNew.FontSize = 12
>     lblNew.SizeToFit
>
> ' Create corresponding label and text box controls for each field.
>     For Each fld In rs.Fields
>
> ' Create new text box control and size to fit data.
>     Set txtNew = CreateReportControl(rpt.Name, acTextBox, acDetail _
>      , , fld.Name, lngLeft + 2500, lngTop)
>         txtNew.SizeToFit
>
>  ' Create new label control and size to fit data.
>         Set lblNew = CreateReportControl(rpt.Name, acLabel, acHeader, _
> txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
>         lblNew.SizeToFit
>
> ' Increment top value for next control
>     lngTop = lngTop + txtNew.Height + 25
>     Next
>
> ' Create datestamp in Footer
>     Set lblNew = CreateReportControl(rpt.Name, acLabel, _
>     acPageFooter, , Now(), 0, 0)
>
> ' Create page numbering on footer
>     Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
>     acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width -
> 1000, 0)
>     txtNew.SizeToFit
>
> DoCmd.OpenReport rpt.Name, acViewPreview
>
>
> Thanks in advance
>
>
>
> "Marshall Barton" wrote:
>
>> sam wrote:
>> >In the following code you gave:
>> >
>> > With Forms!theform
>> > Me.txtSelect1.ControlSource = .txtSelect1
>> > . . .
>> > End With
>> >
>> >Me.txtSelect1.ControlSource is refering to the column header in the 
>> >report?
>>
>> Me.txtSelect1 is the name of the report text box that you
>> want to bind to the field a user specified in the form
>> text/combo box, also named txtSelect1.  The ControlSource
>> property is where you need to put the name of the field with
>> the values yo want the text box to display.
>>
>> Since I have no idea what kind of headers you are using nor
>> what you want them to display, I did not try to comment on
>> that.
>>
>> >and Forms!theform.txtSelect1 is the field from the form?
>>
>> Yes, that is the form text box where users specify the name
>> of a field that you put in the report's record source query.
>>
>> >If I have the right understanding, once I do the above code: then I open 
>> >the
>> >report with: DoCmd.OpenReport "Student Report", acViewReport, , 
>> >strWhere1 &
>> >strWhere2
>>
>> That may or may not be correct depending on what you are
>> putting in the strWhere strings.  The commas look to be
>> wrong to me.  The end result should look something like:
>>
>> DoCmd.OpenReport "Student Report", acViewPreview, ,
>> strWhereAll
>>
>> with strWhereAll containing something along these lines:
>> thisnumberfield=123 And thattextfield="ABC"
>>
>> -- 
>> Marsh
>> MVP [MS Access]
>> .
>> 


0
Larry
5/31/2010 12:32:51 AM
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"
> .
> 
0
Utf
5/31/2010 10:59:01 PM
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]
0
Marshall
6/1/2010 1:43:21 PM
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]
> .
> 
0
Utf
6/4/2010 7:03:29 PM
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]
0
Marshall
6/4/2010 9:13:02 PM
Reply:

Similar Artilces:

CToolBar Help
Hello, I've been trying to add a toolbar to my dialog following the example from MSDN a http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vcmfc98/html/_mfc_ctoolbar.asp But for some reason I just can't add the toolbar I created. Here is my code: CToolBar m_toolbar; if (!m_toolbar.Create(this, WS_CHILD | WS_VISIBLE | CBRS_TOP | CBRS_GRIPPER | CBRS_TOOLTIPS | CBRS_FLYBY | CBRS_SIZE_DYNAMIC, IDR_TOOLBAR1)) { MessageBox("Error -1"); } m_toolbar.LoadToolBar(IDR_TOOLBAR1); Could somebody please tell what, if anything, I am doing wrong? Thank you, V...

Office 2007 Professional: Help Files Issues
Gents, I cannot find a solution to this odd issue and before I submit a support ticket I thought I would ask the community. We have office 2007 professional (volume licensing) on a Window 2003 terminal server. We have a few users out of a bunch that get the "This page is unavailable" I have tried the Refresh and back. I've tried online and offline modes. In offline I get the table of contents to the left but same unavailable message when I click the links. I noticed on testing some other users, that apparently never used the help. That there are some prompts...

Outlook 2003 Messages and Calendar Appts Received as PLAIN TEXT
SGVsbG86DQoNCkkganVzdCBnb3QgYSBuZXcgbGFwdG9wIHdpdGggVmlzdGEgSG9tZSBCYXNpYyBv biBpdCBhbmQgSSBjb3BpZWQgbXkNCnBzdCBmaWxlIGZyb20gbXkgb2xkIGxhcHRvcCB0byB0aGUg bmV3IG9uZS4gRXZlcnl0aGluZyB3b3JrcyBmaW5lDQpleGNlcHQgYWxsIGUtbWFpbCBtZXNzYWdl cyBhbmQgY2FsZW5kYXIgYXBwdHMuIEkgcmVjZWl2ZWQgYXJlIGluIHBsYWluDQp0ZXh0LiBJJ3Zl IHNlYXJjaGVkIGV2ZXJ5d2hlcmUgZm9yIHRoaXMgZml4LCBidXQgZm91bmQgbm90aGluZy4gDQoN CkRvZXMgYW55b25lIGhhdmUgYW55IHN1Z2dlc3Rpb25zIGZvciBtZT8gDQoNClRoYW5rIHlvdSEN ClNwdWRkeQ0K me@cox.net <me@cox.net> wrote: > I just got a new laptop with Vista Home Basic on it and I copied my > pst file...

Spell Check Help
I have this code in the AfterUpdate in a TextBox set for a memo: If Len(Me!casefacts & "") > 0 Then DoCmd.RunCommand acCmdSpelling Else Exit Sub End If everytime the spell check executs, it check the whole form. How can I get it to just check that one textbox and not everything else. bladelock wrote: >I have this code in the AfterUpdate in a TextBox set for a memo: > >If Len(Me!casefacts & "") > 0 Then > DoCmd.RunCommand acCmdSpelling > Else > Exit Sub >End If &...

Help with Regexp, please
Hi, The regular expression (\d{15,16}) matches a substring in a cell. I want to extract the remaining part of the cell ie. from the character after the matched substring till the end of the string in the cell using a regular expression. Is it possible to do this? Thanks in advance for the help. Regards, Raj Raj, with abcd15,16xyz in a cell the code below abstracts abcxyz. Maybe you can use this as a basis to develop your code Function GetSubstring(S As String) As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.Pattern = &quo...

How can I run a GP sales report by State
I need to run a sales report in Great Plains showing all of our sales by state, one state in particular. Is there an existing report, or do I have to create one in Smartlist? If Smartlist is the right option, where should I start? Thank you, Adam You might be able to modify the sales report in Report Writer to sort by state and then add state header and footer sections to the report. You didn't mention which report you were trying to run or what type of information you need. If you're not a Report Writer person, I would go with SmartList or some external report writer. -- C...

Default Value on Form Help
I have a query that calculates the total of a field in my table - Field Name in my Table is Total Value which calculates the total for a series of records that has the same invoice number. Field in in my query is Aggregate Value. I now have a form where I bring in the Aggregate Value. I need to create another Text box Value Verified where I bring in the Aggregate Value as a default, but I may have to change it if the Value has changed - but the Aggregate Value has to remain the same. I tried using default value on the form for the Value Verified Text control that brings in ...

Publisher 2007 Trial Help
I installed the trial version of Publisher 2007 and want to uninstall it now. I have tried to remove in the "add/remove" programs section. And I have also tried a suggestion I found in an earlier post about going to the "installer" and trying to uninstall it there. Oh and I tried repairing it and then trying to uninstall it. I can't even open the program from my programs menu and I can't get into the language section to check the language. It tells me that the language isn't compatible with my version or something to that effect. Nothing has worked...

PLEASE PLEASE HELP
I Have an emergency... I deleted a macro !!! I have 60 rows with checkboxes on cells A1 trough A60 that automatically as checked were inserting current dates on the cell next to it. for some reason a disaster happened and the macro was deleted or messed up. I tried searching for help in these groups, but none so far. I found one, but it is not a macro besides when I use that function all the other previous dates keep changing when other checkboxes are checked on in different days of the week. If you can please guide me to the right answer I will appreciated. Hope I made sense with my explana...

Cannot access a file -- HELP
I have this problem at work (using Excel 2000 Windows 2000) and at home (Excel 2003, Windows XP Pro). SOMETIMES...not always, I try to access a file on a network drive I get the message: "path..filename could not be found. Check the spelling of the file name and verify that the file location is correct". Once I get the message on that file, I always get it on the file....but there might be 27 others in the same folder that I can open. And I get the message if I try to open the file via explorer or via Excel. If I copy the file to my C: drive it opens fine. I even tried t...

Sum Function Help Please
I have created two Option Groups, setting up values against the radio buttons. The output from both boxes works fine (thanks to the Wizard), however when I'm creating a formula on the report, I'm unable to add the two returned values together. If I type in =([Q1]+[Q2]) then I get both numbers concatenated together (ie returned value from Q1 is 1 and returned value from Q2 is 2, the answer I get is 12 !!!!.) Interestingly if I enter =([Q1]-[Q2]) then I get the correct answer. Any help would be greatly appreciated here. I could be wrong, but it seems like access i...

Referencing a dynamic column
How do I reference a column that changes number of rows day to day? I am running a macro that is filtering on a number of criteria, and I want the macro to select the first value, replace that value with a new value, and replace all the other values returned by the filter (the VBA equivalent of Filter, Copy, SHIFT-CTRL-Down arrow key and Paste). I am sure that it is something fairly simple, but I've been trying for four or five hours (doing other stuff at the same time), and I'm stumped. I've tried using Relative Reference in Macro Recorder, but for some reason, it does not seem...

Multi select box Query problem
Hi, I'm having a problem running a query using a Multi Select List Box. Right now I'm just trying for one item, so my Criteria for the Year field looks like this... Forms!frmCreateReport.[crcYear].ItemData(2) If I use the Immediate Window and write this in, it gives me the answer of 2005... which is what I would like the query to use for it's query. However when I input this in the query it gives me a user defined function error. Any one know the solution to this problem? Thanks in advance. Once the MultiSelect property is set to other than None, you can't refer to l...

2nd request for help -PLEASE PLEASE PLEASE
went through a windows update and was asked to reboot which I did. I had Outlook open and closed it before rebooting - it was opened to contacts. when I tried to access Outlook I got an error message saying "your personal address book could not be opened. the file is either not accessible or not a personal address book. choose retry to access your personal address book, or choose cancel to not open your personal address book with this application." I have no other application to open this address book with! my contact info is detrumental to me as well as my Calendar ...

Report Writer not Carrying Lines Across Multiple Groups
I have a multi page PA invoice wherein the vertical lines do not display on pages 2 or 3. The data displays, but no lines. Furthermore, the last page has only a half page of data & should have vertical lines above the footer, but there is simply a large white space. I do not imagine I am the only person with this type of issue. However, It doesnt appear report writer can easier handle formatting of multi-page invoices. If the vertical lines start in the First Page Header, they will extend down the first page. However, since the First Page Header is not printed on the ...

In Word How do I switch off auto date insertion please
How can I disable? ...

Copy HIDDEN columns to a new workbook
Hi there! Is there any code which you can help me with to copy HIDDEN COLUMNS to a new workbook. I'm using below codes: (COLUMNS HIDDEN are columns J-K-L) Sub Copy_With_AutoFilter1() 'Note: This macro use the function LastRow Dim My_Range As Range Dim CalcMode As Long Dim ViewMode As Long Dim FilterCriteria As String Dim CCount As Long Dim WSNew As Worksheet Dim sheetName As String Dim rng As Range Dim WS As Worksheet Set My_Range = Worksheets("Sheet1").Range("A1:BN" & LastRow(Worksheets("...

Remove template help task pane from startup
I created an invoice using one of Microsoft's templates. The help template appeared when I was designing the invoice. Now that the invoice is finished, I would like for the help template to NOT appear. I went to Tools-Options and unchecked "startup task pane" and also went to Tools-Customize and unchecked "Task Pane". This made the task pane go away, but only until I opened it again. Is there any way to make it not show up every time I open the document? ...

Mail displayed without Sign In
Why do my E-mails still appear if I do not sign in ? On the sign in screen if you just click Cancel then Live Mail will still open and show all previous E-mails. So if anyone else opened the program they would be able to see every previous E-mail without using the password. Is there a way to prevent Live Mail from opening unless the password is entered ? To protect your mail from other user, ensure that your Windows account has a password, then logoff of your Windows profile. Even with a password, anyone having access to your computer would still have access to the locally saved...

Can't SEND SSL email out!! Please Help... it HURTZ!
OKay... being a bit silly about he hurting part. I have a strange email issue with some of my computers at home that I use for work. We have 4 computers. 2 of them work FINE, the other 2 have issues. I know the email accounts are working correctly because if I run them on the 2 *good* computers they work like they should. Configurations: (GOOD Notebooks) 1 - HP AMD-XP notebook with WinXP-Home SP1, Office 2000 2 - Sony P4 Notebook with WinXP-Home SP1, Office 2000 (Bad systems) 3 - Dell P3 notebook with WinXP-Pro SP1, Office 2002 4 - Compaq AMD-XP with WinXP-Home SP2, Office 2000 ALl ma...

Query to show relevant training
I want to make a report (which I hope to eventually use as a subreport) to detail training classes employees have taken since they last renewed licenses. Training only counts if it's occurred after the issue date of the certificate. I have four tables that I believe would be relevant, a truncated list follows: tblTrainingClass: lngClassID (PK) chrClassTitle dtmClassDate .... tblEmployee: chrEmpID (PK) chrLName chrFName .... tblTrainee: (an intersection table) lngClassID (PK) chrEmpID (PK) tblCertificate: lngCertificateID (PK) chrEmpID (FK) dtmIssueDate dtmEx...

Parent Child report #2
Hi there does anyone have a parent child report? To list items by parent? Or by Matrix for that matter? Ivan The Reports Library has these two below: This report displays the Item Quantity List grouped by matrix code. Only matrix items are displayed, and each matrix code is displayed with its corresponding components. (April 26th, 2004) https://mbs.microsoft.com/downloads/customer/Custom_Matrix_Quantity_List.zip This report displays the Item Quantity List for Parent Child items. Only parent child items are displayed, and each parent code is displayed with its corresponding child ...

Please Please Please help
Hi I have a Worksheet with option buttons and I need the users to be able to click on the options but not be able to drag or move them. I don't want the users to be able to click into or type anything in any of the cells - only these options buttons???? How is it done. I have tried unlocking all cells and then locking the individual ones, but the user can still drag the option button around and they could do this by mistake thus upsetting the design of my sheet! I have tried protecting the sheet for Objects - but then I can't click on the option at all ....Please someone help...

Help with formula #3
I need to edit this formula 2 ways =COUNT(J1:J5) first way I need it to count any cell with a value lower then 10 as 1 and any cell with a value of 10 or higher as .75 For example if cells j1 to J4 are all valued at 6 but cell J5 is value at 11 the total I get would be 4.75 Second formula I need is to count J1 to J5 only the cells with a value of 10 or higher as .25 For example if cells J1 to J4 are all valued at 6 but cell J5 is value at 11 the total I get would be .25 Hi Leave Alone! First problem: =COUNTIF(J2:J6,"<10")+COUNTIF(J2:J6,">=10")*0.75 Second pro...

Show/Hide Column and Row Headers
Hello, ive got difficulty how to show the column and row headers in Excel worksheets, can anyone help me? tnx! -- graz ------------------------------------------------------------------------ graz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27611 View this thread: http://www.excelforum.com/showthread.php?threadid=471335 Good morning Graz Try Tools > Options > Row & Column Headers (under the View tab). HTH DominicB -- dominicb ------------------------------------------------------------------------ dominicb's Profile: http://www.exce...