Issue with data reload and code not working

[Stephd8459]

Hey all,


I have an odd situation with some code I have for an Alpha Numeric autonumber field

The below code looks at a field determines the highest number, which is in the last 4 positions and then returns the next value-

'autonumber

[FONT=Verdana][COLOR=black]Private Sub Region_AfterUpdate()[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]If Me.NewRecord Then[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]  Me.Code = Me.Region & Me.[PrePaid or Maintenance] & _[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]            Format(Val(Nz(DMax("Right(code,4)", _[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]                           "[Support Detail]"))) + 1, "0000")[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End Sub[/COLOR][/FONT]

**** the above code had to be editted to be posted the section ("Right(code,4)" would actually have [] around the word 'code' ****

It was functioning correctly until I reloaded the table with new data.
I have been creating the db with test data, I went to my source for the actual data and after deleting the test data and adding in the actual the code does not find the highest number. There are no errors, and when I compared the steps from the old db and the new the function the same. I have checked the format on the field and find no difference. Any suggestion would be appreciated.

Thanks
Steph

 

[DCrake]

Does not help using the word Code as a field name (Access Reserved word) also auto number fields cannot be alpha-numeric.

 

[Stephd8459]

As I said this code did function before I reloaded the data, and I call the field 'autonumber' but it is not the standard autonumber field access creates. In the data Type it is a text field called 'code' and I have the VBA code to create a value ... semantics that I called it autonumber

Also, fyi - 'code' is not an Access reserved word
http://office.microsoft.com/en-us/a...7-reserved-words-and-symbols-HA010030643.aspx

 

[DCrake]

What heppens if the field is greater than 00009999?

Because you are restricting your search to the last 4 characters you will be limited to 9999. Change it to look for the DMax without the Right,4 clause.

Then use the format

Format(n,"00000000") where n is the next id

 

[Stephd8459]

The field is an Alphanumeric field so I thought I needed to parse out the alpha to allow the code to identify the numeric value

Example format of the field is below:

NAMN0023
APMN0102

 

[DCrake]

Create a new query that has the following column

LastId:Right([IDfield],4)

sort the column Descending

Then use a dlookup on this field and add 1 to it

 

[Stephd8459]

I tried to use the option of a query when I first created the VBA code. I wasn't able to get it to function. I need the 'Code' field to autopopulate after the user selects 'Region' on the form.

I was hoping someone would have a suggestion as to why if I change the data in the table it would cause the code to no longer work correctly

 

[DCrake]

Is there anything of significance between the test data and the real data? Can you post an example of your db to look at?

 

[vbaInet]

I was hoping someone would have a suggestion as to why if I change the data in the table it would cause the code to no longer work correctly

I think there are IDs with over 4 number characters at the end. If that's the case then you woudldn't get the right Max. Look through the records.

 

[Stephd8459]

vbaInet,
Thanks for the hint... it actually ended up being that 1 records value didn't have a long enough value... it was 1 digit short... was a lot of records to comb through

Thanks

 

[vbaInet]

You would have been able to write a quick function to check it for you using Right(,4) and IsNumeric()

It might be an idea to investigate how that ID was saved without passing your validation checks.

Good luck!