Need incrementing field on report (Access 2000)

Topic

New Reply

I need to place a field on a report that contains a number which automatically increases by 1 upon each printing. To give more details, I am working on a database which prints work orders, each one must have a unique #. The # cannot be the same for each customer, or I would simply place the AutoNumberID field on the work order, this won’t work since each one has to be different. This # does not need to be kept in a table, so unbound would probably work, but I can’t seem to figure how to code it. Any help would be much appreciated.
Thanks,
Michael

Reply | Quote

Replies

I’m not certain if I understand exactly what it is that you are trying to do? I assume that you just want a number on a report to be incremented by 1 each time you run or print the report?

You say that you do not need to keep the number is a table? I’m not sure what you mean by this, as the easiest way to remember what number you are up to each time is to store the number is a table.

I would use the Reports Open event to access a table and determine the next report value from that table and set a variable to hold the value. Then from the control on the report that I would want to display this number, I would reference a function which returns the current value of the variable.

Does this make any sense?

Reply | Quote

You may also be able to use the Print Event of the detail section of the report…if you only wish to increment the number each time the report is printed, rather than run?

What do others think?

Reply | Quote

What you’re saying makes a lot of sense, it’s just that I ‘m not sure how to make this happen. I have worked with vba for about three years, but I still have a lot to learn. Would you have time, or be willing to go into more detail on how to accomplish my goal.
The reason I said I don’t need to keep this in a table is simply that I can’t store this value in the same table as the customer information, because it would require many workorders per customer. If it would be best to create a table to store this #, that would be fine, except that 1.) I don’t exactly understand how to accomplish this and 2.) It is not needed after being printed (although I can see benefits to this).
Thank you so much for your help, and thanks in advance for any future help or advice,
– Michael

Reply | Quote

… Can I try a different angle. It appears that you have created a table to hold Customers where each customer can have only one unique ID. Each customer can also have multiple work orders. Could you clarify what makes the work order ID unique? Is every work order ID unique or is the Customer and Work Order combination unique such that you could re-use the same work order number for different customers.

You can number the work orders various ways. One way would be to set up a master Customer table holding the Customer Name, Unique Identifier, and other information as necessary. Set the Unique ID and Customer Name as the primary key. Create another table that holds work order information. This is where you need to define what makes the work order ID unique. Is every work order ID unique or is the Customer Unique ID and Work Order ID unique. (I am assuming that every work order ID is unique.)

The work order table will hold work order information including the Customer Unique ID. Link the Customer Unique ID and Customer Name (Primary Key) in the primary table as a one to many link to the Customer Unique ID, Customer Name, and Work Order unique ID in the Work Order table. You could then set the work order table to create a new ID every time a work order is created thus creating unique work orders that are linked to the Customer.

Good Luck.

Reply | Quote

Thanks for the response. As far as what makes the work order # unique, it’s fairly straightforward. Each work order has a different number that is printed on it. The easiest way to do this would have been to have the printer put the numbers on the work orders when they were printed, unfortunately though, I don’t have the ability to have this done. I really just need to have the work orders printed as follows:

Customer Name // Work Order #
Jim Smith // 000001
Sally Jones // 000002
Frank Sanders //000003
Jim Smith // 000004

Each Work Order would have a different #, even if you printed 2 or more for the same customer (note Jim Smith example above) they would each have a different number.
Again, Thanks for your suggestions and feel free to give any further input you may have based on my response.
Thanks, Michael

Reply | Quote

I think what it looks like you need is actually a ‘work-order’ table, with the work-order number an auto-number field. The work-order table would be in a one-to-many relationship with the customers table, with the customer table being the one side of the relationship.

The work-order table would contain the customer number, the work order number and any details you wish to record about that particular work order itself.

I think that you are perhaps going about it the wrong way wanting to print a new number each time a work order is printed…what happens if you want to reprint a work order, it would be given a different number, which is not true as it would just be a reprint.

Reply | Quote

I agree with Jayden’s response and am also confused as to why you would create a new number each time a work order is printed.

You could auto increment the work order id in a work order table and use a report to print the work orders, thus giving you the ability to generate the unique work order numbers as needed and also print out the work orders without changing the work order unqiue id.

Reply | Quote