Auto incrementing letter part of an alphanumeric key

[AmyAimee]

We can replace your code in just one line. Just let me know the outcome of the above.

That's very promising! I can't wait to break the back of it, although it gives me an #Error when I test it. What could probably cause such an error?

 

[vbaInet]

Copy and paste the following into the Control Source of a textbox for the sake of testing:

[COLOR=red]=[/COLOR]UCase(Chr(Asc(Nz(DMax("[COLOR=red][[/COLOR]Letter[COLOR=red]][/COLOR]", "T_M_AutoClaimNo", "[COLOR=red][[/COLOR]GroupType[COLOR=red]] [/COLOR]= 'GC'"), [COLOR=red]"@"[/COLOR])) + 1))

I made a slight mistake there. 96 should have been "@". I also added some extra bits - highlighted in red.

 

[AmyAimee]

It gives me the same error again

 

[vbaInet]

And what do you get when you:

=DMax("[Letter]", "T_M_AutoClaimNo", "[GroupType] = 'GC'")

?

 

[vbaInet]

Ok, we're going to keep adding to the code. This:

=Asc(Nz(DMax("[Letter]", "T_M_AutoClaimNo", "[GroupType] = 'GC'"), "@"))

?

And are you sure when you copied the copied you didn't miss off a parens?

 

[AmyAimee]

It works, the content of the [letter] field appears

 

[AmyAimee]

Ok, we're going to keep adding to the code. This:

=Asc(Nz(DMax("[Letter]", "T_M_AutoClaimNo", "[GroupType] = 'GC'"), "@"))

?

And are you sure when you copied the copied you didn't miss off a parens?

the number 64 appears

 

[vbaInet]

And finally,

=Chr(Asc(Nz(DMax("[Letter]", "T_M_AutoClaimNo", "[GroupType] = 'GC'"), "@")) + 1)

?

NB: I realised we don't need Ucase()

 

[AmyAimee]

It works now

 

[vbaInet]

So you must have missed off a parenthesis when you copied the code. It happens sometimes in these boxes.

I'm guessing you know how to do the other bits?

 

[AmyAimee]

I already set the format of the [letter] field to '>' to get it in upper case

 

[vbaInet]

I already set the format of the [letter] field to '>' to get it in upper case

You don't need that.

 

[AmyAimee]

So you must have missed off a parenthesis when you copied the code. It happens sometimes in these boxes.

I'm guessing you know how to do the other bits?

Well, honestly I'm trying to place it somewhere in my function, I don't know if it's going to work in the VBA editor though.

 

[vbaInet]

I thought I told you that we can replace your function with one line of code? You don't want to let the hardwork you put into writing that function just go to waste

 

[AmyAimee]

Lol, That's "Programming world", I have to get used to it.

Now I'm trying to make use of that effective one line of code to generate the key in it's final format.

 

[vbaInet]

Where's the Year coming from? I've just put 2010 in there so you can amend the code to suit:

="CLM/GC/" & Format(Nz(DMax("[Letter]", "T_M_AutoClaimNo", "[GroupType] = 'GC'"), 0) + 1, "0000") & "2010" & Chr(Asc(Nz(DMax("[Letter]", "T_M_AutoClaimNo", "[GroupType] = 'GC'"), "@")) + 1)

 

[AmyAimee]

The year comes from the function parameter, I've amended it and it works perfectly! Finally, it's done! Thank you, vbaInet!

 

[vbaInet]

You're welcome!