Assign next value automatically

[Carlos]

Hi,

I know this gonna be simple for expert but not for me.

Say I have 2 fields: RefNo (long integer) and Rev (text).
Rev stands for revision with the first revision as “-
“(hyphen) followed by alphabetical sequence A, B, C,
D . . . .Z, AA, AB, AC…. etc.

I have coded a command button to add a new record with
auto RefNo = DMax("RefNo", "tblName") + 1 and Rev = “-“ as
the first revision.

Currently I’m using copy and paste (datasheet view) for
the next revision and just edit the revision as
appropriate.

How could I make a command button (say Next Rev) to copy a
record and assign next revision automatically having said
the sequential condition?

I tried the built in code “Duplicate Record” but got stock
how to assign the next revision.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

If the revision is a number then I could easily increment
the value by 1. Any advice will be much appreciated.

Thanks.


Carlos

 

[John Vinson]

If the revision is a number then I could easily increment
the value by 1. Any advice will be much appreciated.

Given the oddity of your Rev field I'd suggest just creating a table
with two fields - a sequence number and the corresponding Rev.

You could use some VBA code with the Asc() and Chr() functions to
increment A to B, detect that you've hit Z and go on to AA or
whatever... but a table driven solution would probably be simpler.

John W. Vinson[MVP]

 

[Carlos]

Thanks John. I use Asc() and Chr() functions in VBA with
few trial and error and it works. Thanks for the advice.

God Bless,

Carlos