Thank you for responding. Not having this work is backlogging a large amount of data entry as we are in the middle of our calibration effort for these 1500+ records.
I understand I shouldn't be able to alter the auto-number PK. But something has changed what Access 'thinks' the next auto-number should be. In the table design, that field is a long-integer. How does that procession go from "1378x" to "7831552xx", (which is incremented each time I try to add a new record). Regardless, something is causing the sub to skip over, to jump into the error-trapping code in the sub, and not complete the 2nd query. That 9 digit number is entered into the 2nd table as it's PK/FK, but it isn't appended with the info from the form.
[QUOTE:Why should the number generated by autonumber be less than 15000? Exactly how do you query for the largest value? Record must first be committed to table before the new ID is available[/QUOTE].
Why: the other records in the (first appended) table currently has largest value of 13784 (+/-). all other records added to that table have incremented sequentially. it is the primary key for that table. there is a query written, residing in the nav-panel that finds the largest ID_number in this table, after it is appended, and before the 2nd table is appended with the balance of the information in the data entry form. That queried number is then the Key in the 2nd table that links the info for the individual record between the two tables. It becomes the foreign key, virtually.
Why not use form/subform for data entry?
That is what is being used. The (sub-)form is filled with 18-20 pcs of info, which is appended into the two tables, in two steps. A record is created, (if the asset number we assign to each item is not duplicated; [a bar code # or a tool's mfg ser. number]), and then the (auto-number) Primary key is entered into the 2nd table as its primary key, along with the remainder of the data from the form. Both appends are via append queries, run consecutively in the sub.
Have you revised code or made other changes recently? Have you run Compact & Repair?
Yes. numerous times, (C&R); no, haven't changed code since the last time it worked correctly. I did change the sequence of what happens after the second append query, so I could add duplicate records' data changing the 'bar code number, toool's serial number, etc, retaining other identical info common to the batch of items I'm adding. After the 'commit' command, the sub closed the form. I made some fields blank, and didn't close it. It worked many times over the last 2 yrs. only recently it has stopped. I'm the only one with access to the code. but haven't affected the dB in this function in the last year or more.
If you want to provide db for analysis, follow instructions at bottom of my post