Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Feb 2009
    Location
    Numurkah, Vic, Australia
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,

    I have just upgraded to office 2010 and i have noticed that in my Access Database, that it no longer auto inserts the next value in sequence in a column of records -

    I am a basic user - no programming involved.

    Basically, prior to Access 2010 if i had a entered information via a query, and one of the columns was a blank field. If i started the 1st record with say 101 and then cursored down to the same field in the next record and entered 102, a further scroll down would automatically add 103 and so forth.

    Access 2010 does not perform this natively - and I have been using Access since Version 1 and it is the 1st time i have come across this.

    I could not locate anything in the (very limited) Access Help and i cannot explain why this has changed.

    Does anyone have any thoughts / solutions / directions for me to work with?

    Rgds
    Phill...

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Access does what you describe if the data type of the relevant field is set as Autonumber.

    I don't have Access 2010, but I presume autonumbers still exist. What is the datatype of the field you are using?

    To find out the datatype, you need to look at the Design of the Table.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Can you provide further details? I also have been an Access user (and developer) since day 1 of Access 1.0 and am unaware of any automatic behavior like this. It does do this in Excel, but not in Access that I've ever noticed (maybe I'm not nearly as observant as I thought ) . If you are using an autonumber, you can't enter it at all.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I can see now that I misread the question. Phill is not talking about autonumbers, because he says he enters 101 in the first field.

    I can't remember seeing the behaviour you describe in the past either.
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Feb 2009
    Location
    Numurkah, Vic, Australia
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,

    Thanx for the feedback.

    The Field is actually a text data type

    Changing to Autonumber cannot be actioned due to pre-existing data.

    Changing to number - also did not do anything.

    In the past, i have exported the query to Excel, - performed some bulk actions against the data and then imported back into Access, the auto incrementing has been occurring for as long as i can remember - as to when it first started to appear - i dont know.

    BTW - the database is in Access 2000 format..

    I converted to Access 2003 format - still the same
    Converted from 2000 to 2010 .accdb - still the same.

    Rgds
    Phill..

  6. #6
    New Lounger
    Join Date
    Feb 2009
    Location
    Numurkah, Vic, Australia
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Further to my post above - here is an attachment of the table properties for this particular field.

    [attachment=88734:access properties.JPG]

    Phill.
    Attached Images Attached Images

  7. #7
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Excel involved - I'm not surprised. I am surprised that this happens with entries in Access. In fact, I am going to ask for a demonstration .

    In Access, open the table (no query, no form). Enter something into any other field other than the one in question (let's call the field in question Field 1). Show us a snapshot of Field 1 before and after the entry into the other field. Then commit the new record (by positioning to a different record). Make a snapshot of Field 1 again.

    Post those and we'll go from there.

  8. #8
    New Lounger
    Join Date
    Feb 2009
    Location
    Numurkah, Vic, Australia
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Larry,

    When the import / export was done with excel, i think i used the csv format to perform this.. i dont think i exported / imported using XLS.

    I will respond tomorrow about the way the fields / records are displayed. I am trying to discover what older versions of Access do that allows this to occur - especially if i am using other databases.

    Unfortunately, my PCs and laptop are all running 2010 - and i will have to use logmein to connect to other PCs that i know that run Access 2003. I use Access 2007 at my workplace and it also performs this (what appears to be a) unique activity..

    I took it for granted that Access did it for everyone on all versions..

    Will keep you posted.

    Phill..

  9. #9
    New Lounger
    Join Date
    Feb 2009
    Location
    Numurkah, Vic, Australia
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    aha..

    My brothers PC is operational - i created a new database in Access 2003.

    Designed a table (called it table1)
    First field is called field_one and is a TEXT field
    Second Field is called field_two and is a TEXT field

    Saved the design - NO Primary Key is defined

    [attachment=88735:access test 1.jpg]

    Switched to Table view

    [attachment=88736:access test 2.JPG]

    In the first field i entered 101

    [attachment=88737:access test 3.JPG]

    Hit the down arrow and entered 102

    [attachment=88738:access test 4.JPG]

    [attachment=88739:access test 5.JPG]

    Hit the down arrow and 103 appeared (with text fully selected) of its own accord.

    [attachment=88740:access test 6.JPG]

    This appears to a native activity in Access 2003 .. so i believe my export/import with excel is not relevant.

    Can you try this for yourself and advise.

    Rgds
    Phill
    Attached Images Attached Images

  10. #10
    New Lounger
    Join Date
    Feb 2009
    Location
    Numurkah, Vic, Australia
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PS - if you keep on holding the down arrow - it adds records and increments the numbers to its hearts content.

    phill

  11. #11
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I cannot reproduce this with Access 2007 with Access 2003 format. It should take code to have this sort of behavior. That, we all could help you with.

    Maybe it is one of those "south of the equator" things.

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I can reproduce that behavior in Access 2002 and 2003, but not in 2007 or 2010. I believe the "feature" was introduced in 2000 or 2002, and I think it was called Autocomplete, but I haven't been able to verify that. The intention was to behave in a similar fashion to Excel. Curiously enough, I can't find any mention of it being removed in 2007. I'll make some inquiries and see what documentation exisits.

    UpdateA softie I know just pointed me to this on Access Junkie
    Wendell

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I have just fired up my copy of Access 2003 and copied your steps exactly, and Yes I can replicate the behaviour you describe.

    But I have to do exactly what you describe. If I don't use the downarrow, for instance, just Tab or use the mouse I don't get it.

    I have now reopened the same database in 2007 and it does not happen.

    PS I am in the Southern Hemisphere , so maybe Larry is right!

    Maybe it is one of those "south of the equator" things.
    Actually I have just found a reference to this here. It came in with Access 2000. This claims it works with the Tab key also.

    I can't find anything official to say it has been removed, but I did find another post where someone else has complained about it being removed.
    Regards
    John



  14. #14
    New Lounger
    Join Date
    Feb 2009
    Location
    Numurkah, Vic, Australia
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I thought I was going mad.. as the only person to see it.. disappeared in 2007 - hmph.. didnt realise that.. yet i thought it worked on my employers workstations..

    I have read all of the links that have been offered - one suggestion was using CTRL + ' but that only replicates data from the record above (in the same field) ..

    I thought i would be a smartie and see if CTRL + ; also worked ( Excel = insert current date ) but it didnt and i got an error message - it was worth trying..

    Anyhow, thank you to all.

    Phill..

    PS - so the "down under" versions of ACCESS are the same as those in the "UP over" parts of the world

  15. #15
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    If you really need this functionality then you could reproduce it using datasheet view of a form (after all users shouldn't be entering data directly into a table anyway).

    in the keydown event of the field use some code like this
    Code:
    Select Case KeyCode
            Case vbKeyDown
                intNewValue = CInt(Me.txtWhatever) + 1 ' assuming that you are incrementing numbers in a text field
                DoCmd.GoToRecord , , acNext 'this will take you to the same field in the next (or New) record
                if isnull(me.txtWhatever) then me.txtWhatever = CStr(intNewValue)

Posting Permissions

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