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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
PING: Kaltuu - Dave
You were kind eniough to respond to a question I posted, one which many
others have askied in other ways in other places and not received answers. It boils down to generating a simple 4 digit number - for PO Order numbers, Invoice numbers and so on. My visit to the Church of Google searching using a variety of operands and variables esults in a lot of people asking similar questions getting know where - and it being available on ebay You seem to have the answer - but you gave me part of it, and you asked another question, which I answered. I think it would be interesting to find an answer. I copied the Post below so you don't have to hunt it down. I hope you can help. Trying to be moer clear, Auto-generating a number using autonumber isn't sufficient for most operations. many need to prefix it with a letter, or date. In my case, I need to have each record generate a number in the format mmyy-#### with, or without the dash - preferably with. So opening a new record for todays, today's would be 0408-0001 for that record, and the next record today would be 0408-0002 and so on. When it comes to next year, that's OK because the year will have changed (08 to 09) As I won't be around in 99, I don't care about what happens then Any asnwers that seem to deal with ithis don't explain how to apply the formula - and for one like me, just diving , up late reading and researching, waiting for a class to come along, extra help is needed to understand whther to edit a table, a form, or just how to make it work. Copy follows------------- Hi Dave, and thanks for ther reply Glad you asked - I expected that the new year would start the new number and the 0000 would roll over after 9999. The numbers for date ned to be 2 digits each. that is, Month ## and year ## (last 2 for year of course) followed by the 4 digit. Many thanks for your help. "Klatuu" wrote: The first part is easy Format(Date,"mmdd") How do you want to determine the rest? What will you do next year when the numbers start recurring? -- Dave Hargis, Microsoft Access MVP "Shell" wrote: I would like to generate a PO number using the following month/day/#### so it appears as, using todays date for example: 0508-0001 Is this possible for a Form? Thank you in advance. S ------------------------------ copy ends ------------------- S |
#2
|
|||
|
|||
PING: Kaltuu - Dave
Sorry, Shell, I didn't know we weren't done.
Okay there are a few issues surround this technique. Mostly, it has to do with what you are wanting and that is an incrementing identifier related to some other data item. One would initially think a field containing the entire value would be the correct answer. It is, however, not the best approach. The coding is much easier and the concept is within database normalisation rules when the two elements are carried separately. Now, keep in mind that what is carried in the database and what is presented to users is not always the same thing. We will use your situation as an example. What you want to present is a year, month, and a sequential number. Those, together, will comprise your PO number. The first thought would be to create a text field that would show it as 080403-0001, Or as a decimal data type and carry the year and month as the whole number and the sequential number as the decimal part and use formatting to present it to the user. Either of those choices would require parsing the value to retrieve it or to create it. You would then have to either repeat the logic every where it is used or create functions you could call throughout your application to manipulate it. The better method would be to use two fields. one for the grouping value (department, PO number, Document number, date, etc.) and another for the sequential number. This makes the coding much easier. In your case, a Purchase Order will always have PO date. I have been doing this for over 30 years and I have never seen a PO system where a PO doesn't have a date. So, we can use that for the grouping part and just add one field to the table using a Long Integer data type. So, to create a new PO, we use the PO date, find the highest existing number in the PO Table, and add 1 to it. That is the simplistic view, and shortly I will show the technique for that, but first we have to look at the practicality of a multi user system. It is remotely possible two users could be creating a new PO at the same time. So both would use the technique to create a new number and be creating different POs with the same number. The first to complete the PO and update the underlying table would be okay, but the second would get an error when trying to update the table. There are a number of ways to handle this, but I will keep it simple. First, here is how to create a new number. LngPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = " & Format(Date,"yymm"),0) + 1 This finds the highest PO number for the year and month and adds 1 to it. The Nz function is used in the case there is no PO number for the current month and date. It will convert the Null returned by the DMax function to 0. 0 + 1 = 1, so it will be the first number. Now, where to put it? As I said, I want to keep it simple. First, use the Form Current event and if it is a new record, create the number. If Me.NewRecord Then Me.txtPoDate = Date Me.txtPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = " & Format(Date,"yymm"),0) + 1 End If Now, in the example above, I would make the visible propery of txtPoNum False so the user doesn't see it and create another unbound control to display the number to the user. In it's control source property: =Format([txtPoDate],"yymm-") & Format([txtPoNum], "0000") Now, to ensure another user has not created a PO with the same number, user the form' Before Update event to check for the possibility and if it does, cancel the update and warn the user and add 1 to value saved in the hidden text box. If Not IsNull(DLookup("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = " & Format(Date,"yymm")) Then MsgBox "PO Number " & Me.txtShowPO & " Is in Use " & vbNewLine & "Creating New Number" Me.txtPoNum = Me.txtPoNum + 1 Cancel = True End If Then you just need to provide a way for the user to retry the update. Hope that answers your question -- Dave Hargis, Microsoft Access MVP "Shell" wrote: You were kind eniough to respond to a question I posted, one which many others have askied in other ways in other places and not received answers. It boils down to generating a simple 4 digit number - for PO Order numbers, Invoice numbers and so on. My visit to the Church of Google searching using a variety of operands and variables esults in a lot of people asking similar questions getting know where - and it being available on ebay You seem to have the answer - but you gave me part of it, and you asked another question, which I answered. I think it would be interesting to find an answer. I copied the Post below so you don't have to hunt it down. I hope you can help. Trying to be moer clear, Auto-generating a number using autonumber isn't sufficient for most operations. many need to prefix it with a letter, or date. In my case, I need to have each record generate a number in the format mmyy-#### with, or without the dash - preferably with. So opening a new record for todays, today's would be 0408-0001 for that record, and the next record today would be 0408-0002 and so on. When it comes to next year, that's OK because the year will have changed (08 to 09) As I won't be around in 99, I don't care about what happens then Any asnwers that seem to deal with ithis don't explain how to apply the formula - and for one like me, just diving , up late reading and researching, waiting for a class to come along, extra help is needed to understand whther to edit a table, a form, or just how to make it work. Copy follows------------- Hi Dave, and thanks for ther reply Glad you asked - I expected that the new year would start the new number and the 0000 would roll over after 9999. The numbers for date ned to be 2 digits each. that is, Month ## and year ## (last 2 for year of course) followed by the 4 digit. Many thanks for your help. "Klatuu" wrote: The first part is easy Format(Date,"mmdd") How do you want to determine the rest? What will you do next year when the numbers start recurring? -- Dave Hargis, Microsoft Access MVP "Shell" wrote: I would like to generate a PO number using the following month/day/#### so it appears as, using todays date for example: 0508-0001 Is this possible for a Form? Thank you in advance. S ------------------------------ copy ends ------------------- S |
#3
|
|||
|
|||
PING: Kaltuu - Dave
Many thans Dave - I have replied inline below.
"Klatuu" wrote: Sorry, Shell, I didn't know we weren't done. Okay there are a few issues surround this technique. Mostly, it has to do with what you are wanting and that is an incrementing identifier related to some other data item. One would initially think a field containing the entire value would be the correct answer. It is, however, not the best approach. The coding is much easier and the concept is within database normalisation rules when the two elements are carried separately. Now, keep in mind that what is carried in the database and what is presented to users is not always the same thing. We will use your situation as an example. What you want to present is a year, month, and a sequential number. Those, together, will comprise your PO number. The first thought would be to create a text field that would show it as 080403-0001, Or as a decimal data type and carry the year and month as the whole number and the sequential number as the decimal part and use formatting to present it to the user. Either of those choices would require parsing the value to retrieve it or to create it. You would then have to either repeat the logic every where it is used or create functions you could call throughout your application to manipulate it. The better method would be to use two fields. one for the grouping value (department, PO number, Document number, date, etc.) and another for the sequential number. This makes the coding much easier. In your case, a Purchase Order will always have PO date. I have been doing this for over 30 years and I have never seen a PO system where a PO doesn't have a date. So, we can use that for the grouping part and just add one field to the table using a Long Integer data type. So, to create a new PO, we use the PO date, find the highest existing number in the PO Table, and add 1 to it. That is the simplistic view, and shortly I will show the technique for that, but first we have to look at the practicality of a multi user system. It is remotely possible two users could be creating a new PO at the same time. So both would use the technique to create a new number and be creating different POs with the same number. The first to complete the PO and update the underlying table would be okay, but the second would get an error when trying to update the table. There are a number of ways to handle this, but I will keep it simple. First, here is how to create a new number. LngPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = " & Format(Date,"yymm"),0) + 1 This code above - I assume I need to create a Table and put it in there somewhere? As it stands, I have a Form that has, until yesterday, created the Table as I entered text Fileds. I have a text Field called POCalc (Control Source) and has used the Autonumber genrator in the Table Sedign mode. Eacjh record now has a number there from 1 to 144 (number of records so far). SIDEBAR: I'm thinking perhaps I blew $700.00 and a new computer for something that is waaaay over my head - but I'm starting to understand, and I have been reading the "Microsoft Access 2007 Bible" 1,242 pages to go Mean while... I'm trial and error, and reading the Forums. This finds the highest PO number for the year and month and adds 1 to it. The Nz function is used in the case there is no PO number for the current month and date. It will convert the Null returned by the DMax function to 0. 0 + 1 = 1, so it will be the first number. Now, where to put it? As I said, I want to keep it simple. First, use the Form Current event and if it is a new record, create the number. I've looked every where - no "Current event. Looked under Propertis, Ecnets, in Table Propertis and elsewhere - no "Current" event. If Me.NewRecord Then Me.txtPoDate = Date Me.txtPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = " & Format(Date,"yymm"),0) + 1 End If Now, in the example above, I would make the visible propery of txtPoNum False so the user doesn't see it and create another unbound control to display the number to the user. In it's control source property: =Format([txtPoDate],"yymm-") & Format([txtPoNum], "0000") Is it something I add to the "Current" event, of create a new Field and put it in there? Now, to ensure another user has not created a PO with the same number, user the form' Before Update event to check for the possibility and if it does, cancel the update and warn the user and add 1 to value saved in the hidden text box. If Not IsNull(DLookup("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = " & Format(Date,"yymm")) Then MsgBox "PO Number " & Me.txtShowPO & " Is in Use " & vbNewLine & "Creating New Number" Me.txtPoNum = Me.txtPoNum + 1 Cancel = True End If Then you just need to provide a way for the user to retry the update. I'm sorry. You took a lot of time and effort to explain this and it all went right over my head. I've printed it out and read through it and tried a few things to no avail. I don't have a clue whwere this stuf goes. But all is not lost on your part. Google will have this in His Church sooon and those seeking an answer there will find this,. and benefit from it. Mean while, I'll keep plugging the formulas abocve itno different places and see what happens. Thanks - your efforts are very appreciated. Hope that answers your question -- Dave Hargis, Microsoft Access MVP "Shell" wrote: You were kind eniough to respond to a question I posted, one which many others have askied in other ways in other places and not received answers. It boils down to generating a simple 4 digit number - for PO Order numbers, Invoice numbers and so on. My visit to the Church of Google searching using a variety of operands and variables esults in a lot of people asking similar questions getting know where - and it being available on ebay You seem to have the answer - but you gave me part of it, and you asked another question, which I answered. I think it would be interesting to find an answer. I copied the Post below so you don't have to hunt it down. I hope you can help. Trying to be moer clear, Auto-generating a number using autonumber isn't sufficient for most operations. many need to prefix it with a letter, or date. In my case, I need to have each record generate a number in the format mmyy-#### with, or without the dash - preferably with. So opening a new record for todays, today's would be 0408-0001 for that record, and the next record today would be 0408-0002 and so on. When it comes to next year, that's OK because the year will have changed (08 to 09) As I won't be around in 99, I don't care about what happens then Any asnwers that seem to deal with ithis don't explain how to apply the formula - and for one like me, just diving , up late reading and researching, waiting for a class to come along, extra help is needed to understand whther to edit a table, a form, or just how to make it work. Copy follows------------- Hi Dave, and thanks for ther reply Glad you asked - I expected that the new year would start the new number and the 0000 would roll over after 9999. The numbers for date ned to be 2 digits each. that is, Month ## and year ## (last 2 for year of course) followed by the 4 digit. Many thanks for your help. "Klatuu" wrote: The first part is easy Format(Date,"mmdd") How do you want to determine the rest? What will you do next year when the numbers start recurring? -- Dave Hargis, Microsoft Access MVP "Shell" wrote: I would like to generate a PO number using the following month/day/#### so it appears as, using todays date for example: 0508-0001 Is this possible for a Form? Thank you in advance. S ------------------------------ copy ends ------------------- S |
#4
|
|||
|
|||
PING: Kaltuu - Dave
LngPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = "
& Format(Date,"yymm"),0) + 1 This code above - I assume I need to create a Table and put it in there somewhere? As it stands, I have a Form that has, until yesterday, created the Table as I entered text Fileds. I have a text Field called POCalc (Control Source) and has used the Autonumber genrator in the Table Sedign mode. Eacjh record now has a number there from 1 to 144 (number of records so far). No, there is no need for an additional table. I assume you have a table for a PO. That table probably has a field that carries the date of the PO. The idea is to add a long integer field to that table. That field would be the field you would use to carry the sequence number. The DMax fucntion will return the highest number in the sequence field for the date. Adding 1 to it will give you the next PO sequence number for that date. If no records exist in the table for the date, the DMax will return Null. Adding the Nz function converts the Null to a 0. Adding 1, then creates the first PO sequence for the date. I've looked every where - no "Current event. Looked under Propertis, Ecnets, in Table Propertis and elsewhere - no "Current" event. The Current event is one of the Form events. Open the properties dialog for the form, select the Events tab. You will find the Current event there. Is it something I add to the "Current" event, of create a new Field and put it in there? Yes, the code above goes in the form's Current Event. Hope this will help. -- Dave Hargis, Microsoft Access MVP |
#5
|
|||
|
|||
PING: Kaltuu - Dave
It's not you - it's me. I just can't wrap my had around this one. I pasted
the formula you gave (LngPoNum...) into the Current Event via Form. BTW - I have just one Form, no tables except the one for Form. When I save, I get an error popup saying it can't find the Object LngPoNum... et cetera. I've been trying a lot of different ways with your suggestion - I created a tblPO, and more - to no avail. I'm checking with the local college to see if anyone there will take the Form and add your suggestions - I'm sure someone there will understand it. Keep in mind, I just opened the book on this and some of the terms are way over my head. Again, it's not you - and I really appreciate the extra steps you took to try and explain it to me - but it's all just bouncing off I'm sure many will benefit from your help. S "Klatuu" wrote: LngPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = " & Format(Date,"yymm"),0) + 1 This code above - I assume I need to create a Table and put it in there somewhere? As it stands, I have a Form that has, until yesterday, created the Table as I entered text Fileds. I have a text Field called POCalc (Control Source) and has used the Autonumber genrator in the Table Sedign mode. Eacjh record now has a number there from 1 to 144 (number of records so far). No, there is no need for an additional table. I assume you have a table for a PO. That table probably has a field that carries the date of the PO. The idea is to add a long integer field to that table. That field would be the field you would use to carry the sequence number. The DMax fucntion will return the highest number in the sequence field for the date. Adding 1 to it will give you the next PO sequence number for that date. If no records exist in the table for the date, the DMax will return Null. Adding the Nz function converts the Null to a 0. Adding 1, then creates the first PO sequence for the date. I've looked every where - no "Current event. Looked under Propertis, Ecnets, in Table Propertis and elsewhere - no "Current" event. The Current event is one of the Form events. Open the properties dialog for the form, select the Events tab. You will find the Current event there. Is it something I add to the "Current" event, of create a new Field and put it in there? Yes, the code above goes in the form's Current Event. Hope this will help. -- Dave Hargis, Microsoft Access MVP |
#6
|
|||
|
|||
PING: Kaltuu - Dave
lngPONum is a variable.
You should dim the variable in the event at the top of the event code: Dim lngPoNum As Long. tblPO is just an example. You would need to use the name of your table and use the names of your fields. Don't give up. It will start making sense after a while. -- Dave Hargis, Microsoft Access MVP "Shell" wrote: It's not you - it's me. I just can't wrap my had around this one. I pasted the formula you gave (LngPoNum...) into the Current Event via Form. BTW - I have just one Form, no tables except the one for Form. When I save, I get an error popup saying it can't find the Object LngPoNum... et cetera. I've been trying a lot of different ways with your suggestion - I created a tblPO, and more - to no avail. I'm checking with the local college to see if anyone there will take the Form and add your suggestions - I'm sure someone there will understand it. Keep in mind, I just opened the book on this and some of the terms are way over my head. Again, it's not you - and I really appreciate the extra steps you took to try and explain it to me - but it's all just bouncing off I'm sure many will benefit from your help. S "Klatuu" wrote: LngPoNum = Nz(DMax("[PO_Num]", "tblPO", "Format([PO_Date], ""yymm"") = " & Format(Date,"yymm"),0) + 1 This code above - I assume I need to create a Table and put it in there somewhere? As it stands, I have a Form that has, until yesterday, created the Table as I entered text Fileds. I have a text Field called POCalc (Control Source) and has used the Autonumber genrator in the Table Sedign mode. Eacjh record now has a number there from 1 to 144 (number of records so far). No, there is no need for an additional table. I assume you have a table for a PO. That table probably has a field that carries the date of the PO. The idea is to add a long integer field to that table. That field would be the field you would use to carry the sequence number. The DMax fucntion will return the highest number in the sequence field for the date. Adding 1 to it will give you the next PO sequence number for that date. If no records exist in the table for the date, the DMax will return Null. Adding the Nz function converts the Null to a 0. Adding 1, then creates the first PO sequence for the date. I've looked every where - no "Current event. Looked under Propertis, Ecnets, in Table Propertis and elsewhere - no "Current" event. The Current event is one of the Form events. Open the properties dialog for the form, select the Events tab. You will find the Current event there. Is it something I add to the "Current" event, of create a new Field and put it in there? Yes, the code above goes in the form's Current Event. Hope this will help. -- Dave Hargis, Microsoft Access MVP |
#7
|
|||
|
|||
PING: Kaltuu - Dave
Shell, I definitely agree with Dave -- there's a lot of territory to
cover in Access, and there is a very definite learning curve. I like to use "critical mass" as an analogy -- there is a point where concepts begin to come together; the pathway leading to that point can certainly be arduous! snip tblPO is just an example. *You would need to use the name of your table and use the names of your fields. Don't give up. *It will start making sense after a while. -- Dave Hargis, Microsoft Access MVP -- Clif, erstwhile programmer, newcomer to Access and VBA. |
#8
|
|||
|
|||
PING: Kaltuu - Dave
Thanks Clif.
I've spent too much time already trying to figure out thow to Dim a LngPoNum and in what field, macro, or Expression builder, on what Table or Form - plus looking up How-To-Dim is a whole new world on VBA writing (I think) I'm trying to get the books suggested, found one and it's only 1300 plus pages (Access 2007 Bible), Step x Step Access 2007 and Access In and Out. I've enough reading now to last me into my next life! I am still trying to digest where exactly it is Klatuu (Dave) is suggesting those forumlas (statements?) get placed, and how. Still copying, pasting, testing and putting the Access error codes to the test Thanks S "Clif McIrvin" wrote: Shell, I definitely agree with Dave -- there's a lot of territory to cover in Access, and there is a very definite learning curve. I like to use "critical mass" as an analogy -- there is a point where concepts begin to come together; the pathway leading to that point can certainly be arduous! snip tblPO is just an example. You would need to use the name of your table and use the names of your fields. Don't give up. It will start making sense after a while. -- Dave Hargis, Microsoft Access MVP -- Clif, erstwhile programmer, newcomer to Access and VBA. |
Thread Tools | |
Display Modes | |
|
|