So you want to print a new report using the next report number and also store the details of the report e.g. report number and report details for future reprinting, emailing etc. ?
This opens up another option where you could save the report details as above in a table then use the DMAX function to determine the last number of the report printed to determine the next number.
Could you please send me a copy of your database and I will use it as guide to post an example on the forum so that other users can follow the steps etc.?
Allan - Each individual client transaction requires a number of steps, most are internal actions which are simply saved as time and date stamps, some require client and/or managerial notifications. These are to be saved as pdf's and emailed accordingly. Rather than save the date, I number the report and save the report number which also coincides with the pdf file name. Report numbering is also a customer requirement as well as an ISO requirement. On a monthly basis, a collection of all completed transactions will be emailed to the client along with a summary report as well as a quarterly and annual report, the quarterly and annual reports adding detailed financial info. Everything is keyed to an internal transaction number so that historical lookups will be based on that number. The app is currently planned to be used for a specific client, however much of the info generated is applicable to other clients so I've designed things so that with a little reconfiguration the app will allow the user to select the client and based on client info stored in a table, allow variations in workflow(looking forward) The numbering solution you gave works quite well and will serve us in several areas such as generating the original work order #, etc. Thanks to you and others in this forum, the app is getting closer to completion.
I will send you a copy of the db later in the week after I've smoothed out a few rough spots. Its larger than the forum allows so I'll email it to you. I will post a copy of the part that pertains to this forum also.
Sorry about getting off-track on the original posting about serializing Cats but I think the original question is a basic problem that applies to all sorts of applications, including my own.
Here is a sample db that remembers the last work order number (any number of digits) and updates it by 1 each time a new work order (or cat serial number) is added. Put your starting number in the WorkID field of the NextWorkNum Table. The resulting number can be concatenated with the rest of the cat's identification features to get a unique cat ID.
I'm using this method to generate work order #'s and report #'s in my app. What's nice is that if you cancel out from updating etc., you don't have to reset the number to the last #, it only updates to the next available # after you've done whatever.
Here is another version of your WorkNUm form. I have created a form frm_work_number that prompts the user to Save or abort entry. I tend to use unbound fields when adding records then use the Insert method to append to the relevant tables.
If the user selects Yes then it validates the entry, stores the entry details and then updates the next record number.
If the user selects No then the form closes without updating etc.
Just one question, why is the WoDate store as Now() this will cause problems when selecting reports by a date range?
In this particular example, Date() would make more sense - who cares what time the work order was issued, in my application some of the numbered reports refer to when a truck is unloaded or loaded. Having the time logged gives a reference that allows management to be able to review video logs if they suspect someone is playing 'games' with inventory. It also allows management to calculate how long it takes to unload or load a truck (start time is logged when the work order is selected to be processed) The work order is the primary key to the entire process and each step on the way to completion is logged and reported. The initial work order number will have Date() and the final completion report will have Date(), everything in between will be Now()
Nice sample db - I like the way you addressed error conditions
Report numbering
Reply With Quote
4 digit sequential serial update
