How increment number by 1 when text field is one of three choices
Posted on 2012-12-27
I have a form which has a field which has been populated from an option group on a separate form for the creation of a new record. This text field, named "txtDrawingType", has either been populated with a "M", a "P", or a "I" from the option group in the separate form.
On this same form is a field named "txtDrawingNumber". This field can hold a value between:
If "M" in field "txtDrawingType" - 1,000 to 29,999
If "P" in field "txtDrawingType" - 30,000 to 39,999
If "I" in field "txtDrawingType" - 40,000 to infinity
Here is the challenge. For example, if the drawing type is "M", and the last record written for a "M" type drawing is 1,205 (for example), then the new "txtDrawingNumber" needs to be 1,206.
As another example, using a drawing type of "I", if the last record written had a drawing number of 50,234 then the new record needs to have its drawing number be 50,235.
I have no idea where to begin. Can someone help?