Need to print thousands of sequentially barcoded report pages

[Coastie]

I am trying to figure out how to print a sequential number on report pages. The number is printed as a barcode. I have been able to accomplish this by creating a table of numbers and creating a report tied to it. My problem is I need to make the printing available to more than one person and I need to not allow the same numbers to be duplicated.

The form is used as a cover sheet that is sent to a scanning company. We enter shell information into a database and scan the barcode sheet. That sheet then goes with a document to the scanner. The scanned barcode is used in the file name and the files are matched back to the input data.

The barcode sheet only contains a company header and the barcode in the middle of the page.

We will be processing about 2000 of these records per week.



I was trying to come up with a solution that would take a stored number and have the user type in the number of sheets they need to print and store the new ending number and then generate the report.


Any thoughts on how to accomplish this?

[Micron]

Can you elaborate your idea with examples to prevent us from interpreting it incorrectly?
What about using the number of pages a person decides on as a suffix to your main number? (-12 for example)

[Perceptus]

Yep, been here for this one. PITA. I had to create a local table for the Front end to store the information generated just for that user. Since each user has their own front end. the contents can be overwritten without an issue

[Coastie]

It is a basic form. The barcode will start with 1 and go forever. I anticipate printing them in batches of 50. the only reason to use a database is that I have about 14 people that will need to print these cover sheets and the barcoded numbers can't be repeated.
I am looking for a way to have a database that stores the last number printed and will allow the next person to select a number of sheets to print and have it start with the number after the last sheets printed. I have the code to actually produce the barcode just need to figure out how to generate the pages and keep the numbers from being reprinted.

[Micron]

Consider using x number of characters of the millisecond part of Time as the beginning part of your barcode and suffixing the number of pages on to that. I've found it to be a useful way of generating a unique number value as the chance that two or more concurrent users start a process at the same millisecond is practically impossible. Then you don't have to store any values unless you want to. You can start the Date/Time value from the day you release the db - you don't have to use the whole value of Time, which actually dates back to 1900 for PC's, I believe. If that doesn't excite you, then you will need a table that everyone uses, assuming I'm correct that you don't want anyone to be able to generate the same number because I think Perceptus' interpretation of that differs from mine. This will involve more code to get the last number, calculate the range, update the number and apply the numbers in a loop of report opening/printouts. Once that process starts, the next user's barcode range starts at the new number that the table was last updated to. The caveat is, if the first process fails, or the user is allowed to cancel after the number is generated, there will be gaps in the batches of reports printed versus the ranges and you have to live with that.

[Coastie]

Micron the end of your post "then you will need a table that everyone uses, assuming I'm correct that you don't want anyone to be able to generate the same number because I think Perceptus' interpretation of that differs from mine. This will involve more code to get the last number, calculate the range, update the number and apply the numbers in a loop of report opening/printouts. Once that process starts, the next user's barcode range starts at the new number that the table was last updated to." is what I am trying to accomplish. I know how to save the range to the table just can't figure out how to get the report to generate without a table of the actual numbers used. As far as a gap in printing due to a print failure or someone canceling a report, it doesn't matter if a number or range is never used. Just can't be duplicated.

Any help on the report generation would be helpful. I can make it work with a table of consecutive numbers then a separate table with the last number printed. I set up a query to take that number and add 1 to get the next beginning number and a variable to get the number of sheets to print.

What I was hoping for was a way to not have to pre populate a table full of numbers that will never be used for anything. Also this will be added to an existing database which has a front end for the users and a back end on the server.

[Micron]

What I was hoping for was a way to not have to pre populate a table full of numbers that will never be used for anything.

I suggested to use the millisecond part of time for that based on a recollection I had. After looking into it, I see that I had used the number of seconds elapsed since the date of implementation. This, or some sort of variation that produces a unique stamp is what I'm suggesting would not require storing data you won't need later. Can your report number not have a numeric identifier like that?

[Coastie]

I will have to have a discussion with the scanning contractor since they are using the barcodes for a file naming convention that will allow us to attach the file to the parent record. I don't see a real problem with that.
But how can I generate a report based on that? I cant seem to get more than 1 page at a time using a coded variable. I am sure I am missing something simple but cant seem to find an answer.

[Micron]

I am trying to figure out how to print a sequential number on report pages. The number is printed as a barcode. I have been able to accomplish this...

I cant seem to get more than 1 page at a time using a coded variable.

I'm getting confused as to what exactly you have achieved and what it is you want. Are you trying to:

-start at 1, have a certain number of zeros after 1 OR the other way around
-just start at 1 or some other point you've arrived at
-print (for ex.) 1000001 on page one, 1000002 on page two, 1000012 for the last (page 12) for one report, the next report starts at 1000013?**
OR
-100001 on each page of one report and the next report has 100002 on each page
- and in either case, prevent anyone else from replicating the number or range of numbers as the case may be?

If you've already figured out how to get a unique number on each page as your first post suggests, you can differentiate them simply by adding the page number as a suffix. You probably already know how to create page numbers in a footer, but as noted here, I no longer think I know what you want. To say you do not want to keep a table of numbers you're not using doesn't make sense to me. If it accomplishes what you need, I would say it's quite useful even if it only holds a single number (the last one generated), and will certainly not be a drag on your db. If you go that route, commit the number of pages to be printed and run an update query that increments the table value by that amount and you have your next starting point.

**Note: if you are looking to have a different code on each page, I think appending the page number would be the simplest by far. Otherwise, you will find yourself coding in the report Format Event and restricting the view to Print Preview.

[Coastie]

Micron.. Thank you for your assistance. I think you actually helped me answer my problem. So now I am at a point that I think I may need a new thread but will ask it here since you have solved most of this.
I think the best bet is to store the last number printed and update it when they get the print preview.
I can get the last number printed from the table but I can't figure out how to pass that to a query nor how to save the new value back to the table.
I am trying to pass the value from the button that opens the report preview to the parameter in the query. And also save the new number from there.

Here is the button code that I have set up:
Private Sub Command53_Click()
On Error GoTo Command53_Click_Err

Dim no_to_print As Integer
Dim new_last_no As Integer

new_last_no = lastnumber![LastNumberPrint] + [no_to_print]

[no_to_print] = InputBox("Enter number of sheets to print:", "Input Number to Print")

If inputData > 0 Then

' The next line is where I would like to use the variable in my query I am just displaying it for now
MsgBox inputData

End If

DoCmd.OpenReport "BarcodeEcapeSheet", acViewPreview, "", "", acNormal ' This report isbased on a query where I use the number no_to_print parameter to supply the number of sheets to print.

'This next line is where I would like to write the value to the the table lastnumber
lastnumber![LastNumberPrint] = [new_last_no]

Command53_Click_Exit:
Exit Sub

Command53_Click_Err:
MsgBox Error$
Resume Command53_Click_Exit

End Sub

[Micron]

My answers/comments in BLOCK CAPS only to make them easier to find within your last post.
Can't say much more than what's below - still too many unknowns for me.

Micron.. Thank you for your assistance. I think you actually helped me answer my problem. So now I am at a point that I think I may need a new thread but will ask it here since you have solved most of this. THIS THREAD SHOULD SUFFICE.
I think the best bet is to store the last number printed and update it when they get the print preview.
I can get the last number printed from the table but I can't figure out how to pass that to a query ++REFERENCE FORM TEXTBOX(ES) IN THE QUERY nor how to save the new value back to the table. RUN AN UPDATE QUERY USING ANOTHER FORM TEXTBOX THAT HOLDS THE NEW NUMBER DURING EXECUTION OF THE BUTTON CLICK.
I am trying to pass the value from the button that opens the report preview to the parameter in the query.[SEE ++ ABOVE] And also save the new number from there.
I DON'T SEE WHERE YOU'VE FIGURED OUT HOW ANY OF THIS GETS ONTO EACH PAGE OF A REPORT - PERHAPS BECAUSE I STILL DON'T KNOW IF IT'S THE SAME NUMBER ON EACH PAGE OR NOT.

Here is the button code that I have set up:
Private Sub Command53_Click()
On Error GoTo Command53_Click_Err
Dim no_to_print As Integer
Dim new_last_no As Integer

new_last_no = lastnumber![LastNumberPrint] + [no_to_print] <----THIS IS UNKNOWN***
[no_to_print] = InputBox("Enter number of sheets to print:", "Input Number to Print") THIS HAS TO BE BEFORE THE PREVIOUS (***).
WHAT IF MY TYPO RESULTS IN 1236 INSTEAD OF 123 BECAUSE MY FINGER HIT THE 3 AND 6, WHICH ARE TOGETHER?
If inputData > 0 Then YOU HAVE NOT DECLARED THIS IN THE SUB. HAVE YOU DONE SO AT THE TOP OF THIS FORM MODULE? YOU ARE EXPLICITLY DECLARING ALL VARIABLES??

' The next line is where I would like to use the variable in my query I am just displaying it for now
MsgBox inputData
End If

DoCmd.OpenReport "BarcodeEcapeSheet", acViewPreview, "", "", acNormal ' This report isbased on a query where I use the number no_to_print parameter to supply the number of sheets to print. AS MENTIONED, I DON'T SEE HOW THIS QUERY WILL ACCOMPLISH THE GOAL. PERHAPS ONLY BECAUSE YOU HAVE NOT REVEALED HOW.
'This next line is where I would like to write the value to the the table last number SEE 1ST PARAGRAPH
lastnumber![LastNumberPrint] = [new_last_no]
Command53_Click_Exit:
Exit Sub

Command53_Click_Err:
MsgBox Error$
Resume Command53_Click_Exit

End Sub