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  

Weird Problem with a Subreport



 
 
Thread Tools Display Modes
  #21  
Old August 10th, 2007, 10:26 PM posted to microsoft.public.access.reports
Klatuu
external usenet poster
 
Posts: 7,074
Default Weird Problem with a Subreport

Do you have a table that lists all the tests for the year by month? If so,
you could filter your query by the month identifier. Or is it (as I fear from
the SQL) you have a table for each month, or two for each month? (even worse
than names with spaces
Well, at least all you have to do is change the file names table names for
each.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Indeed it is a reactor of the nuclear variety. It would cause a mushroom
cloud if things went wrong. It has been running pretty smoothly for 50 years
now so it should be fine. We supply most of Ontario, (possibly even Canada,
I'm not sure I'm just a college student working here for the summer) with
radioactive isotopes for any medical procedure that would require them; such
as radiation therapy to treat cancer.

Ok spaces in names bad! haha.

Ok what you just helped me make is a list of all of the tests that need to
be done to the reactor in the month of august. Hence why it was so important
that the list of tests than need to be done during reactor shutdown be
seperate from the others. Now I need to make reports for the lists for all
of the other eleven months! I was just going to make more queries and then do
a save as for the reports and just change which query they are getting the
info from and then change the titles. Is there an easier way?
--
Chels


"Klatuu" wrote:

Doh! I should have seen the spaces. Sorry.
Spaces should not be in names, see what happens?
what is a reactor? Nothing that will create a mushroom cloud, I hope.
So why 11 more?
Tell me about them. It is very likely we really only need one with some
tweeks to make it think there are 11. I do that all the time. If you can,
it saves a lot of work now and in the future when changes or enhancements are
required.

Would you rather do it 12 times or 1?
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

SUCCESS!!!!!!!!!
SELECT 0 AS ListID, UNIT, Remarks, [Place Holder] FROM AugustND;
UNION ALL SELECT 1 As ListID, UNIT, Remarks, [Place Holder] FROM
AugustShutdownND;
This worked! That is how it has all of the fields in SQL view of the
queries I've made in design view. It just occured to me to check them. Seeing
how those work is helpful but it looks very different from how you write
these ones.
I viewed the field list, added Place holder to my report, made it invisible
and used sorting and grouping to sort by the placeholder and all is well!!!!

Thank you so much for teaching me how to do this and helping me make this
report exactly the way I needed it! I am sure the reactor will be very
thankful for your help with making these testing schedules! haha. Now I just
need to make 11 more just like this....
--
Chels


"Klatuu" wrote:

You bind a field in a form or report's record source to the control source
property of the text box. This is the same for all controls that can contain
data. A label can't. (Well, you can make it show a data item, but that would
be a waste)

As to the SQL problem, post back with the SQL and we will have a look.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Thank you so much. I definitely am learning you area big help! Believe it or
not i did take a course in this program, a lot of help that was!

So how do you make text boxes bound to something?

I tried to add Place Holder to the Union query along with UNIT, Remarks but
it will not work. It wouldnt accept it with a space between the words. I
tried using an underscore between them and it accepted that but it is not
showing any numbers and just asks me for a parameter value. Does this mean it
can't find the information for that field?
--
Chels


"Klatuu" wrote:

Yes, but you are learning

You can use a label.. All you need to do is make the label visible or
invisible.
labels are not intended to hold data. That is what text boxes are for.
Lables are for identifying the context of the data or providing static
information to the user.

As to the sort. You can include the place holder and leave it visible. That
doesn't mean you have to show it on the report. You can just use if for the
sorting and grouping.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

OK I somehow got that VBA code to work! Thank you!!! To answer your
questions....

I have a label as the group header, is that wrong? It's not bound to
anything... Is that why i am having problems with this? I dont know how to
use text boxes....I just use labels because I didnt understand the whole
Text#: Unbound thing as seperate boxes.

Ok for the sorting i will try to explain it to you. My main table in my
database is a list of tests that each have their own Unit #, the unit numbers
are all weird though they all have numbers, come also have letters like P1
for part 1 of a test or R or E if they're a different kind of test but they
all do make sense in an order. The sorting gets confused i think by the
letters and puts it in weird orders such as

UNIT
1
10
11
12
2
29 P1
29 P2
3
30
31 P2

The Place Holder field I made just to keep all of the Unit numbers in the
right order. I usually include Place Holder in all of my querys but put it as
not visible just to keep everything in order and then reports from those
queries are also always in order. Because you told me how to make the Union
query by writing the SQL I didnt include the Place holder field, I thought it
would just keep itself in order because the two queries I made the Union
query from are being sorted by the place holder field. Does that make sense?

I have a feeling I am making things harder for myself because I dont fully
understand how to use this program.

--
Chels


"Klatuu" wrote:

Should be
If Me.txtGroupType = 0 Then
Me.GroupHeader1.Visible = False
Else
Me.GroupHeader1.Visible = True
End If
End Sub

No, you don't need a second one.
Do you have a text box in the Goup Header?
What is the name of the text box and which field in the query is it bound to?

If you don't have a field to sort on, how do you know it is not sorting
correctly?
You can't sort without at least one field.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Ok I have this as the Code for the Group Header:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtGroupType = 0 Then
Me.GroupHeader1.Visible = False
Else
Me.GroupHeader1.Visible = False
End If
End Sub

It is not working. I have never tried using VBA code before. I only have the
one group should I have two?

The problem with the sorting is that the field I usually use for sorting is
not in the query or report. It is just a number assigned to each entry to
keep them all in the proper order because none of the information in the
records can be used for sorting. I usually include it in the queries (It is
called Place Holder) but don't make it visible and then when I make the
reports from those queries everything is usually in the right order. When I
tried to include that field in the Union query it keeps putting it in as
blank and asking for a parameter value.

--
Chels


"Klatuu" wrote:

If you don't want to show the header on the first group, you can use some
simple VBA to do that.
Open the report in design view.
Right Click on the group header and you will get the properties dialog
Select the Events Tab
Select On Format by clicking on the small command button to the right of it
with the 3 dots.
Select Code Builder.

The VBA editor will open with the cursor positioned in the sub.
Enter the following (sort of, I'll show the differences next)

If Me.txtGroupType = 0 Then
Me.GroupHeader1.Visible = False
Else
Me.GroupHeader1.Visible = False
End If

Now the differences.
txtGroupType is the name of the text box where you will show the group
type or the group description.
= 0 should be the value you will expect for the first group
GroupHeader1 is usually the default name for the first group header.

That should take care of hiding the header for the first group.

As to the sorting after that. I would not bother to sort the query. You
can use the next Group in the Sorting and Grouping dialog. It will sort on
whatever field or fields you select, but not including the first one already
identifyed. So everything that starts with 0 (using my example), will be in
the first group, but then if you use say contract type(using my example), The
report will show the contract type in order for each group. For example:
0 A
0 B
0 C
Group 1
1 A
1 C
1 D

Just don't select a group header or footer for the contract group.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

OK scratch that! The grouping is working.... I didnt have to change anything
I just didnt notice it was working the first time. What confused me is it is
putting the group header in front of both groups. I only want to header in
front of the second group. How do I get the first header to go away? Do I
need to put that first list in it's own group without a header?

I still dont know what to do about the sorting though.... should i add my
sorting field to the query? If so can I tell the query not to make the
sorting field visible or do I need to do that in the report like I did with
the List ID field?
--
Chels


"Chels" wrote:

OK you lost me a bit....
So the settings for my grouping are all fine? What do you mean by "You
should be grouping by groupType"?

How do I make a second group below? Am I making these groups to tell them
which records to put in which group? I dont really understand the properties
very much, sorry. Am I telling it to group ListID 0 together and ListID 1 in
another group? Will I need to add anything to my query?

If I am not understanding you sorry I am new to the grouping thing!
--
Chels


"Klatuu" wrote:

You group settings are correct. You should be grouping by GroupType. The
field I showed in my example that separates the list. You can also use the
sorting and grouping to order the groups. Just create another group below
the first group, but don't identify any headers or footers for the group.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Thank you so much! Using the union query to identify the lists worked
wonderfully. I am running into more problems now though.

I have another field I usually put in my queries just for sorting to keep
everything in order because the Unit #s dont keep themselves in the proper
order. How can i tell the query to sort with a field but not show it? When I
view the query everything is in the right order but when I view the report
they're not. Also I am a little confused as to how to do the grouping so
there is a header between the two lists. I did find the sorting and grouping
and i told it to group based on List ID. This is what I have

ListID Group Properties: ( I have List ID sorting Ascending, I dont know if
I should but I can't figure out how to tell it not to sort)
Group Header: Yes

 




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 10:25 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.