Increment number in field when another field updated

[redpenner]

I have a table BILLING which I enter data into using a form in datasheet view. Two of the fields are BILLDATE and INVNUMBER. What I would like to do is automatically enter a new INVNUMBER (calculated as the previous maximum invoice number + 1), but not until the BILLDATE is filled in. I don't want to enter a number when the record is first added, because they are added well in advance of actual billing. I want this to be a default value, but need to be able to modify it if needed.

I can calculate the incrementing invoice number all right, but not sure how to code in (or create macro) to calculate and save it when a value is entered in BILLDATE. (I'm still just learning how to use Events to trigger actions.)



Can anyone suggest a good, preferably simple, way to go about this?

Edited to add: I am using Windows 7 now, not Vista - should have updated before posting

[June7]

Try BillDate AfterUpdate event. Probably don't want to increment the InvNumber every time BillDate is edited for the same record. So try:

If IsNull(Me!InvNumber]) Then Me!InvNumber = DMax("InvNumber", "tablename") + 1

[redpenner]

Tried it, got a Syntax Error.

[June7]

Sorry, typo - unnecessary ] after InvNumber.

[redpenner]

Now getting a type mismatch. It occurred to me that my INVNUMBER field is a text field (because back a few years we had an alpha prefix on our invoices). Do I have to extract a numeric string or something to get the +1 to work?

[June7]

Yes, will have to extract numeric part.

Is there only one character as the alpha prefix? The full value is always same number of characters? Provide examples.

Generating custom unique identifier is a common topic. Here is one https://www.accessforums.net/access/...ber-20506.html

[redpenner]

Most older values are of the format NC2011178, a few have an alpha character following (NC2011178a) and more recent values are like 20140021, some with the same alpha suffix. I'm thinking I should just strip off the prefixes and suffixes and put them in separate fields, since most of those are historic but we occasionally still do have the suffix alpha. I can concatenate the 3 fields for the invoice or any other kind of reporting. Since I have 4 different formats to deal with, it seems like the wise thing to do, and not too much work for me on a slow day like today.

I think I'll go ahead and do that, then try your solution again.

[redpenner]

Well, it works very well! When I fill in a BILLDATE, the correct INVNUMBER fills in, and I can edit or delete it if I want. And I'm much happier about my three-part invoice number solution.

Thank you for the code! I appreciate your time and help.