Access Autonumber Problem

[cbellew]

I have a problem with autonumbering in a database. It relates specifically to the use of the GoToRecord...acnewrec method.

When clicking a button with this method (to add a new record) the autonumber field is incremented by 2 as opposed to 1.

It is however fine when i add a new record via opening the table and clicking on then next free row.

Thanks in advance guys.

Chris.

[NeoPa]

Perhaps you're already at the new record when you say goto new record. This might explain it. Try the following to see if it helps :

If Not Me.NewRecord Then Call DoCmd.RunCommand(acCmdRecordsGoToNew)

[missinglinq]

The really important question here is why do you care? Autonumbers are intended to be used by Access for internal housekeeping tasks and really should be never available to users. John Vinson, MVP, posted this a while back:

“When using Autonumber, do be aware that there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous.”

If you need an account number, invoice number, ID number, etc, you need to implement an auto-incrementing number scheme, not use an Autonumber.

Welcome to TheScripts!

Linq ;0)>

[NeoPa]

That's very well put Linq (and I agree 100%).
I wasn't going to lay this on the OP as so many newbs to database concepts find it very confusing, but it's a good point well made.

[cbellew]

Replies greatly appreciated guys!

Linq - Good explanation, a point well made. I agree. It doesnt matter what number is assigned by autonumber. I guess i did not really explain the problem fully enough:

The problem i have occurs when i add a new record on a main form, which has a subform linked to it (by a one-to-one relationship). Both forms have the same record source For clarity, the main form shows the hospital employee and the subform shows their disease immunity information. I chose to have a subform because of the need for a large amount of controls, which would otherwise not be allowed by a single form.

The problem is that when i add a new employee on the main form, two records are created (lets say for instance records 'A' and 'B'), instead of the normal 'one'. Typically, the details i enter on the main form will be placed into record B and the immunity details on the subform will be placed into record A. I would like the details from both forms to be placed in the same record, ie one record per employee.

The symptom i had described in my first post (whereby the autonumber of each new employee is incremented by 2) is observed when viewing the main form. The field on this form showing the autonumber would display the number assigned to record B.

This is a recent problem and has not always been occuring. Any help would, as always, be greatly appreciated. Thanks. Chris.

[missinglinq]

It wasn't that you didn't explain your problem fully enough, but rather that you didn't explain it at all! Your problem has nothing to do with autonumbers. Having form and subform share a recordsource is the cause of your problem and simply shouldn't be done; your current problem is just beginning, I'm afraid, if you contimue to follow this course of action. When real estate is an issue, i.e. when the form is crowded with controls, the answer is to use tabbed pages. Tabbed pages not only allow you elbow room on you form, but allow you to divide your information in logical groups. In your situation, for instance, one page could hold employees ID/contact data, another could hold educational/training data, and another the employees immunization info.

If you really have a large amount of data and want to do this with subforms, you need to have separate tables for each of the categories above, linked by a common key field, not one common table.

If you decide to use tabbed pages, here's a HowTo Article with some of the salient points.

Linq ;0)>

[cbellew]

Very helpful again! Sorry not to explain it to start with...confusing i can see!

I like the idea of subforms on a form so im going to stick with that. I have managed to separate my large table into 4 smaller tables all linked by a one-to-one relationships.

I now have EmployeeDetails, MRSADetails, DiseaseHistory and NeedleStick.

The common field is the ID autonumber field used in the initial table, changing three of the four new tables with this column as data type 'number'. They appear to link together ok with 'referential integrity', 'cascade update' and 'cacade delete' all enabled.

I have made a form bound to EmployeeDetails and have included the other three as subforms. The subforms appear to link to the main form and show correct information, but when i add a new record i get the error message:

'Index or primary key cannot contain a Null value'

Any ideas what i have done wrong in my table linking? Once again, thank you!!

[NeoPa]

I can't help with this I'm afraid, but assuming Linq comes back to answer, I expect he would find your current linking information of your form and subforms quite helpful. It's hard to point out the problem if it's not shown.
Please consider this as helpful guidance rather than criticism. I just know what it's like from the other side of the fence ;)

[cbellew]

Ok, sure, thanks anyway!

I have a main form titled 'Data Entry Form' with EmployeeDetails as the record source. Inside this form i have three subforms; 'Diseases subform', 'MRSA Subform and 'Needle Stick subform' with respective record sources; DiseaseHistory, MRSADetails and NeedleStick. These three tables are all linked to the EmployeeDetails table via one-to-one relationships (for several reasons). They all have the 'ID' field which is the unique primary key for each table, and as such use this field in the relationships.

The subforms 'Link Child Fields' and 'Link Master Fields' properties are both set to 'ID'.

I hope this is adequate information, any suggestions at all would help me alot.

Thanks again guys! Chris.