John,
That is marvelous!
I simply changed the design of form and put the code on cmbClientCIN after update event.
Also amended last line as i remembered my boss told that code should start with ClientCIN+Two alpha+Two digits.
Me.CollectionCode = [ClientCIN] & [strCode]
Thank you very much John, i gained the knowledge of generating unique code.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
That compiles and will work, but it won't do anything with CollectionCode. Needs one more line:
Private Sub ConsignmentNo_AfterUpdate()
Dim strCode As String
' Define string to generate a random letter
Dim strAlpha As String
Dim intI As Integer
strAlpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
' Set the randomizer seed
Randomize
' Generate the first letter
intI = CInt(Int((26 * Rnd())) + 1)
strCode = Mid(strAlpha, intI, 1)
' Generate the second letter
intI = CInt(Int((25 * Rnd())) + 1)
strCode = strCode & Mid(strAlpha, intI, 1)
' Generate a two-digit number between 0 and 99
intI = Int(100 * Rnd())
strCode = strCode & Format(intI, "00")
Me.CollectionCode = strCode
End Sub
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 28, 2015, at 8:55 PM, John Viescas JohnV@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:Khalid-Try this:Private Sub ConsignmentNo_AfterUpdate()Dim strCode As String' Define string to generate a random letterDim strAlpha As StringDim intI As IntegerstrAlpha = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"' Set the randomizer seedRandomize' Generate the first letterintI = CInt(Int((26 * Rnd())) + 1)strCode = Mid(strAlpha, intI, 1)' Generate the second letterintI = CInt(Int((25 * Rnd())) + 1)strCode = strCode & Mid(strAlpha, intI, 1)' Generate a two-digit number between 0 and 99intI = Int(100 * Rnd())strCode = strCode & Format(intI, "00")End SubJohn Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)On Mar 28, 2015, at 7:58 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John,After running the new form "Assign clients to Consignment" and running the code you gave on the after update event of ConsignmentNo following error occurs:Private Sub ConsignmentNo_AfterUpdate()Dim strCode As String' Define array to generate a random letterDim 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 seedRandomize' Generate the first letterintI = CInt(Int((25 * Rnd()) + 1)strCode = strAlpha(intI)' Generate the second letterintI = CInt(Int((25 * Rnd()) + 1)strCode = strCode & strAlpha(intI)' Generate a two-digit number between 0 and 99intI = Int(100 * Rnd())strCode = strCode & Format(intI, "00")End SubHighlighted lines are showing Red in code.In Dim strAlpha should "I" have a comma before ? like ,"I"Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :Khalid-You need a new "Assign clients to Consignments form - and when the user picks the Consignment Number, run the code I gave you and generate a CollectionCode.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)On Mar 28, 2015, at 5:42 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John,OK. made the new table ClientConsignments now what further?Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :Khalid-Found it. You can't use Collection Voucher because there are multiple rows for each Client and Consignment. You need a new table:ClientConsignments:ClientCINConsignmentNoCollectionCodeYour relationships would then be:Clients -> ClientConsignments -> CollectionVoucher^|Consignment Number—+John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)On Mar 28, 2015, at 12:17 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John,A month or so before i uploaded PTCL_be in the "Assistance Needed" folder of the group. If it is still there then you can view the relationships and design of table "CollectionVoucher". However Primery Key of table is: (as advised by you)ConsignmentNoClientCINCartonNoCartonSuffixfield ProductNameEnglish has Row Source:SELECT Products.ProductNameEnglish, Products.ProductNameRussian, Products.HSCodeFROM ProductsORDER BY Products.ProductNameEnglish;I think for our issue other fields are not necessary to mention here.Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :Khalid-Your query implies these relationships:Clients -> CollectionVoucher -> Consignment Number^|Products —-+What fields are in the CollectionVoucher table? That table seems to provide the many clients to one Consignment relationship, so that appears to be the place where the Collection Code belongs. However, if there can be multiple CollectionVoucher records per consignment, you have a problem because I assume the same Collection Code should be in all the records that relate to a particular Client and Consignment.John Viescas, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)On Mar 28, 2015, at 7:50 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:John,First of all sorry that i was away from office and could not reply earlier.Each consignment has multiple clients delivering their cargo of different products. Like Consignment No. 2015-A-03ClientCIN Total Cartons3 1356 522 26126 3430 3875 1676 1877 17So for this Consignment No.2015-A-03 each client should have his own single Collection Code. Then on wards for next consignment each client should have his own single unique Collection Code.I hope now i have made clear what i need.So please guide in this situation how to manage?Regards,Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :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 letterDim 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 seedRandomize' Generate the first letterintI = CInt(Int((25 * Rnd()) + 1)strCode = strAlpha(intI)' Generate the second letterintI = CInt(Int((25 * Rnd()) + 1)strCode = strCode & strAlpha(intI)' Generate a two-digit number between 0 and 99intI = 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, AuthorMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access ApplicationsSQL Queries for Mere Mortals(Paris, France)On Mar 26, 2015, at 5:15 AM, khalidtanweerburrah@... [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, AuthorMicrosoft Access 2010 Inside Out
__._,_.___
Posted by: khalidtanweerburrah@yahoo.com
Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (20) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar