Number Increment Problem

[cdw0308]

I have a field that is incremented by one when a new record occurs. The problem I'm having is that access sorts the table out of order when i exit the program and when i go to add a new record it tries to duplicate.
for example: it orders like this 1, 10, 2, 3, 4, 5, 6,7,8,9
and when i try to add a new record it should go to 11 but instead it continuosly tries to add 10 because the last field in the order is 9.

here is my code:
Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next
Me!tran_ID.DefaultValue = Nz(DMax("[tran_ID]", "delivery&quot, 0) + 1
End If
End Sub

Any Help would be greatly appreciated

 

[RoyVidar]

Hi!

You're setting the .DefaultValue property of the control, try settin the value:

[tt]Me!tran_ID.Value = Nz(DMax("[tran_ID]", "delivery&quot, 0) + 1[/tt]

Note - if this is a multiuser setup, this is not a good approach. Should rather have a look at MichalRed's faq faq700-184, or at least use the forms before update event.

Roy-Vidar

 

[cdw0308]

Thanks for the fast reponse roy

I tried changing defaultvalue to value and i also move it to the forms before update event but it still is ordering it like before. (1,10,2,3,4,5,6,7,8,9) This field is my primary number, if that helps any.
This particular section is not in a multiuser environment.
I did look at MichalRed's faq it seems a bit complicated for this particular problem. But i could be interpreting it wrong.
have any ideas?

 

[RoyVidar]

Hi again!

The DMax function can work on both strings and numerics. If your datatype for the primary key is text (which I suspect), then it uses alphabetic sorting. That means 9 is larger than 10 (by the first "character&quot.

You might bypass this by changing the datatype to numeric (Long?). I'm not sure if you can use DMax to find the "highest" "numeric string", well, perhaps if you use leading zeros.

Please note that the comment regarding the faq/event does not address why this increment didn't work (should perhaps have been able to see that you probably use text in the first post) but address the challenges arising in a multiuser setup. Since you say this doesn't apply, please disregard (but have it in mind if you need to autoincrement in a multiuser setup at a later time)

Roy-Vidar

 

[cdw0308]

thanks roy.
that did it. I just overlooked the datatype. The field is actually all numeric so i believe it will do what i am looking for right now. I will definately keep in mind the faq for multi user setup. Hopefully i can incorporate that later on.

Thanks again
cdw

 

[BudE]

I just had the exact same problem when I printed out my report. No matter what I did the numbering was all wrong. My solution? Just added a (0)Zero before the single digit numbers. If there is an even better way to do this for me automatically I would appreciate the input. Oh, by the way, nice to see you all as I am new here,my first time. Have a nice one

BudE

 

[Blorf]

An easy way to add zeros is like this (And I have to do this because of part numbers and our company, although I would rather use a numerif field.

Any way, say the field is called "Numb"

Corrected: right ("00000" & trim (Numb), 5)

This will automaticaly make 5 00005. Change the number of zeros, and the 5 at the end of the right statement to increase or decrease the number of digits you want the number.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[scottian]

put this in the on load event event.
===========================
Private Sub Form_Load()
Me.[Number] = DMax("[Number]", "11- Proposals") + 1
End Sub
=============================

you will have to set the form as data entry

"My God! It's full of stars..."
Dont forget to vote for helpfull posts