If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Is it possible to create unique numbers every time you create a report using print/pr

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,030
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,030
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
File Type: zip UniqueIDGenerator.zip (16.1 KB, 114 views)
__________________
George
Home | Blog
Reply With Quote
  #7 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,217
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #8 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,030
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
Reply With Quote
  #9 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,217
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #10 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,030
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On