Kamis, 26 Maret 2015

Re: [MS_AccessPros] Creating Unique Collection Code

 

Dave-


Yes that might be advisable, but the odds of getting a duplicate are pretty small.  There are 26 x 26 x 100 combinations possible - 67,600.

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 
(Paris, France)




On Mar 26, 2015, at 12:12 PM, 'Dave W' davewi11@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


Surely you would also need to check that the result is different from all the already used 67,600 possibilities and try again if so?
 
Dave W
 
----- Original Message -----
Sent: Thursday, March 26, 2015 10:39 AM
Subject: Re: [MS_AccessPros] Creating Unique Collection Code

 
Khalid-

If the Collection Voucher record is unique for each CollectionCode that you want to create, then it belongs in that table.  If multiple clients can be related to a Collection Voucher record, then perhaps the code belongs in Consignment Number.

To generate a truly random value, you could use code like this:

Dim strCode As String
' Define array to generate a random letter
Dim strAlpha = New String {"A", "B", "C", "D", "E", "F", "G", "H", _
    "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
Dim intI As Integer

    ' Set the randomizer seed
    Randomize
    ' Generate the first letter
    intI = CInt(Int((25 * Rnd()) + 1)
    strCode = strAlpha(intI)
    ' Generate the second letter
    intI = CInt(Int((25 * Rnd()) + 1)
    strCode = strCode & strAlpha(intI)
    ' Generate a two-digit number between 0 and 99
    intI = Int(100 * Rnd())
    strCode = strCode & Format(intI, "00")

At the end of the code, the variable strCode will contain a random value of two letters and two numbers.

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 
(Paris, France)




On Mar 26, 2015, at 5:15 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Good morning!
CollectionCode should itself be a new field. Not yet created, and in which table it should be ? perhaps table CollectionVoucher.

Moreover, the logic you were describing me describes that CollectionCode would be incremented by 1. This logic will be easily got by the clients. We need unique & random code. For example in consignmentNo 2015-A-03, we assign AA01 code to ClientCIN 3, then to CleintCIN 5 might have code NC39 and so on for other clients for this consignment.

Regards,
Khalid
 


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

Khalid-

Which field needs to have this new number?

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 
(Paris, France)




On Mar 25, 2015, at 1:13 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Yes! you are correct.
ClientCIN is unique for each client.

What i'm  not getting is that for a new consignment, when i am entering cargo/cartons received from a specific client, that unique collection code should be assigned once while his cartons are 1 to 10 or more than 100.

Could you please give an example. Following is the sql for "CollectionVouchersubform"

SELECT CollectionVoucher.ConsignmentNo, [Consignment Number].ExportDocs, CollectionVoucher.Route, CollectionVoucher.ClientCIN, Clients.ClientName, CollectionVoucher.Destination, CollectionVoucher.DeliveryVr, CollectionVoucher.DeliveryVrDate, CollectionVoucher.CartonNo, CollectionVoucher.CartonSuffix, CollectionVoucher.TripNo, CollectionVoucher.UnitOfCarton, CollectionVoucher.ProductNameEnglish, Products.ProductNameRussian, Products.HSCode, CollectionVoucher.BrandName, CollectionVoucher.ProductQty, CollectionVoucher.UnitOfQty, CollectionVoucher.WeightOfCarton, CollectionVoucher.UnitOfGrossWeight, CollectionVoucher.[Net Weight], CollectionVoucher.UnitOfNetWeight, CollectionVoucher.UnitOfValue, CollectionVoucher.Rate, CollectionVoucher.Amount
FROM Products INNER JOIN ([Consignment Number] INNER JOIN (Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN) ON [Consignment Number].ConsignmentNo = CollectionVoucher.ConsignmentNo) ON Products.ProductNameEnglish = CollectionVoucher.ProductNameEnglish;

Khalid
 


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

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 
(Paris, France)




On Mar 25, 2015, at 11:43 AM, khalidtanweerburrah@... [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 
(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
 






__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (10)

.

__,_._,___

Tidak ada komentar:

Posting Komentar