Reports & Object Dependencies

[justanotherstudent]

I have a customer who I have created custom macros for in the past in Excel.
Recently he asked me to look into a very small project in Access for one of
his clients which has turned out to be somewhat of a nightmare. I can't
figure this out. Any help would be greatly appreciated.

SHORT - The database is composed of tables, queries, and reports and is
primarily used to generate insurance reports. I need to create another report
for this year based of one of the existing reports except I can't find the
object dependencies for these reports listed anywhere in the database. I
realize I can copy and paste a new report but I need to change the SQL in one
of the queries that is listed as a dependency but is not in the actual list
of queries. If I do copy and paste a new report for the year 2008 and change
the SQL in the query it affects the previous report for the year 2007 because
they appear to still use the same dependency. My main question is how does a
report aqcuire a dependency that is listed as a query when you right click
the report to view dependencies but does not show up in the list of queries
from the database window?

LONG DESCRIPTION - What when the DB opens is a main form like a switchboard
except the previous developer used VBA to create this form. Every year the
main data table for the DB is updated but a new report is needed to summarize
the sales of the new year. So the main form or switchboard has a button that
opens another form with buttons for all the yearly reports. When you click on
one of those buttons one last form opens that has 4 different reports for
that single year. All of this is controlled by VBA code. The rest of the
functionality appears to be tables, reports, and queries that were created by
wizards within Access. The 4 different reports are summary, 25% of the
summary, 50% of the summary, and 75% of the summary. The reports for all the
different years and for each report for that year appear exactly the same.
The only difference is in the SQL by using a WHERE clause that pulls data for
each report from the DB based on the date. When I right click on each report
to view the object dependencies a window in the right pane appears that has
two options 1)objects that depend on me 2) objects that I depend on. The
first option never has any tables or queries listed but when I click on the
2nd option I see a list of 2 queries and the tables they get their data from
listed in the right hand pane. It's these queries listed as Object
dependencies that I need to replicate because they are where the actual SQL
resides. But in the Database Window when I click on Queries they are not
listed and if I try to create a new report from scratch, I still can't find
them listed anywhere.
What complicates this is I could just generate a new report based off a new
query that I create but the SQL is composed of two tables which should have
a relationship but are showing up as if they don't and a join statement which
isn't working for me when I try this. The client says I should be able to
copy and paste a new report based off another report but when I do this and
change the SQL in the object dependencies it also changes the SQL for the
report I copied from.

I hope this is something simple. I don't program in Access a whole lot and
the is becoming frustrating to me. I can't understand how a report could have
a list of dependencies that you can't find elsewhere in the DB? Even if I
could create a report from scratch using the same method of creating these
mysterious dependencies it would help but I don't understand enough about
Access to do this apparently.

Thanks for any help!

 

[Klatuu]

Short Description Answer.
The record source properties for both Forms and Reports can be a table, a
stored query, or a query built directly into the form or report. It will not
show in the list of queries. It is, in essence, a hidden query that you will
only see when you open the Report in design view and chose the data tab of
the properties box and open the record source by clicking on the small
command button with the 3 dots.

There are a couple of ways you can use that query as a basis for a new
report. One would be to make a copy of the report and modify it to meet the
requirements of the new report. The other would be to save the query as a
stored query. To do that, click on the blue disk "save" icon on the menu and
you can save the query as as stored query and work from there.

Long Description Answer

To have to create new reports each year actually means either the original
developer was marginally competent or that his/her honesty can be questioned.
I have seen developers that lock in support and maintenance hours by
creating routines that have to be changed on a periodic basis for the sole
purpose of generating revenue. The correct solution would be to modify the
reports and the application so that the same report can be used every year.

 

[justanotherstudent]

First off thanks for such a quick reply. When I follow the procedures you
outlined above and right click on the report in design view to see the
properties. I can see the field for each Control Source of the data tab but
not an entire query. By the way this is in Access 2003 and I'm not sure if
control source and record source are supposed to be the same thing but I did
not see a field for record source. For instance the label to show the
SubscriberID is set to the field SubscriberID in the data tab - control
source property. Is that right or should I be looking for an entire query?
All I see when I click on properties is a list of all the labels on the
report which each has it's own field.

I've tried to create a new report with my own query based off the SQl for
the other dependencies but because it joins two tables there appears to be a
problem in the SQL. I'm much more familiar with working in MS SQL than Access
so my first initiative was to see if I could replicate the report another way.

I agree on your comments about the previous developer. If he new the reports
needed to change on a yearly basis and came from the same data source then it
seems if he went to the trouble of creating some VBA code he would have
created another button or command to create a new report for each year.
Really the only thing that changes is the name of the report. I've more
experience with VBA in Excel than Access.

 

[Klatuu]

See answers in line below.
--
Dave Hargis, Microsoft Access MVP

First off thanks for such a quick reply. When I follow the procedures you
outlined above and right click on the report in design view to see the
properties. I can see the field for each Control Source of the data tab but
not an entire query.

Open the Dialog box and select Report so you are getting the properties for
the report. Select the Data tab. One of the properties on the data tab is
record source. There is a small command button with 3 dots on it to the
right of the record source text box. Click on that button and the query
builder will open.

By the way this is in Access 2003 and I'm not sure if

control source and record source are supposed to be the same thing but I did
not see a field for record source.

They are not the same thing. The record source defines the table or query
that provides data to the report. It is a property of the report. A control
source property is a property of a control. The control source property can
be used for one of two things. One is to define the field in the report's
record source that will be displayed in the control. You can select the
field by using the drop down to the right of the control source property in
the properties dialog when you have a control selected. The other is to
present calculated values. For example, let's say you want to present an
extended price based on the fields in your report's record source named
UnitPrice and QtyShipped. In the control source, you would put:

=[UnitPrice] * [QtyShipped]

It would then do the calculation and print the results on the report.

For instance the label to show the

SubscriberID is set to the field SubscriberID in the data tab - control
source property. Is that right or should I be looking for an entire query?

No, for a control, you want one field or you want a calculation as described
previously.

All I see when I click on properties is a list of all the labels on the
report which each has it's own field.

Actually, it is the other way around. labels are most often associated with
controls, but you can have a control without a label (you would have to
delete the label after you create the contro) or you can have a stand alone
label not associated with a control.

I've tried to create a new report with my own query based off the SQl for
the other dependencies but because it joins two tables there appears to be a
problem in the SQL. I'm much more familiar with working in MS SQL than Access
so my first initiative was to see if I could replicate the report another way.

Yes, there are some syntactial differences between SQL Sever SQL and Access
SQL, but shouldn't be much of a challenge, because Access SQL is actually not
as complex. It might be useful to use the query builder to get you started.
Using the icon in the upper left corner of the meny in query design mode,
you can switch between design, datasheet, and SQL views of the query.

I agree on your comments about the previous developer. If he new the reports
needed to change on a yearly basis and came from the same data source then it
seems if he went to the trouble of creating some VBA code he would have
created another button or command to create a new report for each year.
Really the only thing that changes is the name of the report. I've more
experience with VBA in Excel than Access.

If you are already familiar with VBA, then there is a bit of a learning
curve, but it should not be a big hill for your. One tip that may help as
you go forward. In Access VBA, you use the OpenReport method of the Docmd
object to run a report. There is a Where arugment in this method. The
syntax for it is exactly the same as it would be for an SQL Where clause
without the word WHERE. It is used for filtering the report's output. My
preferred method is to only filter the report's record source where the
filtering would apply to the data in all circumstances and use the Where
argument of the OpenReport for all other filtering. My point is, this is
where I would filter for data that applies to a specific year.

 

[justanotherstudent]

Ok thanks to your help I finally found the Report Source in the Report. FYI
you have to right click somewhere on the blue part of the form. Then select
properties and it's right there in the data tab as you described. I was right
clicking to view properties within the report itself in design view and it
only showed me a list of controls to select from.

Now I see that I can write a query in the in the query builder which I
should be able to get trough.

There is still something bugging me though that I am finding in all the
reports when I view the queries. They are selecting from another table that
just like the object dependencies does not show up in the list of available
tables. I can see the SQL when viewing the dependencies that the developer is
first querying a table then joining that first table with another table to
get the actual data. So when I look at these queries in the query builder I
see they are selecting and joining to the first table, which once again is
nowhere to be found. In SQL I have no problems with Joins etc but I still
don't understand how he is doing this. If I were to try and create a new
report I would not have the first table available to me. I'm sure I could
work around that but I was just trying to replicate the reports the same way.

By the way you have been more than helpful and I really appreciate your time.

 

[Klatuu]

It is possible the table is hidden or it is a system table.
If the table name starts with msys, it is a system table and you will not
see it unless you use Tools, Options, View and Click System Objects.
The same is true for a hidden table, you just click the Hidden Objects.

As to where to click, there are a number of ways to do it. You can first
select the properties dialog from the menu or right click anywhere in the
form to get the properties dialog. The you can select the specific object
from the dropdown at the top of the dialog.

 

[justanotherstudent]

Thanks for your help. I got most of this working now. One minor problem
though. One of the fields in the report is the customer name. When I run the
reports for the very first time a pop up box always shows up asking for me to
enter a name. It doesn't do this on the previous reports. Is there a property
for a control that needs to be set in order to stop this?

 

[Klatuu]

Is the report susposed to be filtered by customer name?
If you look at your query, I would bet there is something in the query like
[Enter Customer Name]. You need to remove that if the report is not to be
filtered. If it is, then you need to replace that prompt message with a
reference to a control on an open form that contain the name to use, or
modify your code so that you pass the customer name as the Where argument of
the OpenReport method.

 

[justanotherstudent]

I created a query based off the previous developers SQL. Here is the actual
statement.

SELECT HRT.MonthPaid, HRT.SubscriberID, SRC.Definition,
Format([HRT.Date_of_Birth],"m/d/yyyy") AS DOB, First(Left([LastName],1) &
LCase(Right([LastName],Len([LastName])-1)) & ", " & [Initial]) AS Name,
Sum(HRT.Paid_Amount) AS SumOfPaid_Amount, HRT.Sex_Rel_Code
FROM HudsonReportsTable AS HRT LEFT JOIN Sex_Relationship_Codes AS SRC ON
HRT.Sex_Rel_Code=SRC.Code
WHERE (((HRT.Service_Date) Between #7/1/2008# And #6/30/2009#) AND
((HRT.MonthPaid)>=#7/1/2008# And (HRT.MonthPaid)<=#6/30/2010#))
GROUP BY HRT.MonthPaid, HRT.SubscriberID, SRC.Definition,
Format([HRT.Date_of_Birth],"m/d/yyyy"), HRT.Sex_Rel_Code
ORDER BY HRT.MonthPaid, HRT.SubscriberID,
Format([HRT.Date_of_Birth],"m/d/yyyy"), First(Left([LastName],1) &
LCase(Right([LastName],Len([LastName])-1)) & ", " & [Initial]);

You can see the formatting where the last name and initial are being
combined as one column. All I did was add this select statement to the query
when I created it. I can't think of anything else. Does this look ok to you?

 

[Klatuu]

I think the problem is here:

First(Left([LastName],1) &
LCase(Right([LastName],Len([LastName])-1)) & ", " & [Initial]) AS Name

This is a totals query and each field has to be included in a domain
aggregate function. As written, you are only including the Last Name. All 3
field names need to be included.

There is an easier function to use to do what you are doing with the last
name.
It would be StrConv([LastName], 3)

First(strConv([LastName]) & ", " & [Initial])) AS Name
or
First(strConv([LastName])) & ", " & First([Initial]) AS Name


So, I would try:

 

[justanotherstudent]

Even if I omit the portion to select a name from the SQL a pop up was still
appearing. I think it has to do with the fact I copied the report from an
existing report and renamed it. There are two headers that are added that
have [SubscriberID] & [FirstOfName] etc. The value FirstOfName is a column
that doesn't exist in my query. When the pop up first appears it wants me to
enter a name. If I press ok without entering anything a new pop up appears
asking for FirstOfName so I think this is somehow connected to the group
headers. Question is how do I change it.

 

[Klatuu]

Check the controls on the report. It sounds like there is a control with a
control source that does not exist in the report's record source.

 

[justanotherstudent]

I think everything is good now. I changed the fields for the group headers
that had firstofname in them to name and the actual name control was named
firstofname. So I changed that to name. The pop ups disappeared and now
everything looks fine. Should be good for this round.

Thanks for your help.

 

[Klatuu]

Oh, yes. I forgot to mention sorting and grouping.
Glad you got it working.

 

[Kari]

I believe that "Name" is a reserved word, and therefore a bad name for a
control. If you use txName (for textboxes) or lbName (for list boxes), etc.
you don't have to worry about using a reserved word by mistake (e.g. "Name"
may be a reserved word, but "txName" would't be).

From my (limited) experience, using reserved words doesn't necessarily
result in an immediate crash, but can cause unpredictable behaviour down the
road (which, in my opinion, is worse than a crash).

I believe Allen Browne has a list of reserved words on his web site. (I
don't have the address handy, but if you search on his name in the discussion
boards you should be able to find it.)

Kari

 

[Klatuu]

That would be allen.browne.com

Better than a list, he has an Access app you can download that will scan
your mdb for reserved words, bad naming, etc.

Name is a reserved word.
The worst possible reserved word to use is Date. It will almost guarantee
an inccorect results if used in a calculation, even when bracketed.

 

[Douglas J. Steele]

Actually, Allen's site is allenbrowne.com, not allen.browne.com

The list is at http://www.allenbrowne.com/AppIssueBadWord.html It has a link
to http://www.allenbrowne.com/AppIssueChecker.html (the utility) at the top.

 

[Klatuu]

Thanks, Doug