Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    375

    Is it possible to create unique numbers every time you create a report using print/pr

    Dear All, I have a small db and I often want to print reports (statement) using a query on subform. Everything works fine and I can print the report with all the required fields.
    My manager has asked me that everytime I print a report (statement) it should create a unique number at the top of the report (Lets call it master invoice number). And I couldnt figure it out as to what would be the best course of action?

    Any help and idea would be much appreciated.

    Emi
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Mar 2004
    Location
    California
    Posts
    475
    How about =Date() in the report header or footer.

    From the Properties Menu format the Date field to be GeneralDate and that will give you the time also. Like 4/8/2007 08:39:26.

    The TIME will always be unique!

  3. #3
    Join Date
    May 2006
    Posts
    375
    Dear Rick, Thank you for your time.
    But I want the report to create unique number like Autonumber. The date and time can be unique but my aim is that every time i click on the print/preview button from the subform (query) to print the report, it should produce unique number EXACTLY like autonumber.

    Assume that you have 5 records and each of these records have unique numbers (we call them invoice numbers) but when I want to print the statement that has all those 5 invoies, it should create another unique number which we will call master invoice number.

    Thanks for your help.
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,092
    This is not a good suggestion really

    Create a table with a single field, populate this with the number zero.
    When the report loads, run an update query to add 1 to this fields value.
    Use this number as your uniqueID.
    George
    Home | Blog

  5. #5
    Join Date
    May 2006
    Posts
    375
    But how will this work in practice? I mean, what should I do in order for that new unique number in a new table to work with the report and everytime I try to create/print a report, it should populate a unique number?

    I would really appreciate your help and clear instructions!
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,092
    I've attached a dB that you may want to take a look at and customize to your requirements. Everytime the form is run, a new unique identifier is created and displayed on the form.

    If you have ANY questions about the code in the file - please ask!

    HTH

    GeorgeV
    Attached Files Attached Files
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,434
    so in effect its a statement no you want
    a suggestion why not create a table with a sattement number, detailing what invoices (and payments or whatever are included on it)

    PK, say StatementNo, is an autonumber column

    alterntively you could get cute and get invovled in some complex algolrhytm
    eg sum invoice numbers add a datestamp, convert the resultant number to hex or some other obscuring technique. you can encode years using A...Z (a=2007....) so AA may equal Jan 2007

    there are lots of options
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,092
    Looks like I beat you to it Healdem
    DateTime is the simplest solution to implement, it could even be formatted as an integer.
    E.g. 2007-04-10 11:14:22.877 could become 20070410111422877 easily!
    Which let's be honest, is better than any algorithm, really!

    However, my example is much like healdems suggestion: table with autonumber field (I added a date field too) where a new record is created for every report produced.
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,434
    Quote Originally Posted by georgev
    Looks like I beat you to it Healdem
    DateTime is the simplest solution to implement, it could even be formatted as an integer.
    E.g. 2007-04-10 11:14:22.877 could become 20070410111422877 easily!
    Which let's be honest, is better than any algorithm, really!

    However, my example is much like healdems suggestion: table with autonumber field (I added a date field too) where a new record is created for every report produced.
    I agree, except that there is always a risk that two people may attmept to generate a statement at exactly the same time..... small, possibly highly unlikely but a possibility none the less, hence why in my view you need to add something else to the stamp.

    some companies are nervous about letting suppliers or customers know how much or little business they do and require some form of masking of numbers, some companies want a simple and quick manual filing system so ABCxxxx can provide a quick filing mechanism where A is year, B is month, xxx is number, which ideally should be autogenerated. C is a fudge number to make the numbers look bigger

    eg AD5600001 could be the first document generated (which happend in April 2007). I wouldnt be keen on encouraging a customer to use an ascending number for each month, but as ever the customer (assuming they are paying their bills) knows best.

    Personally Id go with a statement no design. but then again I can see another issue where the customer will probably demand that there should be no gaps in the number sequence.. so that tends to stuff most autonumber systems. if thats required then you will have to implement some form of query to retrieve the next available number locking the table whilst retrieving and calculating the new number. try a search on this site for several examples (assuming the search works)
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,092
    True again!
    However, you could use my method, but write a one off script so your autonumber starts nice and high (recursive INSERT of 10,000 records)!

    As for the likelihood of the datetime being correct, slim-to-none is often not good enough. I used QA to generate my above example which uses accuracies to 1000th of a second - Access generally only does it to the second (I think, can't BA going to look that up )

    Alpha-Numeric indexing is often a very effective method also... but it still doesn't beat time!

    Healdem, take a look at my design: an autonumber + date field = unique everytime.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •