how to select parameter in subreport when main report is opened

[Kelly]

Does anyone know how I can code and event on open for the main report to look
for a parameter in the subreport. I have a field in the subreport called
RevisonNumber. In the query for the subreport the criteria is set to Like
[Which Revision]. This pops up several times when you open the main report.
When I tried to place the like criteria on the main reports query it did not
work it showed all revisions.


Kelly

 

[Ken Snell [MVP]]

The subreport runs its RecordSource query before the main report is fully
ready. So each time the subreport must run, you'll get that parameter
prompt, assuming that the parameter is a value that is supposed to come from
the main report.

Is that Which Revision value something that you're providing to the main
report when you open the report, say a control on a form? If yes, change the
subreport's query to get its parameter value from that form.

If you're doing something else, let us know what your setup is.

 

[Kelly]

The setup for the main report is information about a particular job. It was
requested if a revision sheet could be placed at the end of the report. Since
I am new to Access I did not know about adding a subreport to the footer. So
the initial report was RevisonWorking. Since then I added this as a subreport
which doesn't have anything in common with the main report except a
child/master relationship to a drawing number. In the query for
RevisonWorking I set the criteria to Like [Which Revison] so when they opened
the report it would ask the question and narrow the report to one particular
revision. Maybe there is a better way of doing it I'm up for suggestions but
the ultimate goal is to select the latest revison (notes, number, date,
prepared) and have it automatically placed at the end of the report.

The follow is the SQL for the Main report.

SELECT Circuit.RevStatus, Circuit.CircuitNumber, Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable, Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size, Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks, Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON Circuit.CircuitNumber =
Circuit_detail.CircuitNum
WHERE (((Circuit.RevStatus) Like "A" Or (Circuit.RevStatus)="C" Or
(Circuit.RevStatus)="D"));

the sub report

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber = Revision.DrawingNumber
WHERE (((Revision.RevisonNumber) Like [Which Revision]));

Kelly

The subreport runs its RecordSource query before the main report is fully
ready. So each time the subreport must run, you'll get that parameter
prompt, assuming that the parameter is a value that is supposed to come from
the main report.

Is that Which Revision value something that you're providing to the main
report when you open the report, say a control on a form? If yes, change the
subreport's query to get its parameter value from that form.

If you're doing something else, let us know what your setup is.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Does anyone know how I can code and event on open for the main report to
look
for a parameter in the subreport. I have a field in the subreport called
RevisonNumber. In the query for the subreport the criteria is set to Like
[Which Revision]. This pops up several times when you open the main
report.
When I tried to place the like criteria on the main reports query it did
not
work it showed all revisions.


Kelly

 

[Ken Snell [MVP]]

So your goal for the subreport is to show the data for the most recent
revision (based on the value in the Revision.RevisionDate field) for the
main report's Title.DrawingNumber value?

If this is correct, change your subreport's query to this:

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE Revision.RevisionDate =
(SELECT Max(R.RevisonDate) AS MRD FROM
Revision AS R WHERE
R.DrawingNumber = Title.DrawingNumber);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

The setup for the main report is information about a particular job. It
was
requested if a revision sheet could be placed at the end of the report.
Since
I am new to Access I did not know about adding a subreport to the footer.
So
the initial report was RevisonWorking. Since then I added this as a
subreport
which doesn't have anything in common with the main report except a
child/master relationship to a drawing number. In the query for
RevisonWorking I set the criteria to Like [Which Revison] so when they
opened
the report it would ask the question and narrow the report to one
particular
revision. Maybe there is a better way of doing it I'm up for suggestions
but
the ultimate goal is to select the latest revison (notes, number, date,
prepared) and have it automatically placed at the end of the report.

The follow is the SQL for the Main report.

SELECT Circuit.RevStatus, Circuit.CircuitNumber, Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable,
Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size, Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks, Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON Circuit.CircuitNumber =
Circuit_detail.CircuitNum
WHERE (((Circuit.RevStatus) Like "A" Or (Circuit.RevStatus)="C" Or
(Circuit.RevStatus)="D"));

the sub report

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE (((Revision.RevisonNumber) Like [Which Revision]));

Kelly

The subreport runs its RecordSource query before the main report is fully
ready. So each time the subreport must run, you'll get that parameter
prompt, assuming that the parameter is a value that is supposed to come
from
the main report.

Is that Which Revision value something that you're providing to the main
report when you open the report, say a control on a form? If yes, change
the
subreport's query to get its parameter value from that form.

If you're doing something else, let us know what your setup is.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Does anyone know how I can code and event on open for the main report
to
look
for a parameter in the subreport. I have a field in the subreport
called
RevisonNumber. In the query for the subreport the criteria is set to
Like
[Which Revision]. This pops up several times when you open the main
report.
When I tried to place the like criteria on the main reports query it
did
not
work it showed all revisions.


Kelly

 

[Kelly]

Ken thanks for the reply. I did as you stated but it ask for the date of the
revision three times. Two to get into the main report then once to go to the
last page. I tried using the code with the RevisionNumber but I had to enter
it 5 times and it gave me all the revisions. Is Max reserved for just dates?
How can I get a specific revision number in the subform without having to
type in so much when you open the main form.

Kelly

So your goal for the subreport is to show the data for the most recent
revision (based on the value in the Revision.RevisionDate field) for the
main report's Title.DrawingNumber value?

If this is correct, change your subreport's query to this:

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE Revision.RevisionDate =
(SELECT Max(R.RevisonDate) AS MRD FROM
Revision AS R WHERE
R.DrawingNumber = Title.DrawingNumber);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

The setup for the main report is information about a particular job. It
was
requested if a revision sheet could be placed at the end of the report.
Since
I am new to Access I did not know about adding a subreport to the footer.
So
the initial report was RevisonWorking. Since then I added this as a
subreport
which doesn't have anything in common with the main report except a
child/master relationship to a drawing number. In the query for
RevisonWorking I set the criteria to Like [Which Revison] so when they
opened
the report it would ask the question and narrow the report to one
particular
revision. Maybe there is a better way of doing it I'm up for suggestions
but
the ultimate goal is to select the latest revison (notes, number, date,
prepared) and have it automatically placed at the end of the report.

The follow is the SQL for the Main report.

SELECT Circuit.RevStatus, Circuit.CircuitNumber, Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable,
Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size, Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks, Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON Circuit.CircuitNumber =
Circuit_detail.CircuitNum
WHERE (((Circuit.RevStatus) Like "A" Or (Circuit.RevStatus)="C" Or
(Circuit.RevStatus)="D"));

the sub report

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE (((Revision.RevisonNumber) Like [Which Revision]));

Kelly

The subreport runs its RecordSource query before the main report is fully
ready. So each time the subreport must run, you'll get that parameter
prompt, assuming that the parameter is a value that is supposed to come
from
the main report.

Is that Which Revision value something that you're providing to the main
report when you open the report, say a control on a form? If yes, change
the
subreport's query to get its parameter value from that form.

If you're doing something else, let us know what your setup is.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Does anyone know how I can code and event on open for the main report
to
look
for a parameter in the subreport. I have a field in the subreport
called
RevisonNumber. In the query for the subreport the criteria is set to
Like
[Which Revision]. This pops up several times when you open the main
report.
When I tried to place the like criteria on the main reports query it
did
not
work it showed all revisions.


Kelly

 

[Ken Snell [MVP]]

I apparently am not understanding something about your setup. Is the
subreport to show the latest revision for a drawing number? Or something
else? Is the main report showing the data for a single drawing number? or
something else?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken thanks for the reply. I did as you stated but it ask for the date of
the
revision three times. Two to get into the main report then once to go to
the
last page. I tried using the code with the RevisionNumber but I had to
enter
it 5 times and it gave me all the revisions. Is Max reserved for just
dates?
How can I get a specific revision number in the subform without having to
type in so much when you open the main form.

Kelly

So your goal for the subreport is to show the data for the most recent
revision (based on the value in the Revision.RevisionDate field) for the
main report's Title.DrawingNumber value?

If this is correct, change your subreport's query to this:

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE Revision.RevisionDate =
(SELECT Max(R.RevisonDate) AS MRD FROM
Revision AS R WHERE
R.DrawingNumber = Title.DrawingNumber);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

The setup for the main report is information about a particular job. It
was
requested if a revision sheet could be placed at the end of the report.
Since
I am new to Access I did not know about adding a subreport to the
footer.
So
the initial report was RevisonWorking. Since then I added this as a
subreport
which doesn't have anything in common with the main report except a
child/master relationship to a drawing number. In the query for
RevisonWorking I set the criteria to Like [Which Revison] so when they
opened
the report it would ask the question and narrow the report to one
particular
revision. Maybe there is a better way of doing it I'm up for
suggestions
but
the ultimate goal is to select the latest revison (notes, number, date,
prepared) and have it automatically placed at the end of the report.

The follow is the SQL for the Main report.

SELECT Circuit.RevStatus, Circuit.CircuitNumber, Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable,
Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size,
Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks, Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON Circuit.CircuitNumber
=
Circuit_detail.CircuitNum
WHERE (((Circuit.RevStatus) Like "A" Or (Circuit.RevStatus)="C" Or
(Circuit.RevStatus)="D"));

the sub report

SELECT Revision.DrawingNumber, Revision.RevisionDate,
Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE (((Revision.RevisonNumber) Like [Which Revision]));

Kelly


:

The subreport runs its RecordSource query before the main report is
fully
ready. So each time the subreport must run, you'll get that parameter
prompt, assuming that the parameter is a value that is supposed to
come
from
the main report.

Is that Which Revision value something that you're providing to the
main
report when you open the report, say a control on a form? If yes,
change
the
subreport's query to get its parameter value from that form.

If you're doing something else, let us know what your setup is.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Does anyone know how I can code and event on open for the main
report
to
look
for a parameter in the subreport. I have a field in the subreport
called
RevisonNumber. In the query for the subreport the criteria is set to
Like
[Which Revision]. This pops up several times when you open the main
report.
When I tried to place the like criteria on the main reports query it
did
not
work it showed all revisions.


Kelly

 

[Kelly]

Ken I had to make an adjustment to the table because I created a cartesian
product by not joining one of the tables. Long story on why but here is the
SQL for the subreport now.

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Revision
WHERE (((Revision.RevisonNumber) Like [Which Revision]));

You are correct the subform needs to show the last revision for that drawing
number and the main is showing the data for the same drawing the revision is
showing. Only one drawing for this setup. The parameter popup for [Which
Revision] looks like it is responding to the four revisions listed in the
Revision table.

Another factor is the setup from the main forms. You mentioned on the first
response to have the subreport's query get the parameter value from the form.
I tried this and it asked six times for the revision number. Three with Which
revision and three with Forms!RevisionSubform!RevisonNumber. Below is what I
placed in the query. What am I doing wrong and is there a way to avoid
showing this until the main report is ready.

Forms![RevisionSubform]![RevisonNumber] Like [Which Revison]

You can tell I'm not sure what I am doing Ha! But I keep trying....
Kelly

I apparently am not understanding something about your setup. Is the
subreport to show the latest revision for a drawing number? Or something
else? Is the main report showing the data for a single drawing number? or
something else?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken thanks for the reply. I did as you stated but it ask for the date of
the
revision three times. Two to get into the main report then once to go to
the
last page. I tried using the code with the RevisionNumber but I had to
enter
it 5 times and it gave me all the revisions. Is Max reserved for just
dates?
How can I get a specific revision number in the subform without having to
type in so much when you open the main form.

Kelly

So your goal for the subreport is to show the data for the most recent
revision (based on the value in the Revision.RevisionDate field) for the
main report's Title.DrawingNumber value?

If this is correct, change your subreport's query to this:

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE Revision.RevisionDate =
(SELECT Max(R.RevisonDate) AS MRD FROM
Revision AS R WHERE
R.DrawingNumber = Title.DrawingNumber);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



The setup for the main report is information about a particular job. It
was
requested if a revision sheet could be placed at the end of the report.
Since
I am new to Access I did not know about adding a subreport to the
footer.
So
the initial report was RevisonWorking. Since then I added this as a
subreport
which doesn't have anything in common with the main report except a
child/master relationship to a drawing number. In the query for
RevisonWorking I set the criteria to Like [Which Revison] so when they
opened
the report it would ask the question and narrow the report to one
particular
revision. Maybe there is a better way of doing it I'm up for
suggestions
but
the ultimate goal is to select the latest revison (notes, number, date,
prepared) and have it automatically placed at the end of the report.

The follow is the SQL for the Main report.

SELECT Circuit.RevStatus, Circuit.CircuitNumber, Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable,
Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size,
Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks, Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON Circuit.CircuitNumber
=
Circuit_detail.CircuitNum
WHERE (((Circuit.RevStatus) Like "A" Or (Circuit.RevStatus)="C" Or
(Circuit.RevStatus)="D"));

the sub report

SELECT Revision.DrawingNumber, Revision.RevisionDate,
Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE (((Revision.RevisonNumber) Like [Which Revision]));

Kelly


:

The subreport runs its RecordSource query before the main report is
fully
ready. So each time the subreport must run, you'll get that parameter
prompt, assuming that the parameter is a value that is supposed to
come
from
the main report.

Is that Which Revision value something that you're providing to the
main
report when you open the report, say a control on a form? If yes,
change
the
subreport's query to get its parameter value from that form.

If you're doing something else, let us know what your setup is.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Does anyone know how I can code and event on open for the main
report
to
look
for a parameter in the subreport. I have a field in the subreport
called
RevisonNumber. In the query for the subreport the criteria is set to
Like
[Which Revision]. This pops up several times when you open the main
report.
When I tried to place the like criteria on the main reports query it
did
not
work it showed all revisions.


Kelly

 

[Ken Snell [MVP]]

OK... let's try this:

Set the LinkChildFields property of the subreport control to
DrawingNumber

Set the LinkMasterFields property of the subreport control to
DrawingNumber

Use this query as the subreport's RecordSource property:

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Revision
WHERE Revision.RevisionDate =
(SELECT Max(R.RevisonDate) AS MRD FROM
Revision AS R WHERE
R.DrawingNumber = Revision.DrawingNumber);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken I had to make an adjustment to the table because I created a cartesian
product by not joining one of the tables. Long story on why but here is
the
SQL for the subreport now.

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Revision
WHERE (((Revision.RevisonNumber) Like [Which Revision]));

You are correct the subform needs to show the last revision for that
drawing
number and the main is showing the data for the same drawing the revision
is
showing. Only one drawing for this setup. The parameter popup for [Which
Revision] looks like it is responding to the four revisions listed in the
Revision table.

Another factor is the setup from the main forms. You mentioned on the
first
response to have the subreport's query get the parameter value from the
form.
I tried this and it asked six times for the revision number. Three with
Which
revision and three with Forms!RevisionSubform!RevisonNumber. Below is what
I
placed in the query. What am I doing wrong and is there a way to avoid
showing this until the main report is ready.

Forms![RevisionSubform]![RevisonNumber] Like [Which Revison]

You can tell I'm not sure what I am doing Ha! But I keep trying....
Kelly

I apparently am not understanding something about your setup. Is the
subreport to show the latest revision for a drawing number? Or something
else? Is the main report showing the data for a single drawing number? or
something else?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken thanks for the reply. I did as you stated but it ask for the date
of
the
revision three times. Two to get into the main report then once to go
to
the
last page. I tried using the code with the RevisionNumber but I had to
enter
it 5 times and it gave me all the revisions. Is Max reserved for just
dates?
How can I get a specific revision number in the subform without having
to
type in so much when you open the main form.

Kelly


:

So your goal for the subreport is to show the data for the most recent
revision (based on the value in the Revision.RevisionDate field) for
the
main report's Title.DrawingNumber value?

If this is correct, change your subreport's query to this:

SELECT Revision.DrawingNumber, Revision.RevisionDate,
Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE Revision.RevisionDate =
(SELECT Max(R.RevisonDate) AS MRD FROM
Revision AS R WHERE
R.DrawingNumber = Title.DrawingNumber);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



The setup for the main report is information about a particular job.
It
was
requested if a revision sheet could be placed at the end of the
report.
Since
I am new to Access I did not know about adding a subreport to the
footer.
So
the initial report was RevisonWorking. Since then I added this as a
subreport
which doesn't have anything in common with the main report except a
child/master relationship to a drawing number. In the query for
RevisonWorking I set the criteria to Like [Which Revison] so when
they
opened
the report it would ask the question and narrow the report to one
particular
revision. Maybe there is a better way of doing it I'm up for
suggestions
but
the ultimate goal is to select the latest revison (notes, number,
date,
prepared) and have it automatically placed at the end of the report.

The follow is the SQL for the Main report.

SELECT Circuit.RevStatus, Circuit.CircuitNumber,
Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable,
Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size,
Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks,
Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON
Circuit.CircuitNumber
=
Circuit_detail.CircuitNum
WHERE (((Circuit.RevStatus) Like "A" Or (Circuit.RevStatus)="C" Or
(Circuit.RevStatus)="D"));

the sub report

SELECT Revision.DrawingNumber, Revision.RevisionDate,
Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE (((Revision.RevisonNumber) Like [Which Revision]));

Kelly


:

The subreport runs its RecordSource query before the main report is
fully
ready. So each time the subreport must run, you'll get that
parameter
prompt, assuming that the parameter is a value that is supposed to
come
from
the main report.

Is that Which Revision value something that you're providing to the
main
report when you open the report, say a control on a form? If yes,
change
the
subreport's query to get its parameter value from that form.

If you're doing something else, let us know what your setup is.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Does anyone know how I can code and event on open for the main
report
to
look
for a parameter in the subreport. I have a field in the subreport
called
RevisonNumber. In the query for the subreport the criteria is set
to
Like
[Which Revision]. This pops up several times when you open the
main
report.
When I tried to place the like criteria on the main reports query
it
did
not
work it showed all revisions.


Kelly

 

[Barry A&P]

I usually have no idea what i am talking about but can kelly get rid of the
sub report and just do a unbound control with Dmax on the revision number
WHERE drawing numbers are =
Sorry i dont know syntax

Barry

OK... let's try this:

Set the LinkChildFields property of the subreport control to
DrawingNumber

Set the LinkMasterFields property of the subreport control to
DrawingNumber

Use this query as the subreport's RecordSource property:

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Revision
WHERE Revision.RevisionDate =
(SELECT Max(R.RevisonDate) AS MRD FROM
Revision AS R WHERE
R.DrawingNumber = Revision.DrawingNumber);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken I had to make an adjustment to the table because I created a cartesian
product by not joining one of the tables. Long story on why but here is
the
SQL for the subreport now.

SELECT Revision.DrawingNumber, Revision.RevisionDate, Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Revision
WHERE (((Revision.RevisonNumber) Like [Which Revision]));

You are correct the subform needs to show the last revision for that
drawing
number and the main is showing the data for the same drawing the revision
is
showing. Only one drawing for this setup. The parameter popup for [Which
Revision] looks like it is responding to the four revisions listed in the
Revision table.

Another factor is the setup from the main forms. You mentioned on the
first
response to have the subreport's query get the parameter value from the
form.
I tried this and it asked six times for the revision number. Three with
Which
revision and three with Forms!RevisionSubform!RevisonNumber. Below is what
I
placed in the query. What am I doing wrong and is there a way to avoid
showing this until the main report is ready.

Forms![RevisionSubform]![RevisonNumber] Like [Which Revison]

You can tell I'm not sure what I am doing Ha! But I keep trying....
Kelly

I apparently am not understanding something about your setup. Is the
subreport to show the latest revision for a drawing number? Or something
else? Is the main report showing the data for a single drawing number? or
something else?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Ken thanks for the reply. I did as you stated but it ask for the date
of
the
revision three times. Two to get into the main report then once to go
to
the
last page. I tried using the code with the RevisionNumber but I had to
enter
it 5 times and it gave me all the revisions. Is Max reserved for just
dates?
How can I get a specific revision number in the subform without having
to
type in so much when you open the main form.

Kelly


:

So your goal for the subreport is to show the data for the most recent
revision (based on the value in the Revision.RevisionDate field) for
the
main report's Title.DrawingNumber value?

If this is correct, change your subreport's query to this:

SELECT Revision.DrawingNumber, Revision.RevisionDate,
Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE Revision.RevisionDate =
(SELECT Max(R.RevisonDate) AS MRD FROM
Revision AS R WHERE
R.DrawingNumber = Title.DrawingNumber);

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



The setup for the main report is information about a particular job.
It
was
requested if a revision sheet could be placed at the end of the
report.
Since
I am new to Access I did not know about adding a subreport to the
footer.
So
the initial report was RevisonWorking. Since then I added this as a
subreport
which doesn't have anything in common with the main report except a
child/master relationship to a drawing number. In the query for
RevisonWorking I set the criteria to Like [Which Revison] so when
they
opened
the report it would ask the question and narrow the report to one
particular
revision. Maybe there is a better way of doing it I'm up for
suggestions
but
the ultimate goal is to select the latest revison (notes, number,
date,
prepared) and have it automatically placed at the end of the report.

The follow is the SQL for the Main report.

SELECT Circuit.RevStatus, Circuit.CircuitNumber,
Circuit.ServiceType,
Circuit.Volts, Circuit.FromEquipment, Circuit.ToEquipment,
Circuit.CablesInCircuit, Circuit.LengthOfCable,
Circuit_detail.WiresInCable,
Circuit_detail.WiresNotUsed, Circuit_detail.Size,
Circuit_detail.Material,
Title.DrawingNumber, Title.RevNumCurrent, Title.Remarks,
Title.Station,
Title.Plant, Title.Company, Title.Title, Title.Location
FROM Title, Circuit INNER JOIN Circuit_detail ON
Circuit.CircuitNumber
=
Circuit_detail.CircuitNum
WHERE (((Circuit.RevStatus) Like "A" Or (Circuit.RevStatus)="C" Or
(Circuit.RevStatus)="D"));

the sub report

SELECT Revision.DrawingNumber, Revision.RevisionDate,
Revision.Prepared,
Revision.Checked, Revision.RevisionNote, Revision.RevisonNumber
FROM Title INNER JOIN Revision ON Title.DrawingNumber =
Revision.DrawingNumber
WHERE (((Revision.RevisonNumber) Like [Which Revision]));

Kelly


:

The subreport runs its RecordSource query before the main report is
fully
ready. So each time the subreport must run, you'll get that
parameter
prompt, assuming that the parameter is a value that is supposed to
come
from
the main report.

Is that Which Revision value something that you're providing to the
main
report when you open the report, say a control on a form? If yes,
change
the
subreport's query to get its parameter value from that form.

If you're doing something else, let us know what your setup is.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Does anyone know how I can code and event on open for the main
report
to
look
for a parameter in the subreport. I have a field in the subreport
called
RevisonNumber. In the query for the subreport the criteria is set
to
Like
[Which Revision]. This pops up several times when you open the
main
report.
When I tried to place the like criteria on the main reports query
it
did
not
work it showed all revisions.


Kelly

 

[Ken Snell [MVP]]

I usually have no idea what i am talking about but can kelly get rid of the
sub report and just do a unbound control with Dmax on the revision number
WHERE drawing numbers are =
Sorry i dont know syntax

My understanding of Kelly's original question is that she wanted to show all
the data for that particular revision, so a single control using DMax would
not allow that.