Trying to create a report that users can select fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Put on your thinking caps...Here's what I'm trying to do. I want to create a
report from a order entry table I created. I want to set up a form with check
boxes for each field in the table. (Not sure if the check boxes is the right
way to go) Then I want the user to put a check mark in the boxes of the
fields they want to show on the report. Then select a customer from a combo
box and then run the report. So the end result will be all orders from that
customer with only the fields they selected from the form. Can this be done?
 
Secret said:
Put on your thinking caps...Here's what I'm trying to do. I want to create a
report from a order entry table I created. I want to set up a form with check
boxes for each field in the table. (Not sure if the check boxes is the right
way to go) Then I want the user to put a check mark in the boxes of the
fields they want to show on the report. Then select a customer from a combo
box and then run the report. So the end result will be all orders from that
customer with only the fields they selected from the form. Can this be done?
Click to expand...


Yes it can be done, but it requires a fair amount of VBA
code in the report's open event.

Create the report with all the text boxes you may ever need
and make them invisible.

Then use the report's open event to check the form and make
the appropriate reposrt text boxes visible. You didn't
provide specific details about the controls, where they
should appear or how you plan to determine which field
should be related to each check box, but here's some sample
code that might give you a starting point:

Dim lngPos As Long
lngPos = .25 * 1440 'start 1/4 inch from left edge
With Forms!theform
If .chkfieldA = True Then
Me.txtfieldA.Visible = True
Me.txtfieldA.Left = lngPos
lngPos = lngPos + Me.txtfieldA.Width
End If
If .chkfieldB = True Then
Me.txtfieldB.Visible = True
Me.txtfieldB.Left = lngPos
lngPos = lngPos + Me.txtfieldB.Width
End If
. . .
End With
 
Thank you for your response. Is that code the code that I should use in the
reports open event? Will I have to do that code for every field that I want
to use on the report? Also, I have a form set up with all the check boxes but
how do I tie that into the report and the query that I have set up? The user
will select the customer from a combo box and them check off the boxes of the
fields that they want to list on the report. I have a query created but I
don't know how to tie these check boxes into the query and then create the
report.
 
Also, do I have to put these text boxes on the report exactly where I would
want them or will the code put them in order for me from left to right?
 
The is a complete ad-hoc/query by form "applet" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. You just
import a few objects into your application, create some master queries, and
use it.

Users select fields, sorting, filtering, grouping,... When they click the
run button their results are displayed in a datasheet subform. They can then
select a destination such as Excel, Print, Word,... and click a button to
send the records.
 
There is no file on that topic to download from that link. Will that tell me
everything I need to know so I can create this report?
 
"Query by form"

--
Duane Hookom
MS Access MVP


Secret Squirrel said:
There is no file on that topic to download from that link. Will that tell
me
everything I need to know so I can create this report?
Click to expand...
 
I'm getting the feeling that you're really looking for a
tutorial on how to design and implement a dynamic report.
This quesstion is too vague and goes beyond the scope of
what can be dealt with in newsgroup posts. At this point, I
recommend that you pursue at least one of these avenues:

Take a class in VBA and possibly another that includes the
report object and how to manipulate controls.

Download and study Duane's generic dynamic report as an
example of how this kind of thing can be done. If you have
a specific question about a specific point, then asking the
group to help is appropriate.

Hire a professional to create all this for you.
 
Back
Top