469,330 Members | 1,330 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

Autoincrement to Long

Tim
Hello All, I could use some help on an error that is just now popping it's
head up.

Seems that the autoincrement numeric has hit 32,767. The autoincrement is
used in various locations in the database, but in this case (log sheets), we
have hit 32k log entries over the past 2 years.

Is there a simple switch to set the autoincrement from integer to long
integer?

While I need to take care of this problem relatively quickly so that the log
sheets can be entered, I also need to solve how this error using
autoincrement will affect other areas of the database using that feature.
So hopefully it is just a matter of telling the autoincrement that it is now
a long integer instead of an integer; therefor just a blind spot I didn't
see coming 2 years ago that we would hit 32,000 entries. :-)

At least that is my hope. I did a quick google/groups search on increment
converting to long, and got a topic where the discussion was about
converting autoincrement to long, but you lose the ability to autoincrement.
That was a quick view and am still looking at other threads to see if this
really can be done. I don't want to lose AutoIncrement, but I also have
areas in the database that will also hit 32,767 entries at some point in the
future, so this has to be addressed.

Any helpful suggestions, reads, or instructions on a fix?

Tim
Aug 27 '07 #1
4 2180
Tim
Ok, after getting a chance to get in the database to find out what is wrong,
here is what is really the problem; my self-made increment feature. :-)

Private Sub Form_BeforeUpdate(Cancel As Integer)
'
If Me.NewRecord Then
'
Dim intTestCount As Integer
Dim intNextCount As Integer
Dim strCounterName As String
'
strCounterName = "invLineCount"
intTestCount = Nz(DLookup("[nxtCountInteger]", "tblCounter",
"[nxtCountName] = '" & strCounterName & "'"))
intNextCount = intTestCount + 1
[invlineId] = intNextCount
CurrentDb.Execute "UPDATE tblCounter SET nxtCountInteger = " &
intNextCount & " WHERE [nxtCountName] = '" & strCounterName & "'"
'
End If
'

End Sub
As I remember back 2 years ago, I am reminded that I created my own
'autoincrement' feature, not the internal AutoNumber system that Access has.
But it still brings us back to the problem of Integer count only allowed to
hit 32,767 and can not proceed higher. So the field it is going into is a
Long, and all is fine there it looks like, I just need to convert this
routine to use LongIntegers and be up and running.

Can I just change the routine from As Integer, to As Long; and all will be
fine?

I will verify all locations in the database where this self autoincrement
feature is used and can change the integer to long; but I will also make
sure the field where the number is being held also is set for a Long.

Any consequences I am not seeing by just changing the Integer to Long?

Tim



"Tim" <no*****@noemail.comwrote in message
news:c-******************************@comcast.com...
Hello All, I could use some help on an error that is just now popping
it's head up.

Seems that the autoincrement numeric has hit 32,767. The autoincrement is
used in various locations in the database, but in this case (log sheets),
we have hit 32k log entries over the past 2 years.

Is there a simple switch to set the autoincrement from integer to long
integer?

While I need to take care of this problem relatively quickly so that the
log sheets can be entered, I also need to solve how this error using
autoincrement will affect other areas of the database using that feature.
So hopefully it is just a matter of telling the autoincrement that it is
now a long integer instead of an integer; therefor just a blind spot I
didn't see coming 2 years ago that we would hit 32,000 entries. :-)

At least that is my hope. I did a quick google/groups search on increment
converting to long, and got a topic where the discussion was about
converting autoincrement to long, but you lose the ability to
autoincrement. That was a quick view and am still looking at other threads
to see if this really can be done. I don't want to lose AutoIncrement,
but I also have areas in the database that will also hit 32,767 entries at
some point in the future, so this has to be addressed.

Any helpful suggestions, reads, or instructions on a fix?

Tim


Aug 27 '07 #2
Tim wrote:
Ok, after getting a chance to get in the database to find out what is wrong,
here is what is really the problem; my self-made increment feature. :-)

Private Sub Form_BeforeUpdate(Cancel As Integer)
'
If Me.NewRecord Then
'
Dim intTestCount As Integer
Dim intNextCount As Integer
Dim strCounterName As String
'
strCounterName = "invLineCount"
intTestCount = Nz(DLookup("[nxtCountInteger]", "tblCounter",
"[nxtCountName] = '" & strCounterName & "'"))
intNextCount = intTestCount + 1
[invlineId] = intNextCount
CurrentDb.Execute "UPDATE tblCounter SET nxtCountInteger = " &
intNextCount & " WHERE [nxtCountName] = '" & strCounterName & "'"
'
End If
'

End Sub
As I remember back 2 years ago, I am reminded that I created my own
'autoincrement' feature, not the internal AutoNumber system that Access has.
But it still brings us back to the problem of Integer count only allowed to
hit 32,767 and can not proceed higher. So the field it is going into is a
Long, and all is fine there it looks like, I just need to convert this
routine to use LongIntegers and be up and running.

Can I just change the routine from As Integer, to As Long; and all will be
fine?

I will verify all locations in the database where this self autoincrement
feature is used and can change the integer to long; but I will also make
sure the field where the number is being held also is set for a Long.

Any consequences I am not seeing by just changing the Integer to Long?

Tim



"Tim" <no*****@noemail.comwrote in message
news:c-******************************@comcast.com...
>>Hello All, I could use some help on an error that is just now popping
it's head up.

Seems that the autoincrement numeric has hit 32,767. The autoincrement is
used in various locations in the database, but in this case (log sheets),
we have hit 32k log entries over the past 2 years.

Is there a simple switch to set the autoincrement from integer to long
integer?

While I need to take care of this problem relatively quickly so that the
log sheets can be entered, I also need to solve how this error using
autoincrement will affect other areas of the database using that feature.
So hopefully it is just a matter of telling the autoincrement that it is
now a long integer instead of an integer; therefor just a blind spot I
didn't see coming 2 years ago that we would hit 32,000 entries. :-)

At least that is my hope. I did a quick google/groups search on increment
converting to long, and got a topic where the discussion was about
converting autoincrement to long, but you lose the ability to
autoincrement. That was a quick view and am still looking at other threads
to see if this really can be done. I don't want to lose AutoIncrement,
but I also have areas in the database that will also hit 32,767 entries at
some point in the future, so this has to be addressed.

Any helpful suggestions, reads, or instructions on a fix?

Tim
Get everyone out for a quick change and set the numeric type to Long for
that field in the database tables.

Then anyplace you dim the var to int instead of long change to long. I
doubt there's many locations you'll need to do that.
Aug 27 '07 #3
Tim
"Salad" <oi*@vinegar.comwrote in message
>Any consequences I am not seeing by just changing the Integer to Long?
Get everyone out for a quick change and set the numeric type to Long for
that field in the database tables.

Then anyplace you dim the var to int instead of long change to long. I
doubt there's many locations you'll need to do that.
That is what I was thinking. I think there may be about 8 locations that I
can find and change them. I just didn't know if it would change anything
when going from Integer to Long. Didn't want to find any 'gotchas' for
something that seems so simple.

Thank you for the reply.

Tim
Aug 27 '07 #4
"Tim" <no*****@noemail.comwrote:
>That is what I was thinking. I think there may be about 8 locations that I
can find and change them. I just didn't know if it would change anything
when going from Integer to Long. Didn't want to find any 'gotchas' for
something that seems so simple.
Salad has hit the high points.

That said I'd recommend doing a find through *ALL* your code looking for Integer.
Not just in the places you think might be a problem. Chances are you can change them
all the longs. You don't want to suddenly find out that one particular chunk of VBA
code has wrapped around and is now updating record ID 1, 2 or 3. Or something
similar.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Aug 28 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Fabio Negri Cicotti | last post: by
reply views Thread by Neil | last post: by
6 posts views Thread by Dennis | last post: by
5 posts views Thread by JC Voon | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.