AutoNumber Field

[Toga]

What's the best approach to building my own AutoNumber subroutine? I'm going to be porting my tables to Sybase where while you can create an Autonumber field using the Identity feature but when you link it to Access, Access recognizes it as a text field.

Anyway, I thought it would just be easier to format my fields as a number and do the autonumber thing myself. Is there a preferred way / query to do this?

Thanks...Toga

 

[MichaelRed]

faq700-184

Should be sufficient to get into a lot of trouble.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Toga]

LOL....Didn't like that idea huh

 

[94haddockt]

Try putting this in the open event or whatever you prefer

Dim CID As Long
DoCmd.GoToRecord , , acLast
CID = ClientID
If CID = 0 Then
ClientID = 1

Else
DoCmd.GoToRecord , , acNext
ClientID = CID + 1
End If

 

[MichaelRed]

94haddockt,

but when / if te order is "un-natural" (e.g. Index is set) the last record may/is not the most recent. This approach also FAILS badly for mult-user apps.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ghubbell]

Toga, One more choice on the pile: Thread181-59887 Gord
ghubbell@total.net

 

[Toga]

Actually, I decided against this bright idea after Mr. Reds first response (Who says I don't listen!) and persevered to get it to work as I figured it was supposed to. Anyway, for those that happen to run into this problem using Sybase,
the Identity Field (Sybase version of autonumber)was defaulting to 18 characters long....Tell me that wasn't a BIG clue! Anyway, because the Access autonum is really a Long integer (10 characters long), it was interpreting this as a Text field when I linked it in. I changed the Sybase default setting to 10 characters and it then iterpreted the field as Double. At 9 characters....Wha Lah, it linked in and was interpreted as a Long Integer.

I'd like to take the credit, but credit goes to our resident IS&S guy for searching this out. Anyway, thought you'd like to know....

Toga