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
|
|||
|
|||
Automatic numbering
Not sure where to post this. I have a database for making occasional
reports. The first report of this year is numbered RPT-05-01, the second RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such as 05-01) and RPT_Sequence (the last two digits of RPT_Number). A form (frmRPT) is based on the table. I have placed the following code into the form's On Current event: If IsNull(Me.RPT_Sequence) Then Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1 End If If IsNull(Me.RPT_Number) Then Me.RPT_Number = Format(Date, "yy") & "-" & _ Format(Me.RPT_Sequence, "00") Else: Me.txtRPT_Number.Locked = True End If txtRPT_Number is the text box bound to RPT_Number. People can't change the number after the record is created. The above code assigns 05-01, 05-02, and so forth in the proper sequence, and the number is retained when returning to the record, but of course it will not restart at 06-01 next year. How can I assign the correct number at the start of next year? In general is there a better way of assigning the number, or are there any potential problems with the approach beyond what I have mentioned? |
#2
|
|||
|
|||
Dim strWhere As String
Dim varResult As Variant strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*""" varResult = DMax("RPT_Sequence", "tblRPT", strWhere) If IsNull(varResult) Then Me.RPT_Sequence = Format(Date, "yy") & "-01" Else Me.RPT_Sequence = Left(varResult, 3) & _ Format(Val(Right(varResult,2))+1, "00") End If -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BruceM" wrote in message ... Not sure where to post this. I have a database for making occasional reports. The first report of this year is numbered RPT-05-01, the second RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such as 05-01) and RPT_Sequence (the last two digits of RPT_Number). A form (frmRPT) is based on the table. I have placed the following code into the form's On Current event: If IsNull(Me.RPT_Sequence) Then Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1 End If If IsNull(Me.RPT_Number) Then Me.RPT_Number = Format(Date, "yy") & "-" & _ Format(Me.RPT_Sequence, "00") Else: Me.txtRPT_Number.Locked = True End If txtRPT_Number is the text box bound to RPT_Number. People can't change the number after the record is created. The above code assigns 05-01, 05-02, and so forth in the proper sequence, and the number is retained when returning to the record, but of course it will not restart at 06-01 next year. How can I assign the correct number at the start of next year? In general is there a better way of assigning the number, or are there any potential problems with the approach beyond what I have mentioned? |
#3
|
|||
|
|||
=?Utf-8?B?QnJ1Y2VN?= wrote in
: How can I assign the correct number at the start of next year? In general is there a better way of assigning the number, or are there any potential problems with the approach beyond what I have mentioned? Yes to both. My table (tblReport) contains fields for RPT_Number (such as 05-01) and RPT_Sequence (the last two digits of RPT_Number). The first problem with this is that it's only a matter of time until you have a RTP_Number that says 05_23 and a RPT_Sequence that says 32 -- oh, and a short time after that the report date will say 2004-xx-xx and the Number will say 06-yy; and so on. Take some time to sort out second normal form: this means that a single fact is recorded exactly one time. If the "05" refers to the date of the report, and assuming you have that date stored, then it's child's play to get it out in a query: ShortYearNumber: Year(CreateDate) Mod 100 This leaves the Sequence number in the second field as a plain integer. You can display the entire report number like this: ReportNumber: "RPT_" & Format(Year(CreateDate) Mod 100, "00") & "_" & Format(Sequence, "00") Allocating a new sequence number is pretty much as you have done, except for using a criterion on the DMax() function: ' calculate the current year from the record varTemp = DMax("Sequence","MyTable", _ "Year(CreateDate)=" & Year(Me!CreateDate)) If IsNull(varTemp) Then NextSequence = 1 Else NextSequence = varTemp + 1 End If Hope that helps Tim F |
#4
|
|||
|
|||
I appreciate the quick response. I had to do some tweaking, but the end
result is that it seems to work. When I changed my computer clock to next year I was suddenly late for just about everything (according to Outlook Calendar), but the numbers fell in line starting with 06-01. In the line strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*""" there seemed to be an extra quote after Like, so I removed one. RPT_Number is the concatenated value that is the official document number (05-01, 05-02, etc.). RPT_Sequence is the incrementing value that forms the last two digits of RPT_Number. RPT_Sequence is a number field. I changed RPT_Sequence to RPT_Number everywhere in your code. Apparently I do not need the RPT_Sequence field, which just incremented 01, 02, 03, etc. I have tried to find out more about Like (in the strWhere = ... line), but Help isn't very helpful, and I cannot find a way to frame the search in Google groups (because Like is a very common word, I suppose). Not to impose on your generously offered assistance, but could you either say a little more about the strWhere = line or point me to a resource that can provide some clarity. Thanks again. "Allen Browne" wrote: Dim strWhere As String Dim varResult As Variant strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*""" varResult = DMax("RPT_Sequence", "tblRPT", strWhere) If IsNull(varResult) Then Me.RPT_Sequence = Format(Date, "yy") & "-01" Else Me.RPT_Sequence = Left(varResult, 3) & _ Format(Val(Right(varResult,2))+1, "00") End If -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BruceM" wrote in message ... Not sure where to post this. I have a database for making occasional reports. The first report of this year is numbered RPT-05-01, the second RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such as 05-01) and RPT_Sequence (the last two digits of RPT_Number). A form (frmRPT) is based on the table. I have placed the following code into the form's On Current event: If IsNull(Me.RPT_Sequence) Then Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1 End If If IsNull(Me.RPT_Number) Then Me.RPT_Number = Format(Date, "yy") & "-" & _ Format(Me.RPT_Sequence, "00") Else: Me.txtRPT_Number.Locked = True End If txtRPT_Number is the text box bound to RPT_Number. People can't change the number after the record is created. The above code assigns 05-01, 05-02, and so forth in the proper sequence, and the number is retained when returning to the record, but of course it will not restart at 06-01 next year. How can I assign the correct number at the start of next year? In general is there a better way of assigning the number, or are there any potential problems with the approach beyond what I have mentioned? |
#5
|
|||
|
|||
I had imagined originally that when a new year started RPT_Sequence would
restart from 01. When asking a question I usually try to show I have been working on the problem, even if I suspect I am not on track. I have implemented another suggestion that seems to work (from Allen Browne), but I am interested in your approach too. In the future I need to design a similar database except with the department code in the number (D-05-01, D-05-02, S-05-01, D-05-03, Q-05-01) so maybe your suggestion will come into play yet, except that I am having trouble understanding it. As I understand your suggestion, CreateDate is a field containing the date the report was created. That would have an advantage over Format(Date(),"yy") in that a handwritten report created on the last day of the year but entered into the system in the new year would contain the correct report date. How does MOD 100 fit in? As I understand it MOD is a sort of whole number division sign. On another matter, I usually use autonumber PKs, but in this case I wonder if there is any advantage (or disadvantage) to using the concatenated number (RPT_Number) as the PK. I have no plans just now for relationships with other tables, but am I correct in thinking that the FK (if there is one) would have to be of data type Text? If so, any potential problems with that? "Tim Ferguson" wrote: =?Utf-8?B?QnJ1Y2VN?= wrote in : How can I assign the correct number at the start of next year? In general is there a better way of assigning the number, or are there any potential problems with the approach beyond what I have mentioned? Yes to both. My table (tblReport) contains fields for RPT_Number (such as 05-01) and RPT_Sequence (the last two digits of RPT_Number). The first problem with this is that it's only a matter of time until you have a RTP_Number that says 05_23 and a RPT_Sequence that says 32 -- oh, and a short time after that the report date will say 2004-xx-xx and the Number will say 06-yy; and so on. Take some time to sort out second normal form: this means that a single fact is recorded exactly one time. If the "05" refers to the date of the report, and assuming you have that date stored, then it's child's play to get it out in a query: ShortYearNumber: Year(CreateDate) Mod 100 This leaves the Sequence number in the second field as a plain integer. You can display the entire report number like this: ReportNumber: "RPT_" & Format(Year(CreateDate) Mod 100, "00") & "_" & Format(Sequence, "00") Allocating a new sequence number is pretty much as you have done, except for using a criterion on the DMax() function: ' calculate the current year from the record varTemp = DMax("Sequence","MyTable", _ "Year(CreateDate)=" & Year(Me!CreateDate)) If IsNull(varTemp) Then NextSequence = 1 Else NextSequence = varTemp + 1 End If Hope that helps Tim F |
#6
|
|||
|
|||
I have to take back what I waid earlier. It works up to a point, but
RPT_Number keeps changing for the same record. The problem is with the On Current event, which reassigns the number every time the record is current. Maybe I will need to exit the sub if there is already a value in that field. "Allen Browne" wrote: Dim strWhere As String Dim varResult As Variant strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*""" varResult = DMax("RPT_Sequence", "tblRPT", strWhere) If IsNull(varResult) Then Me.RPT_Sequence = Format(Date, "yy") & "-01" Else Me.RPT_Sequence = Left(varResult, 3) & _ Format(Val(Right(varResult,2))+1, "00") End If -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BruceM" wrote in message ... Not sure where to post this. I have a database for making occasional reports. The first report of this year is numbered RPT-05-01, the second RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such as 05-01) and RPT_Sequence (the last two digits of RPT_Number). A form (frmRPT) is based on the table. I have placed the following code into the form's On Current event: If IsNull(Me.RPT_Sequence) Then Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1 End If If IsNull(Me.RPT_Number) Then Me.RPT_Number = Format(Date, "yy") & "-" & _ Format(Me.RPT_Sequence, "00") Else: Me.txtRPT_Number.Locked = True End If txtRPT_Number is the text box bound to RPT_Number. People can't change the number after the record is created. The above code assigns 05-01, 05-02, and so forth in the proper sequence, and the number is retained when returning to the record, but of course it will not restart at 06-01 next year. How can I assign the correct number at the start of next year? In general is there a better way of assigning the number, or are there any potential problems with the approach beyond what I have mentioned? |
#7
|
|||
|
|||
No: don't use Form_Current. You don't need to assign the number once it
exists. Use Form_BeforeUpdate. This is the last possible moment you can assign the value before the record is saved (which reduces the change of duplicates being given to different users attempting to enter new records at the same time.) You only need to do this is if it a new record, so: Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then 'put the code in here. End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BruceM" wrote in message ... I have to take back what I waid earlier. It works up to a point, but RPT_Number keeps changing for the same record. The problem is with the On Current event, which reassigns the number every time the record is current. Maybe I will need to exit the sub if there is already a value in that field. "Allen Browne" wrote: Dim strWhere As String Dim varResult As Variant strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*""" varResult = DMax("RPT_Sequence", "tblRPT", strWhere) If IsNull(varResult) Then Me.RPT_Sequence = Format(Date, "yy") & "-01" Else Me.RPT_Sequence = Left(varResult, 3) & _ Format(Val(Right(varResult,2))+1, "00") End If "BruceM" wrote in message ... Not sure where to post this. I have a database for making occasional reports. The first report of this year is numbered RPT-05-01, the second RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such as 05-01) and RPT_Sequence (the last two digits of RPT_Number). A form (frmRPT) is based on the table. I have placed the following code into the form's On Current event: If IsNull(Me.RPT_Sequence) Then Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1 End If If IsNull(Me.RPT_Number) Then Me.RPT_Number = Format(Date, "yy") & "-" & _ Format(Me.RPT_Sequence, "00") Else: Me.txtRPT_Number.Locked = True End If txtRPT_Number is the text box bound to RPT_Number. People can't change the number after the record is created. The above code assigns 05-01, 05-02, and so forth in the proper sequence, and the number is retained when returning to the record, but of course it will not restart at 06-01 next year. How can I assign the correct number at the start of next year? In general is there a better way of assigning the number, or are there any potential problems with the approach beyond what I have mentioned? |
#8
|
|||
|
|||
Thanks again. One concern, though, is that this report, while important in
the big picture, is generally issued no more than 30 times per year. This means that some users will only use it every few months, if that. I can explain that the number is not assigned until they are done, but they will forget, and then they will call me and say that they can't enter a report number, and I will tell them it is assigned when they are done, and they will mutter and sigh and fill out a report suggesting that they be able to see the number right away. In fairness, it is not their job to remember administrative details like that. What I have done is to place the code into the On Current event, which produces a RPT_Number value, then I have placed the same code into Before Update, except that the first line there (after If Me.NewRecord) is to clear the text box containing RPT_Number (I allowed zero length strings in table design to make that possible). I have tested this with two users, and it seems to work. I could probably find a way to generate a message advising the user that the record number will be changed. It is not likely to occur often. Similarly, in the very unlikely event that two users attempt to save a record at the same time, I could probably set the RPT_Number field to not allow duplicates, and in case of the specific error of trying to assign a duplicate where it is not allowed I could have the code to assign the number run again. I would have to figure out which error number that is. Am I on the right track, or am I taking the long way around or otherwise causing myself difficulties? "Allen Browne" wrote: No: don't use Form_Current. You don't need to assign the number once it exists. Use Form_BeforeUpdate. This is the last possible moment you can assign the value before the record is saved (which reduces the change of duplicates being given to different users attempting to enter new records at the same time.) You only need to do this is if it a new record, so: Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then 'put the code in here. End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BruceM" wrote in message ... I have to take back what I waid earlier. It works up to a point, but RPT_Number keeps changing for the same record. The problem is with the On Current event, which reassigns the number every time the record is current. Maybe I will need to exit the sub if there is already a value in that field. "Allen Browne" wrote: Dim strWhere As String Dim varResult As Variant strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*""" varResult = DMax("RPT_Sequence", "tblRPT", strWhere) If IsNull(varResult) Then Me.RPT_Sequence = Format(Date, "yy") & "-01" Else Me.RPT_Sequence = Left(varResult, 3) & _ Format(Val(Right(varResult,2))+1, "00") End If "BruceM" wrote in message ... Not sure where to post this. I have a database for making occasional reports. The first report of this year is numbered RPT-05-01, the second RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such as 05-01) and RPT_Sequence (the last two digits of RPT_Number). A form (frmRPT) is based on the table. I have placed the following code into the form's On Current event: If IsNull(Me.RPT_Sequence) Then Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1 End If If IsNull(Me.RPT_Number) Then Me.RPT_Number = Format(Date, "yy") & "-" & _ Format(Me.RPT_Sequence, "00") Else: Me.txtRPT_Number.Locked = True End If txtRPT_Number is the text box bound to RPT_Number. People can't change the number after the record is created. The above code assigns 05-01, 05-02, and so forth in the proper sequence, and the number is retained when returning to the record, but of course it will not restart at 06-01 next year. How can I assign the correct number at the start of next year? In general is there a better way of assigning the number, or are there any potential problems with the approach beyond what I have mentioned? |
#9
|
|||
|
|||
If it important that this field is entered and unique, then you really do
need to set these properties for the field in table design: Required Yes Allow Zero Length No Indexed Yes (No Duplicates) Now that the data is known to be unique and valid, you can do whatever pleases you in the interface. If you don't like the idea of leaving the value to the last minute, then use the form's BeforeInsert event so it gets assigned as soon as the user *starts* to add a new record, instead of at the end of the process. This does increase the chance of duplicates of course. If the field is NOT required, you can clear the text box by setting its value to Null, i.e.: Me.RPT_Number = Null which saves you the bother of having test test of both null and a zero-length-string everywhere in the database where you use this field. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BruceM" wrote in message ... Thanks again. One concern, though, is that this report, while important in the big picture, is generally issued no more than 30 times per year. This means that some users will only use it every few months, if that. I can explain that the number is not assigned until they are done, but they will forget, and then they will call me and say that they can't enter a report number, and I will tell them it is assigned when they are done, and they will mutter and sigh and fill out a report suggesting that they be able to see the number right away. In fairness, it is not their job to remember administrative details like that. What I have done is to place the code into the On Current event, which produces a RPT_Number value, then I have placed the same code into Before Update, except that the first line there (after If Me.NewRecord) is to clear the text box containing RPT_Number (I allowed zero length strings in table design to make that possible). I have tested this with two users, and it seems to work. I could probably find a way to generate a message advising the user that the record number will be changed. It is not likely to occur often. Similarly, in the very unlikely event that two users attempt to save a record at the same time, I could probably set the RPT_Number field to not allow duplicates, and in case of the specific error of trying to assign a duplicate where it is not allowed I could have the code to assign the number run again. I would have to figure out which error number that is. Am I on the right track, or am I taking the long way around or otherwise causing myself difficulties? "Allen Browne" wrote: No: don't use Form_Current. You don't need to assign the number once it exists. Use Form_BeforeUpdate. This is the last possible moment you can assign the value before the record is saved (which reduces the change of duplicates being given to different users attempting to enter new records at the same time.) You only need to do this is if it a new record, so: Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then 'put the code in here. End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BruceM" wrote in message ... I have to take back what I waid earlier. It works up to a point, but RPT_Number keeps changing for the same record. The problem is with the On Current event, which reassigns the number every time the record is current. Maybe I will need to exit the sub if there is already a value in that field. "Allen Browne" wrote: Dim strWhere As String Dim varResult As Variant strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*""" varResult = DMax("RPT_Sequence", "tblRPT", strWhere) If IsNull(varResult) Then Me.RPT_Sequence = Format(Date, "yy") & "-01" Else Me.RPT_Sequence = Left(varResult, 3) & _ Format(Val(Right(varResult,2))+1, "00") End If "BruceM" wrote in message ... Not sure where to post this. I have a database for making occasional reports. The first report of this year is numbered RPT-05-01, the second RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such as 05-01) and RPT_Sequence (the last two digits of RPT_Number). A form (frmRPT) is based on the table. I have placed the following code into the form's On Current event: If IsNull(Me.RPT_Sequence) Then Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1 End If If IsNull(Me.RPT_Number) Then Me.RPT_Number = Format(Date, "yy") & "-" & _ Format(Me.RPT_Sequence, "00") Else: Me.txtRPT_Number.Locked = True End If txtRPT_Number is the text box bound to RPT_Number. People can't change the number after the record is created. The above code assigns 05-01, 05-02, and so forth in the proper sequence, and the number is retained when returning to the record, but of course it will not restart at 06-01 next year. How can I assign the correct number at the start of next year? In general is there a better way of assigning the number, or are there any potential problems with the approach beyond what I have mentioned? |
#10
|
|||
|
|||
Thanks again. I couldn't care less whether the number appears until the end,
but if it is blank I will get calls from users who can't enter a number. The text box is locked, by the way. Whether or not the field is required may not be relevant since the value is assigned programatically (it will contain a value in any case), but I appreciate your comments on Null values and other field properties, and will certainly apply those suggestions. I plan to use an autonumber PK, although at this point I do not anticipate that the table will be part of any relationships. It is essentially a flat database for recording twenty-five or so items that appear on a report. I can change the PK later if need be. Thanks again for the prompt and concise replies to my varied questions. "Allen Browne" wrote: If it important that this field is entered and unique, then you really do need to set these properties for the field in table design: Required Yes Allow Zero Length No Indexed Yes (No Duplicates) Now that the data is known to be unique and valid, you can do whatever pleases you in the interface. If you don't like the idea of leaving the value to the last minute, then use the form's BeforeInsert event so it gets assigned as soon as the user *starts* to add a new record, instead of at the end of the process. This does increase the chance of duplicates of course. If the field is NOT required, you can clear the text box by setting its value to Null, i.e.: Me.RPT_Number = Null which saves you the bother of having test test of both null and a zero-length-string everywhere in the database where you use this field. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BruceM" wrote in message ... Thanks again. One concern, though, is that this report, while important in the big picture, is generally issued no more than 30 times per year. This means that some users will only use it every few months, if that. I can explain that the number is not assigned until they are done, but they will forget, and then they will call me and say that they can't enter a report number, and I will tell them it is assigned when they are done, and they will mutter and sigh and fill out a report suggesting that they be able to see the number right away. In fairness, it is not their job to remember administrative details like that. What I have done is to place the code into the On Current event, which produces a RPT_Number value, then I have placed the same code into Before Update, except that the first line there (after If Me.NewRecord) is to clear the text box containing RPT_Number (I allowed zero length strings in table design to make that possible). I have tested this with two users, and it seems to work. I could probably find a way to generate a message advising the user that the record number will be changed. It is not likely to occur often. Similarly, in the very unlikely event that two users attempt to save a record at the same time, I could probably set the RPT_Number field to not allow duplicates, and in case of the specific error of trying to assign a duplicate where it is not allowed I could have the code to assign the number run again. I would have to figure out which error number that is. Am I on the right track, or am I taking the long way around or otherwise causing myself difficulties? "Allen Browne" wrote: No: don't use Form_Current. You don't need to assign the number once it exists. Use Form_BeforeUpdate. This is the last possible moment you can assign the value before the record is saved (which reduces the change of duplicates being given to different users attempting to enter new records at the same time.) You only need to do this is if it a new record, so: Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then 'put the code in here. End If End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "BruceM" wrote in message ... I have to take back what I waid earlier. It works up to a point, but RPT_Number keeps changing for the same record. The problem is with the On Current event, which reassigns the number every time the record is current. Maybe I will need to exit the sub if there is already a value in that field. "Allen Browne" wrote: Dim strWhere As String Dim varResult As Variant strWhere = "RPT_Sequence Like """" & Format(Date, "yy") & "*""" varResult = DMax("RPT_Sequence", "tblRPT", strWhere) If IsNull(varResult) Then Me.RPT_Sequence = Format(Date, "yy") & "-01" Else Me.RPT_Sequence = Left(varResult, 3) & _ Format(Val(Right(varResult,2))+1, "00") End If "BruceM" wrote in message ... Not sure where to post this. I have a database for making occasional reports. The first report of this year is numbered RPT-05-01, the second RPT-05-02, etc. My table (tblReport) contains fields for RPT_Number (such as 05-01) and RPT_Sequence (the last two digits of RPT_Number). A form (frmRPT) is based on the table. I have placed the following code into the form's On Current event: If IsNull(Me.RPT_Sequence) Then Me.RPT_Sequence = Nz(DMax("RPT_Sequence", "tblRPT"), 0) + 1 End If If IsNull(Me.RPT_Number) Then Me.RPT_Number = Format(Date, "yy") & "-" & _ Format(Me.RPT_Sequence, "00") Else: Me.txtRPT_Number.Locked = True End If txtRPT_Number is the text box bound to RPT_Number. People can't change the number after the record is created. The above code assigns 05-01, 05-02, and so forth in the proper sequence, and the number is retained when returning to the record, but of course it will not restart at 06-01 next year. How can I assign the correct number at the start of next year? In general is there a better way of assigning the number, or are there any potential problems with the approach beyond what I have mentioned? |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
automatic sequential numbering in excel or word | greg2 | General Discussion | 1 | January 15th, 2005 05:35 PM |
Automatic numbering of documents shared on server | Graham Gobell | General Discussion | 1 | September 1st, 2004 03:40 PM |
Automatic paragraph numbering | Don Zalkin | Formatting Long Documents | 4 | June 21st, 2004 02:00 PM |
Need help with (1) automatic paragraph numbering; (2) page __ of ____ | [email protected] | New Users | 3 | May 10th, 2004 06:47 PM |
Automatic row numbering | Judy | Setting up and Configuration | 1 | December 12th, 2003 07:01 PM |