Multiple Subreports and field references in Subforms

[convexcube]

Hi everyone,

This is just a little rundown of a problem I discovered & the solution I found:

I have a report which runs off a query (query1). Within the report there are also 2 subreports which run off a further 2 queries. These 2 queries are both related to query1, and query1 uses a reference to a subform control in a criteria field. So in essence, to get any results in the report the value of the control in the subform is paramount and must be available before any of the calculations begin.

The problem I found is that this was not the case. The calculations of the queries would start before the value was retrieved from the control and the queries would then ask for parameter input, and also calculated controls on the main report didn't show a result but "Name?" instead. Although, the controls that directly referenced fields in query1 worked perfectly, so I knew the query was able to get the value from the subform control - it just seemed not quickly enough.

After much trial and tribulation with the syntax of the reference, I decided to make a hidden textbox on the main form that referenced the control on the subform. I then made query1 reference this textbox to get the value and now the whole report runs as it should.

I hope this tip can help someone and I invite further discussion on why this step was necessary to make it work.

Regards,
Ken.

[NeoPa]

It's a nice idea to post this ken, but I'm not sure we have enough detail to determine why this is behaving this way.
I wouldn't anticipate a subform control (correctly accessed) to behave any differently in your scenario from a control on the main form. Maybe it's your referencing the item that's wrong. It's hard to know for sure without access to all the details.

[convexcube]

Hi NeoPa,

Before coming up with this solution, I tried all the variants of referencing from an external module as you describe in this article (an excellent article, btw). Specifically:

1. Forms![FormName]![SubFormName].Form![ControlName]
2. Forms("FormName")![SubFormName].Form![ControlName]
3. Form_FormName![SubFormName].Form![ControlName]

One thing I didn't mention before is that I was able to get these to work initially, but when i saved and reloaded the database, it would display the behaviour I described in my previous post.
So I don't think the referencing syntax that was the problem. Other than this, while not specific I believe I supplied fairly detailed information. What extra information should I provide?
At any rate, I am pleased that I have found a solution that works, however inelegant it may be.

Regards,
Ken.

[NeoPa]

...
(an excellent article, btw)
...

Thanks :)

...
So I don't think the referencing syntax that was the problem. Other than this, while not specific I believe I supplied fairly detailed information. What extra information should I provide?

You did. I understood the situation you were describing. I simply didn't have enough detailed information upon which to build an idea of what was wrong. Remote diagnosis is actually heavily dependant on detailed information (most of the time - some questions are obvious). In your situation it was certainly not obvious what was wrong. You seemed to have a reasonable understanding of the issues, so what do I look for to explain it not working? The details is my only option.
The "#Name?" error generally means that something that you're trying to reference is not correctly done. I'm not aware this can be a timing issue. To progress my theory I'd need the details of the SQL and the reference in particular. Basically everything I need to check to try to determine what's going on for you.

At any rate, I am pleased that I have found a solution that works, however inelegant it may be.

And so am I. I wasn't trying to criticise your solution.

[convexcube]

Hi NeoPa,

First of all, I would like to thank you for taking the time to look at my issue, especially since I have a solution in place. After reading a lot of other posts I can understand why it can be frustrating when not enough information is supplied.

Here is the SQL of the query the main report is based on (note the reference to the subform in the WHERE statement):

SELECT Employees.*
FROM Employees
WHERE Employees.EmployeeIndex=Forms![Main]![Employees].Form![txtEmployeeIndex];

Then the SQL for the first subreport is as follows (it selects all events that are not uniform events for a specific employee - which is related to the sql of the main report (EmployeeDetailQuery):

SELECT Events.EventIndex, Events.EmployeeNumber, Events.EventDate, Events.EventType, Events.EventDetail, Events.RecordedBy, Events.ActionedBy, Events.ActionedOn, Events.LastEditedBy, Events.LastEditedOn
FROM Events INNER JOIN EmployeeDetailQuery ON Events.EmployeeNumber = EmployeeDetailQuery.EmployeeIndex
WHERE Events.EventType<>"4"
ORDER BY Events.EventDate DESC;

The second subreport has to work on events that are only uniform events so I must use an alternate query (EmployeeUniformEventsQuery) as an additional source which selects only the uniform events for this specific employee:

SELECT Events.EventIndex, Events.EmployeeNumber, Events.EventDate, Events.EventType, Events.EventDetail, Events.RecordedBy, Events.ActionedBy, Events.ActionedOn, Events.LastEditedBy, Events.LastEditedOn
FROM Events INNER JOIN EmployeeDetailQuery ON Events.EmployeeNumber = EmployeeDetailQuery.EmployeeIndex
WHERE Events.EventType="4"
ORDER BY Events.EventDate DESC;

then the SQL for the second subreport is

SELECT Uniforms.EventDate, Uniforms.UniformItem, Uniforms.ItemPrice, Uniforms.RecordedBy, EmployeeUniformEventsQuery.LastEditedBy, EmployeeUniformEventsQuery.LastEditedOn, Uniforms.EventIndex
FROM EmployeeUniformEventsQuery INNER JOIN Uniforms ON EmployeeUniformEventsQuery.EventIndex = Uniforms.EventIndex
ORDER BY Uniforms.EventDate DESC;

I apologise for the lack of naming conventions in these statements. This database was started before I had read up on that. The reference to the subform was tried in all syntaxes that you outlined in your article.

A couple of points I am pondering on are:

1. Could using SELECT * in the main report query rather than specifically referencing each field be a factor?
2. I noticed when I entered the subform reference in the main report query that it would apply square brackets around each of the sections like this: [Forms]![Main]![Employees].[Form]![txtEmployeeIndex]. This seemed to invalidate the reference, so in SQL view I removed them to read as the syntax of your article. Then, when I ran the report it would work. After saving and reloading the database, the adjusted reference would remain intact in SQL view, but running the report would ask for parameter input, and calculated controls showed the "#Name?" Error, even after entering the Employee Index manually. Though directly referenced fields would show correctly.

Whew! That was a long one. Hopefully I have provided enough information for you. There's no need for a quick response though. Once again, thanks for helping me nut this out.

Regards,
Ken.

[NeoPa]

A long post indeed :)
I will try to go through it properly this evening and see if I can see anything that might explain what you're seeing. It's entirely possible that I won't, but I'll post what I can anyway, giving particular attention to the last two points (questions) you raise.
Until later then.

[NeoPa]

I'm afraid at past midnight I haven't found time to look at this tonight.
I will try tomorrow (late again I'm afraid as it's setting up to become a busy day).

[convexcube]

No worries Neo. Take your time.

Regards,
Ken.

[NeoPa]

...
A couple of points I am pondering on are:

1. Could using SELECT * in the main report query rather than specifically referencing each field be a factor?
2. I noticed when I entered the subform reference in the main report query that it would apply square brackets around each of the sections like this: [Forms]![Main]![Employees].[Form]![txtEmployeeIndex]. This seemed to invalidate the reference, so in SQL view I removed them to read as the syntax of your article. Then, when I ran the report it would work. After saving and reloading the database, the adjusted reference would remain intact in SQL view, but running the report would ask for parameter input, and calculated controls showed the "#Name?" Error, even after entering the Employee Index manually. Though directly referenced fields would show correctly.

...

Ken,
As these two points seem to be the crux of the matter I'll deal first with them.

1. I'm really not sure about this. I believe it does make a difference, but probably only in performance and optimisation. I don't think this would effect any referencing you make anywhere.
2. Access has a nasty habit of doctoring SQL in such a way as to stop it from working. Play with parenthesised subqueries for an illustration of this. Strangely enough, the "fixed / mucked" SQL Access produces doesn't typically cause a problem until you try to make a change somewhere in the SQL itself. Then it complains about the SQL it's created :(
   It sound like this may well be what your problem is related to though. Hard to be definite, but it rings true to me.

Other ideas :

1. Clearly your current idea of adding a hidden control on the main form works.
2. It should be possible to update the RecordSource of your reports on-the-fly in the OnOpen event procedure. Inserting the value got from the form (subform) into the SQL itself as a literal value should produce the results you want quite reliably. You can even check the contents first and abort if there is no data in the control.

[convexcube]

Thanks Neo,

The points you raise make sense to me. For the moment I'm going to leave it as is, but I may have to look into building the SQL on the fly because I have several more reports structured like this to build yet and I would like to abort the print out if there are no records to show.

Once again, thanks for taking the time to look into it for me and I hope you and your family have a great Christmas & holiday season.

Kind Regards,
Ken.

[NeoPa]

No problems Ken and thank you for the Christmas wishes. You can have them right back of course, you and your whole family. Have a very Merry Christmas and a Happy New Year.

As you're interested in terminating a report that has no data in it, I'll just mention that there is an "On No Data" event. Coding this up is very simple and consists of the following (or more if required) :

Private Sub Report_NoData(Cancel As Integer)
    Cancel = True
End Sub