Modifying Sequential Numbers

[connerlowen]

Hi,

I have a database for quotations. The database automatically generates a new quotation number every time a new quote is started. This works great, and I am very happy. My next task is to allow the employee to pull up a quote that has already been generated and edit it. I would like for the new quotation process to be followed step by step, but with all of the information already filled in. This will allow for any edits that need to be made, and keep from having to re-enter a lot of data. I want one thing to change, which is the QuotationNumber. It is currently formatted by "yyyymmdd-01" for the first quote generated on that day. I want the edited quote to have a QuotationNumber formatted by "yyyymmdd-01a". For every edit that letter change going through the alphabet in order. How would this new QuotationNumber code differ from that of the Other?



Thanks,

Conner

 

[vbaInet]

What's going to happen when you hit z?

 

[connerlowen]

start with aa, ab, ac, and so on hopefully.

 

[vbaInet]

I don't know if that's the best model for you or whether it's worth creating a separate field that will hold the "edit sequence"... you decide.

Going by your current setup, you will need to do several things:
1. Change the Data Type of the field from AutoNumber to Text.
2. Write code that detects/splits the numeric and alphabetic parts of your field
3. Write code that auto increments the numeric part for new records or increments the alphabetic part for edits. Now this is where having two separate fields becomes useful.

Look into the following functions:
DMax(), Mid(), Left() Len() and IsNumeric()

 

[connerlowen]

I have already done all of that in the code above for the New QuotationNumber. I want to have the edits code be very similar, use the QuotationNumber and add a letter to the end of it and call it the QuotationNumberEdit or something like that. What would I need to change or add to the coded above to do that?

 

[vbaInet]

I would advise that you first give the two field scenario some good thought. That's easier to work with.

Did you write that code? Why are you prefixing it with zeros?

 

[connerlowen]

I did not write the code. I found it on a website, but the poster did not have a copyright notice, so that is why there is not one there. However I had to go in and edit the code in several places to make it work for my needs. There are 10 zeros because that is how many total numbers there are. The 8 digit date and the 2 digit suffix for the sequential numbering.

 

[vbaInet]

I asked to gauge your level of knowledge of VBA. Have you looked into the functions I mentioned?

Besides the edits sequence, is everything else just numbers?

 

[connerlowen]

currently everything is just numbers and a dash that is in the input mast field.

 

[vbaInet]

Still wondering if you've looked into the functions I mentioned?

Why is a date part of the number? Don't you already store the quote date elsewhere?

 

[Solo712]

start with aa, ab, ac, and so on hopefully.

Not a good idea, Conner. You'll lose sequencing in the quotation code. The double-letter suffix starting with "a" will precede the single-suffixed ones. If you want to have a single quotation sequence with edits you will need to devise a way of incrementing the edit suffix. You best find a fixed-length code.

Best,
Jiri