Assign an Autonumber Value From VBA 1

[GaryCanuck]

Hi All,
I'm currently running Access 2007. I have a field called Close# that I would like to increment from the previous highest Close#. There are a number of complicating factors however.

First, the tables are shared with another program and the field is a text field. So it can't simply be changed to autonumber. Second, the format of the number has changed over the years and includes the letter "C". It has been #C##, ##C## and most recently #####C. The most recent numbers are 5 digits so if the C were removed or if the number to the left of the C were returned by the code then it would successfully return the most recent number.

I have gotten the following code to return the highest number, remove the "C", and increment it by one. The issue is that it is only returning the highest number to the right of the "C".

Does anyone have suggestions on how to alter my code or even an alternate way of doing this? I greatly appreciate any help or suggestions.

Here's the code. It's running on the double click event of a form text field.

Private Sub CLOSE__DblClick(Cancel As Integer)
Dim strMaxKey As String
     strMaxKey = DMax("[Close#]", "File")
     'MsgBox (strMaxKey)
     strMaxKey = Replace(strMaxKey, "C", "")
     'MsgBox (strMaxKey)
     NextKey = Format(Val(Right(strMaxKey, 5)) + 1, "00000")
     'MsgBox (NextKey)
End Sub

 

[PHV]

strMaxKey = DMax("[Close#]", "File","[Close#] LIKE '*C'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

You might want to post in one of the many MS Access fora like forum705.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GaryCanuck]

Thanks PHV. That's getting close to what I'd like it to do. There's some sort of glitch still though. It's returning 9999C which for some reason is "bigger" than 10000C etc. Is there a limit on the number of characters the function will count up to? Surely not. Or is it assigning a value to the "C" perhaps.

 

[SkipVought]

returning 9999C which for some reason is "bigger" than 10000C

TEXT colating sequence...
[tt]
1
10
100
10000C
2
20
200
9
90
900
9999C
[/tt]
because TEXT colates left to right, "9" is greater than "1"



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GaryCanuck]

Ah Yes that makes sense. So then the next obvious question is how do I modify my code to reflect that and choose the actual largest number. Could I for instance set a minimum of five digits?

 

[SkipVought]

You are not really assigning a NUMBER. You are assigning a STRING with numeric characters.

Parse your value to separate the numeric characters from the alpha like...

NumPart = Left([YourValue],Len([YourValue])-1)

assuming that you ALWAYS have one alpha character in the right-most position.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

NextKey = Format(1 + DMax("Val([Close#])", "File", "[Close#] LIKE '*C'"), "00000")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GaryCanuck]

That's got it PHV. Thanks Again!