Access 2010 auto insert from field above issue

Topic

New Reply

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…

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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..

Reply | Quote

Further to my post above – here is an attachment of the table properties for this particular field.

Phill.

Reply | Quote

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.

Reply | Quote

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..

Reply | Quote

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

Switched to Table view

In the first field i entered 101

Hit the down arrow and entered 102

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

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

Reply | Quote

PS – if you keep on holding the down arrow – it adds records and increments the numbers to its hearts content.

phill

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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)

Reply | Quote