Printing incremental number on report.

[Mario]

Hi ,
I have an Access application that prints a number of waybills for
several clients .
On a form i can select the client and add the number of waybills they
need.
(for example : HP - 100 waybills)
Now I need to add an incremental number on each waybill that is
printed.
I have another table called "tblCounter" with 1 field 'CounterNo".
I'd like to use the counter to start the numbering on the waybills and
update the counter when the reports are printed.
Can someone help me with this pls.
This is the code i use now.

Sub PrintWaybill()
Dim Language
Dim dbWaybill As DAO.Database
Dim rstCurWB As DAO.Recordset
Dim RecId, Aantal, RecCount

Set dbWaybill = CurrentDb()
Set rstCurWB = dbWaybill.OpenRecordset("QryPrintSelected",
dbOpenDynaset)

With rstCurWB
RecCount = .RecordCount
End With

If RecCount = 0 Or IsNull(RecCount) Or IsEmpty(RecCount) Then
MsgBox "No clients selected to print", vbInformation +
vbOKOnly, "Error Printing"
Else

rstCurWB.MoveFirst
While Not rstCurWB.EOF

RecId = rstCurWB.Fields("OrderID").Value
Aantal = rstCurWB.Fields("AantalAWB").Value ' number of
waybills needed

DoCmd.OpenReport "RptNewWaybill", acViewPreview, ,
"[OrderID]=" & RecId, acHidden
DoCmd.SelectObject acReport, "RptNewWaybill", False
DoCmd.PrintOut , , , , Aantal
DoCmd.Close acReport, "RptNewWaybill", acSaveNo

rstCurWB.Edit
rstCurWB.Fields("PrintAWB") = False
rstCurWB.Fields("AantalAWB") = 0
rstCurWB.Update
rstCurWB.MoveNext
Wend

End If
End Sub

 

[BruceM via AccessMonster.com]

I may be missing something, but you could have a text box with the Control
Source set to =1, and its Running Sum property set to Over All or Over Group,
depending on how the report is set up. This should number the records on the
report starting with 1, then 2, etc.

Hi ,
I have an Access application that prints a number of waybills for
several clients .
On a form i can select the client and add the number of waybills they
need.
(for example : HP - 100 waybills)
Now I need to add an incremental number on each waybill that is
printed.
I have another table called "tblCounter" with 1 field 'CounterNo".
I'd like to use the counter to start the numbering on the waybills and
update the counter when the reports are printed.
Can someone help me with this pls.
This is the code i use now.

Sub PrintWaybill()
Dim Language
Dim dbWaybill As DAO.Database
Dim rstCurWB As DAO.Recordset
Dim RecId, Aantal, RecCount

Set dbWaybill = CurrentDb()
Set rstCurWB = dbWaybill.OpenRecordset("QryPrintSelected",
dbOpenDynaset)

With rstCurWB
RecCount = .RecordCount
End With

If RecCount = 0 Or IsNull(RecCount) Or IsEmpty(RecCount) Then
MsgBox "No clients selected to print", vbInformation +
vbOKOnly, "Error Printing"
Else

rstCurWB.MoveFirst
While Not rstCurWB.EOF

RecId = rstCurWB.Fields("OrderID").Value
Aantal = rstCurWB.Fields("AantalAWB").Value ' number of
waybills needed

DoCmd.OpenReport "RptNewWaybill", acViewPreview, ,
"[OrderID]=" & RecId, acHidden
DoCmd.SelectObject acReport, "RptNewWaybill", False
DoCmd.PrintOut , , , , Aantal
DoCmd.Close acReport, "RptNewWaybill", acSaveNo

rstCurWB.Edit
rstCurWB.Fields("PrintAWB") = False
rstCurWB.Fields("AantalAWB") = 0
rstCurWB.Update
rstCurWB.MoveNext
Wend

End If
End Sub

 

[Mario]

Hello again ,
I found a solution to get the incremental number on the report.
I get the number from table "tblCounter" with the recordset rstcount
and put it in a variable intCounter

In the OnPrint event in the Report Detail section I add 1 to the
counter
intcounter = intcounter +1
and set the value of the unbound textfield "TxtCounter" with the
intcounter value.
me.TxtCounter.Value = intcounter

After printing i update the counterfield with the latest intcounter
value
rstcounter.Edit
rstcounter.Fields("CounterNo") = intcounter
rstcounter.Update


Any comment on this is highly appreciated.
Thanks
Mario

 

[BruceM via AccessMonster.com]

OK, so you're not starting from 1 on each report. I don't know the
parameters of what you are trying to do, but a couple of things occur to me.
It sounds as if you are resetting (increasing) the base number for the
counter each time you run a report. If you run the same report a second time,
from what I can tell it will have different numbers than it had the first
time. If this is so, I don't see the value of the numbers, as they are
neither a numbered list of records (1,2,3, etc.), nor are they part of the
records. The numbering seems to be dependent on when you run the report.
Further, if you preview the report without printing, the numbers will be
"used up" anyhow.

BTW, you refer to the recordset rstcount, then you show code for rstcounter.
I assume they are intended to be the same thing. It usually helps in the
newsgroups if you post the declarations along with the code, as it can be
easier to understand in context than as a separate explanation.

 

[Mario]

Hi Bruce ,
Thanks for the respons. Much appreciated.
I'll keep in mind to post clear code next time.

I just needed to get each printout a number , but increment if the
report is printed several times.
For instance if i print a report 10 or 100 times the first report
needs to get the latest countnbr from the rstcounter
and increment as it prints.So 1st printout gets nbr 1, 2nd printout
get nbr 2 and so on until the report has been printed several times.
If i need to print the same report again , the the numbering
continious from the last saved counter.

I know when the report is previewed the number will increase
automaticly , but the code does not allow preview.
Users cannot preview.

Thanks again for the respons.

 

[BruceM via AccessMonster.com]

I guess I'm not following what you need to do. It sounds as if you got it to
work, but I do not understand the business situation. It starts with my not
knowing what a waybill is. More to the point, does the detail section
contain several records. It sounds as if it does. If so, assuming there are
10 waybills (I think that's what is in the Detail section), does each waybill
get a number that is one higher than the previous one? If so, if you have
printed 10 waybills for Client 1, let's say they are numbered 1-10. Now you
print out 10 waybills for Client2 . Are they numbered 11-20, or do you
restart the numbering for each Client?

The question is essentially the same if you number the entire report of 10
waybills as 1, the second report for whatever number of waybills as 2, and so
forth.

Regarding the code you initially posted, you have declared Language as a
variable (variant, since you have not specified), but it does not seem to be
in the code. Also, you have declared several other variables as variant by
default since you have not specified otherwise. I think it is good practice
to use a specific variable type (e.g. Long for a number) when possible. I
don't think it makes a difference in your case. I'm just mentioning it.

Another point is that unless you need to know how many waybills you printed
out the last time, there seems no need to store the quantity. Rather, you
could use an Input box or an unbound text box on a form to specify the
quantity.

Then again, maybe there is a different number of waybills for each record in
QryPrintSelected, so you need to set the quantity for each record, then reset
to 0 after printing. Again, the business situation is hard to understand.