Control printing according to list box options

  • Thread starter Thread starter SBGFF
  • Start date Start date
S

SBGFF

I have two list box's that control the report that is going to print. Text20
is the list box that displays the shed number, and the drop down list varies
depending on what sheds are in the table. if a shed number is added or
deleted, it is also added or deleted in the list drop down box.The row
source is:
SELECT DISTINCT [98MatingRecords].[SHED #] FROM 98MatingRecords ORDER BY
[98MatingRecords].[SHED #];

The second is Text22, this list box has the sections in it and the sections
are controled by the shed number, some sheds have 1 section some have 6 and
some have 3 or 4, so the drop down list will vary from 1 to 6 according to
the shed that is selected. The row source for Text22 is:

SELECT DISTINCT [98MatingRecords].Section, [98MatingRecords].[SHED #] FROM
98MatingRecords WHERE ((([98MatingRecords].[SHED
#])=([Forms]![1,9,10-12DayRemates]![Text20]))) ORDER BY
[98MatingRecords].Section;

Right now in my print button I have this code

If Option66 = True Then

stDocName = "1,9,10-12DayRemates,Scratches"
DoCmd.OpenReport stDocName, acNormal
Text22 = Text22 + 1
If (Text22 >= (Forms![ProgramSetUp]![Text20] + 1)) Then
Text22 = 1
Text20.SetFocus
Text20 = Text20 + 1
End If

Else

stDocName = "1,9,10-12DayRemates"
DoCmd.OpenReport stDocName, acNormal
Text22 = Text22 + 1
If (Text22 >= (Forms![ProgramSetUp]![Text20] + 1)) Then
Text22 = 1
Text20.SetFocus
Text20 = Text20 + 1
End If
End If

What is suppose to happen is!
text20 is shed 9, shed 9 only has 1 section, so only 1 is in the list, we
pick it, we click print and it prints. As soon as it finnishes sending the
print job, it is to set text20 to the next shed which is 10, because there
is only 1 section in 9, But in shed 10 there are 4 sections, so it should
set text20 to 10 and text22 to 1 for the first section. after the job is
sent, it should set text22 to2, and so on till section 4 is reached, then
advance text20 to 11 for shed 11 and text22 to 1 for the 1st section. But
shed 11 only has 3 sections so after the 3rd section it should advance
Text22 to shed 51 because that is the next shed in the list for text20 and
text22 to 1 for the first section of that shed. shed 51 has 6 section so it
should advance text22 through the 6 sections then go to the next shed in the
list box, and so on and so on.

Can some one help me change this code to do this?

This line was used before the text box's were updated with the row source to
control the shed and sections
If (Text22 >= (Forms![ProgramSetUp]![Text20] + 1)) Then

It is now no longer needed but I don't know what to replace it with to
accomadate the new text box's row source

Thanks Blair
 
SBGFF,

It sounds as if what you are actually doing is from the selected starting
point of the selected shed, you want to print a report for each Section in
that shed and repeat the process for the remaining sheds, running the report
for each section of each shed.

If this is an accurate statement, then it might be simpler to modify your
report to to have it produce the report for all of the sections in each shed,
starting from the selected shed. You would simply need a sub report and have
the data deliver to the report the data for all remaing sheds and all
sections of those sheds. Then group your report by shed and have it produce a
new page for each section.

This way you would not have to worry abuot incrementing the values in text
boxes, but rather let the report do the work.

This was simply my observation. If this is not what you are trying to do,
please post back with more information.

--
HTH

Mr B


SBGFF said:
I have two list box's that control the report that is going to print. Text20
is the list box that displays the shed number, and the drop down list varies
depending on what sheds are in the table. if a shed number is added or
deleted, it is also added or deleted in the list drop down box.The row
source is:
SELECT DISTINCT [98MatingRecords].[SHED #] FROM 98MatingRecords ORDER BY
[98MatingRecords].[SHED #];

The second is Text22, this list box has the sections in it and the sections
are controled by the shed number, some sheds have 1 section some have 6 and
some have 3 or 4, so the drop down list will vary from 1 to 6 according to
the shed that is selected. The row source for Text22 is:

SELECT DISTINCT [98MatingRecords].Section, [98MatingRecords].[SHED #] FROM
98MatingRecords WHERE ((([98MatingRecords].[SHED
#])=([Forms]![1,9,10-12DayRemates]![Text20]))) ORDER BY
[98MatingRecords].Section;

Right now in my print button I have this code

If Option66 = True Then

stDocName = "1,9,10-12DayRemates,Scratches"
DoCmd.OpenReport stDocName, acNormal
Text22 = Text22 + 1
If (Text22 >= (Forms![ProgramSetUp]![Text20] + 1)) Then
Text22 = 1
Text20.SetFocus
Text20 = Text20 + 1
End If

Else

stDocName = "1,9,10-12DayRemates"
DoCmd.OpenReport stDocName, acNormal
Text22 = Text22 + 1
If (Text22 >= (Forms![ProgramSetUp]![Text20] + 1)) Then
Text22 = 1
Text20.SetFocus
Text20 = Text20 + 1
End If
End If

What is suppose to happen is!
text20 is shed 9, shed 9 only has 1 section, so only 1 is in the list, we
pick it, we click print and it prints. As soon as it finnishes sending the
print job, it is to set text20 to the next shed which is 10, because there
is only 1 section in 9, But in shed 10 there are 4 sections, so it should
set text20 to 10 and text22 to 1 for the first section. after the job is
sent, it should set text22 to2, and so on till section 4 is reached, then
advance text20 to 11 for shed 11 and text22 to 1 for the 1st section. But
shed 11 only has 3 sections so after the 3rd section it should advance
Text22 to shed 51 because that is the next shed in the list for text20 and
text22 to 1 for the first section of that shed. shed 51 has 6 section so it
should advance text22 through the 6 sections then go to the next shed in the
list box, and so on and so on.

Can some one help me change this code to do this?

This line was used before the text box's were updated with the row source to
control the shed and sections
If (Text22 >= (Forms![ProgramSetUp]![Text20] + 1)) Then

It is now no longer needed but I don't know what to replace it with to
accomadate the new text box's row source

Thanks Blair
 
If I understand you correctly, you are right, But I am not sure I understand
how your report would work. My report has 6 subreports in it now, for
different conditions that arise each day

What I really want the control button to do is each time I click it, it will
print the report, then automatically select the next section in that shed.
when it has gone through all the sections of that shed , advance to the next
shed and do it allover again till all the sheds have been gone through.
Right now I have to manually select each shed and section, I would like to
automate it.
Thanks for any help you can give
Blair

Mr B said:
SBGFF,

It sounds as if what you are actually doing is from the selected starting
point of the selected shed, you want to print a report for each Section in
that shed and repeat the process for the remaining sheds, running the
report
for each section of each shed.

If this is an accurate statement, then it might be simpler to modify your
report to to have it produce the report for all of the sections in each
shed,
starting from the selected shed. You would simply need a sub report and
have
the data deliver to the report the data for all remaing sheds and all
sections of those sheds. Then group your report by shed and have it
produce a
new page for each section.

This way you would not have to worry abuot incrementing the values in text
boxes, but rather let the report do the work.

This was simply my observation. If this is not what you are trying to do,
please post back with more information.

--
HTH

Mr B


SBGFF said:
I have two list box's that control the report that is going to print.
Text20
is the list box that displays the shed number, and the drop down list
varies
depending on what sheds are in the table. if a shed number is added or
deleted, it is also added or deleted in the list drop down box.The row
source is:
SELECT DISTINCT [98MatingRecords].[SHED #] FROM 98MatingRecords ORDER BY
[98MatingRecords].[SHED #];

The second is Text22, this list box has the sections in it and the
sections
are controled by the shed number, some sheds have 1 section some have 6
and
some have 3 or 4, so the drop down list will vary from 1 to 6 according
to
the shed that is selected. The row source for Text22 is:

SELECT DISTINCT [98MatingRecords].Section, [98MatingRecords].[SHED #]
FROM
98MatingRecords WHERE ((([98MatingRecords].[SHED
#])=([Forms]![1,9,10-12DayRemates]![Text20]))) ORDER BY
[98MatingRecords].Section;

Right now in my print button I have this code

If Option66 = True Then

stDocName = "1,9,10-12DayRemates,Scratches"
DoCmd.OpenReport stDocName, acNormal
Text22 = Text22 + 1
If (Text22 >= (Forms![ProgramSetUp]![Text20] + 1)) Then
Text22 = 1
Text20.SetFocus
Text20 = Text20 + 1
End If

Else

stDocName = "1,9,10-12DayRemates"
DoCmd.OpenReport stDocName, acNormal
Text22 = Text22 + 1
If (Text22 >= (Forms![ProgramSetUp]![Text20] + 1)) Then
Text22 = 1
Text20.SetFocus
Text20 = Text20 + 1
End If
End If

What is suppose to happen is!
text20 is shed 9, shed 9 only has 1 section, so only 1 is in the list, we
pick it, we click print and it prints. As soon as it finnishes sending
the
print job, it is to set text20 to the next shed which is 10, because
there
is only 1 section in 9, But in shed 10 there are 4 sections, so it should
set text20 to 10 and text22 to 1 for the first section. after the job is
sent, it should set text22 to2, and so on till section 4 is reached, then
advance text20 to 11 for shed 11 and text22 to 1 for the 1st section. But
shed 11 only has 3 sections so after the 3rd section it should advance
Text22 to shed 51 because that is the next shed in the list for text20
and
text22 to 1 for the first section of that shed. shed 51 has 6 section so
it
should advance text22 through the 6 sections then go to the next shed in
the
list box, and so on and so on.

Can some one help me change this code to do this?

This line was used before the text box's were updated with the row source
to
control the shed and sections
If (Text22 >= (Forms![ProgramSetUp]![Text20] + 1)) Then

It is now no longer needed but I don't know what to replace it with to
accomadate the new text box's row source

Thanks Blair
 
Back
Top