ACCESS FEED: Re: [MS_AccessPros] Creating Unique Collection Code

Rabu, 25 Maret 2015

Re: [MS_AccessPros] Creating Unique Collection Code

Khalid-

I assume that the ClientCIN is unique to each client, so any code that uses that as the prefix will, by definition, be unique to the client. By using code to generate the next "number" in sequence, you also ensure that the code is unique within the client.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 25, 2015, at 11:43 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Thanks for your reply.

One thing you did not mentioned, i needed for collection code is that it starts with the ClientCIN and for one consignment collection code for each Client should be unique.

Please look upon it.

Regards,

Khalid

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Does it matter what the alpha characters are?

You could add a field to the Clients table that lists the last alpha-number you used to generate the collection code. In BeforeUpdate of the form that creates a Collection Voucher, check to see if you're on a new record (Me.NewRecord = True), and if so, then fetch the last code from the client record, add 1, update the client record, and use the new code. When you get to 99 with the two digits, then change one of the letters and start over at 00 or 01.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 25, 2015, at 7:05 AM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi All,
I have been assigned a new task to generate and enter a unique collection code separately for each client, for each new consignment while entering data in form "Collection Voucher".

Logic for generating this code is that when this code is generated for clients new consignment, this code will be passed him and then he will pass this code to his authorized person at destination and that person will show or give this code to company's responsible person to deliver him the cargo.

The pattern for this code should be like starts with "ClientCIN" then two OR Three alphabets ending with two digits.

Is it necessary to generate this code in form "Collection Voucher" or some working is to be done in some other form before and then putting that code in form "Collection Voucher" ?

Help required please.
Khalid