datasheet sequential numbers automatic entry

[trevor40]

Hi all, i am having a same issue with entering cheque numbers. using Access 2010 I have a datasheet form, very basic only 6 fields, the first is a cheque number. When I enter the next sequential number in the new record line and use the down arrow key to move to the next new record that new record is populated with the next number, then holding the down arrow key it continues adding cheque numbers for as long as I hold the down arrow. My problem is that the PC had a corrupted office file and had to be reinstalled, and now the auto feature has stopped working. Is there a setting I need to change to allow this to happen?

regards


Trevor

[ItsMe]

I know you are focused on what changed (you reinstalled Office). However, I can't think of a reason why this would create the symptom you are describing.

Not sure I am following correctly. If there is the ability to set focus in a new record, then the form's settings/properties should be correct. If there is a PK with autonumber field in the table/query, I would display that field in the form. Then, I would test the arrow keys to see if the autonumber functions correctly. This might give you a path to follow, depending on whether or not the autonimber populates the PK field.

[trevor40]

ok, i have now seen this same thing work/not work on different pc's and versions of access.

xp/acc2003 it does work

in datasheet view
one field + pk

if i put a 1 in the first row, then a 2 in the next row then use the down arrow to goto the next record it puts 3 there for me, if hold the down arrow it continues adding and incrementing, the pk also increments.
if i put a 2 in the first field and then a 4 in the next row and then use the down arrow to goto the next row it increments by 2 putting 6 in the next row, and so on.

this is the same as in excel when you have say 1 and 2 in cells and select both cells and use the bottom right hand corner to create a range. unlike excel it won't do it for month names.

it won't do this in win7/acc2010

is it a setting in acc2010 causing this?

[ItsMe]

I don't recall ever seeing new records being added using only the arrow key. I don't have an XP box at my main desk to test it, either. I do not see anything in A2010 options that might change the behavior.

[trevor40]

In datasheet view after entering data in any field, then use the arrow key to navigate to the next (new) record. it updates the record just entered.

[CJ_London]

to get this effect, in the chequeNo control beforeupdate event put

ChequeNo.DefaultValue=ChequeNo+1

[keviny04]

to get this effect, in the chequeNo control beforeupdate event put

ChequeNo.DefaultValue=ChequeNo+1

The OP says it doesn't just increment by one, but by the DIFFERENCE of the last two records' ChequeNo value, i.e. 2 and 4 in the last two rows yield 6, 8, 10 in subsequent rows, while 2 and 5 would yield 8, 11, 14, etc. That would be tricky to implement even with code.

But tricky coding still wouldn't explain why it worked only on XP and not other systems. I think there is missing info that the OP hasn't provided. Did someone else work on his database before? Was the database the exact copy before and after his PC crashed? Was there something amiss in the process of migrating the database from Access 2003 to 2010?

[CJ_London]

Agreed. but for this particular requirement, it is an option that provides the functionality required. I've no idea about the 'automatic' functionality in 2003 and why it is not available in 2010 - and was it available in 2007?

[keviny04]

I've been using Access 2007 for years and I know of no such functionality. It seems like coding was involved, but why would code that worked in Access 2003 didn't worked in 2010? I once had an Access 97 database with hundreds of subs, and when I migrated it to Access 2007, I BARELY had to change any code. And if his old code from 2003 really didn't work in 2010, why didn't it error out? Maybe the OP should upload some screenshots and/or both the working and non-working databases for us to see.

[CJ_London]

The OP says it doesn't just increment by one, but by the DIFFERENCE of the last two records' ChequeNo value, i.e. 2 and 4 in the last two rows yield 6, 8, 10 in subsequent rows, while 2 and 5 would yield 8, 11, 14, etc. That would be tricky to implement even with code.

Not really.

Depends on what you actually want to achieve but this code will increment by whatever difference the user types between rows

Option Compare Database
Dim gnum As Long
Dim inum As Long

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    inum=0
    If Field1.DefaultValue = "" Then 'this is the first entry, increment not known, so set to same value
        gnum = Field1
        Field1.DefaultValue = Field1
    End If

    If Field1 <> gnum Then 'reset the increment from 0
        inum = Field1 - gnum
        Field1.DefaultValue = Field1 + inum
        gnum = Field1
    Else 'apply the defaultvalue plus increment
        Field1.DefaultValue = Val(Field1.DefaultValue) + inum
    End If
    
End Sub

[trevor40]

Hi Again, ok there is no code in this at all. to test it i created a blank table with 1 field named one the db added the pk, when saving the table then auto create a datasheet form, now put a 1 in row one and select a new record, put a 2 in that record and select a new record again. Now enter a 3 into the new row and use the down arrow to update it and the numbers auto increment. i tested this on acc2003 it works, i tested this on acc2010 and it doesn't work.

i gave up and used vba to create the numbers for me, but it was easy to do it the old way.

no changes were made to the system on the acc2010 db but after reinstalling office it stopped working, but did work prior to the reinstall on the acc2010 pc.

[keviny04]

Not really.

Depends on what you actually want to achieve but this code will increment by whatever difference the user types between rows

Option Compare Database
Dim gnum As Long
Dim inum As Long

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    inum=0
    If Field1.DefaultValue = "" Then 'this is the first entry, increment not known, so set to same value
        gnum = Field1
        Field1.DefaultValue = Field1
    End If

    If Field1 <> gnum Then 'reset the increment from 0
        inum = Field1 - gnum
        Field1.DefaultValue = Field1 + inum
        gnum = Field1
    Else 'apply the defaultvalue plus increment
        Field1.DefaultValue = Val(Field1.DefaultValue) + inum
    End If
    
End Sub

But it still doesn't let you enter a new row with just a press of the down arrow key as the OP said.

[trevor40]

On an XP/acc2003 db try the above it DOES work! iv'e been doing it for 7 years until the reinstall.

[keviny04]

On an XP/acc2003 db try the above it DOES work! iv'e been doing it for 7 years until the reinstall.

I never used 2003, but on this list of differences between 2003 and 2007, I may have found the answer to your question. Under the section "What's fixed (old issues solved)", it says this:

Autofill (datasheets) -- Previous versions attempted to guess the number you wanted when entering data in a datasheet. This annoying behavior has been removed.

Even though this was a feature you liked, many people must have complained about it, so it was removed in 2007.

[trevor40]

ok, that does sort of explain it, but why would it stop working on an acc2003 db running on a acc2010 pc ?