Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 26th, 2008, 10:16 PM
Registered User
 
Join Date: Mar 2008
Location: , , USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Report from Query

Summary: the user is able to control (include or exclude in the output) 35 checkbox / fields in the query. Currently output is going to an excel sheet. Goal create a dynamic changing report.


Dear All,
This is a bit complicated but I hope that you would be able to help me out.
I have created a query off a linked table (from AS/400). This query has several fields that are controlled from a form using checkboxs, where if the check box is checked then the field will show up in the sql statement if not then then sql will run without that field. Currently I'm having the spreadsheet output to an excel sheet.
But what I'm looking (which is my optimum goal) to have a report based on that query that would dynamically include whatever fields are checked and remove whatever not checked.
Now I thought about this and I'm thinking of creating a report based on the query that would include ALL the fields then I insert some code that should be run- when the report is activated or being populated - that would go and check all the check boxes then what ever is not checked then the code will make the attribute of that field in the report of Visible as false thus removing it from the report...
Is my solution possible? is there such a code that would be created? I would appreciate if you can help me out with this...

Any idea/opinion is greatly appreciated...
Thank you,
Ahmad.

Reply With Quote
  #2 (permalink)  
Old March 28th, 2008, 06:41 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

It looks to me that what you are asking for, while inconsistent (is this an Excel spreadsheet or an Access report?) can be done with the Access report.

Create a module, and then within the module, create 35 public variables As Boolean:

Public bCheck1 As Boolean
Public bCheck2 As Boolean
... etc


Then on the form that launches the report, set the value of the boolean based on the check box state:

If Me.Check1.Value = True Then
   bCheck1 = True
Else
   bCheck1 = False
End If

If Me.Check2.Value = True Then
   bCheck2 = True
Else
   bCheck2 = False
End If
... etc

Then on the On Format event of the Report's Detail section, do this:

Me.Column1.Visible = bCheck1
Me.Column2.Visible = bCheck2
... etc

This will set the Visible property of each column to either True or False, depending on whether the checkbox was checked or not.

If you wanted to be more rigorous, you could make sure that the default value of each check box is set to false.

Did that help? Does that resolve all your issues, or do you still have the export to Excel issue?




mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Report Query happygv SQL Server 2000 4 June 11th, 2007 08:16 PM
Crystal Report Query asters Crystal Reports 0 December 29th, 2006 10:37 AM
Non query Report Parameter qa BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 July 2nd, 2004 02:49 PM
Generating a report from more than one query lic023 Access VBA 1 March 31st, 2004 06:46 PM
Report query khautinh SQL Server 2000 2 July 22nd, 2003 02:21 PM



All times are GMT -4. The time now is 06:16 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.