Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38

    Unanswered: Unwanted autonumber field

    I have an access file I have been asked to look at. It has 5 fields in the one table and the last field is being filled in after the fact with integer values.

    No matter what datatype is chosen, or what formats I choose, if you enter 3 numbers that are consecutive, or in an easy pattern, when the user uses the cursor key to move to the next field, it autofills with the next number in the sequence.

    Is there any way to turn this off?

    Thanks in advance for any help.
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    Well, from what you're saying it seems like the primary key in your table is an autonumber. The problem with changing this, I believe, will be that all the relationships that all your other tables, queries and form / report data (essentially the entire db) in your database will be completely screwed up causing the database to be really useless.

    I may be wrong but we'll see if others agree shortly.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I agree with Grafixx01. It sounds like an autonumber field (either that or the default value for that field is set to something).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i know exactly what Jeff means: it's not an autonumber thing.

    make a table IDcrap(auto PK), strCrap(text), intCrap(integer)
    open the table and add 5 strCrap entries (don't touch intCrap): 'a', 'b', 'c', 'd', 'e'
    now open the table again (or datasheet view in a form) and start adding some intCrap values and down-arrowing to the next intCrap.
    type 1 (everything is fine), downarrow
    type 2 (everything is fine), downarrow
    ...and automagically 3 appears.

    a strange way for a database to behave!
    i don't let users see datasheets or tables so it's not an issue for me.
    i didn't find any off-switch for this 'helpful' feature.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I reckon its probably something to do with autocorrect or intellisense.

    does it occur only in data sheet view?

    It doesn't happen on my copy of A2000/XP, but I've tinkered with so many settings I haven't got a scooby whether that's what Access 2000 does normally or whether its something I've turned off.

    So it may be an Access version setting.. especially if you are using a newer version of Access than 2000/XP
    Last edited by healdem; 06-15-07 at 05:10.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I've not seen anyway or anything to turn this "feature" off either ... Wanna hear my horror story?

    I had a table with 5 columns. Int, Int, Date, Text, Text.

    I added a row in table view.

    I typed in 1 in the 1st col
    Typed in 2 in the next
    GUESS what popped up in the Date column? You guessed right "3" ...

    So this feature isn't even intelligent or sensical ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    So is this a default value type of thing or is it a relationship to an autonumber thing? (given that the data is being entered in a datasheet view and NOT a form which could mean anything.) (or is it an MSAccess trying to sequentially number values when it sees a sequence starting type of thing (which I believe happens in a form (ie datasheet view in a form? - not opening the table directly).)

    Izyrider,

    I couldn't duplicate your scenario designing a table and then opening the table and entering values (not through a form.)
    Last edited by pkstormy; 06-15-07 at 10:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pkstormy
    So is this a default value type of thing or is it a relationship to an autonumber thing? (given that the data is being entered in a datasheet view and NOT a form which could mean anything.) (or is it an MSAccess trying to sequentially number values when it sees a sequence starting type of thing (which I believe happens in a form (ie datasheet view in a form? - not opening the table directly).)

    Izyrider,

    I couldn't duplicate your scenario designing a table and then opening the table and entering values (not through a form.)
    Paul,

    It's the latter ... And it does happen in the table view directly ... It does seem to be access anticipating the next number in a sequence ... FYI: this also happens for any kind of numeric sequence (perhaps except for those of float, single, double ) not just for sequential values ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Paul,
    integer field must be empty to display this happy feature - no default=0
    either delete the zeros or DEL the default=0 in table design

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ok, this peaked my curiosity. I tried setting the default to blank or 0. No matter how I enter text (and even trying to start a sequence in the number field), it doesn't increment. I also tried putting it in a relational scenario but perhaps I need to get a sequence started somehow. I had seen this once many years ago but I can't seem to do it now.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - I've seen this. Useful in excel, absurd in a database.

    I've never investigated it as it has only caught me when whacking data into dummy tables for tesing. Users only get to use GUIs so sorry - I know the problem but not the solution.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •