Autonumber in 'add record via SQL append qry is out of range expected

[hammerman]

Hi, I have a new problem concerning the same dB as my last post.
Summary: instrumentation dB; 2 predominant tables; new records are added by entering necessary info on a form, a query takes some of that and first verifies the instrument asset # is not a duplicate; then updates one of the two tables. In the same subroutine, it then queries for the largest primary key in the first appended table. This number retrieved 'should be less than 15000, but when a new record is added the number seen in that ID field is 9 digits big.
The sub then finds that number and uses it as the 'foreign key for the other table finish appending the form submitted info.
I've used this dB and form many times over the last 2 yrs, and had not problems. over the last 2-3 days, besides that huge number in the ID field, I get an "overflow' error, and the information that should be appended to the second table is not entered.
diagnosis: first table ID is auto-number as type 'long integer'. I can't change this to be just an integer or any other number type. This is where the error starts, since the 2nd table isn't written to. (the same ID is entered in both tables as that table's ID field, working right or not.)


My question: IS there a hidden table that lists the largest auto number used? How do I bring what is entered as the next auto-number for a new record to the next consecutive number? or if not possible, what's the solution?
thank you for your patience. If you need more info or a sample, I'm not sure what you need, so ask for specific parts.

[June7]

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.

Why not use form/subform for data entry?

Have you revised code or made other changes recently? Have you run Compact & Repair?


If you want to provide db for analysis, follow instructions at bottom of my post.

[ssanfu]

I think you might be misunderstanding what the autonumber field type purpose is and how to use it.

AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key. You cannot control what number is generated. You should never display the autonumber.

See
Autonumbers--What they are NOT and What They Are
Microsoft Access Tables: Primary Key Tips and Techniques
Use Autonumbers properly

....a query takes some of that and first verifies the instrument asset # is not a duplicate....

You could set a unique index on that field that would not let a duplicate value to be entered.
( I really hope you do not have field names like "asset #")



I would suggest using a main from/sub form to enter your data...........


Good luck with your project.........

[hammerman]

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

[hammerman]

I think you might be misunderstanding what the autonumber field type purpose is and how to use it.

• I think I do understand. I just am seeing an inconsistency between past performance of the sub and what I'm seeing now. The auto-number may not be culprit. it is the most visible symptom.
  
• Also, this project pre-dates me, and my 'newbie' experience with Access, VBA, etc. I have made some inroads into understanding what this application is doing, and have supplemented its core objectives to give me and my manager better, more complete, data for tracking instrument usage for annual UL audits. but this problem is above my skill level.
  
• That auto-number/PK field does have the index property set.
  
• We do use the form/subform schema. No, Asset # is not the field name. This was written by a 'professional', (the intricacies of data management, coding structure, menu and 'switchboard' structure, error-handling; all point to that!). So I would not expect it to be a 'foundational/code-structure' problem. This subroutine has worked flawlessly for > 6 yrs, possibly > 10!

So, looking at it from a different angle; what causes an 'overflow' error? I'm assuming some data (field entry) can't fit in it's 'container'. But which?

Would I be pre-emptive if I copied all records from each table into new tables, with exact same field names and structure, only allowing Access to re-initialize the auto-number sequence? I could write a sub that could do that, (I think), {use the query that 'joins' the two tables into a datasheet view, and copy each field back into the corresponding new tables, twins of the original. Would that re-create the problem, though?}


AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key. You cannot control what number is generated. You should never display the autonumber.

See
Autonumbers--What they are NOT and What They Are
Microsoft Access Tables: Primary Key Tips and Techniques
Use Autonumbers properly




You could set a unique index on that field that would not let a duplicate value to be entered.
( I really hope you do not have field names like "asset #")



I would suggest using a main from/sub form to enter your data...........


Good luck with your project.........[/QUOTE]

[Minty]

PMFJI - The overflow is probably because the FK in the second table is set to an integer value, that all the time your PK value is below 32768 (the maximum for an Integer) won't be a problem.
As soon as your PK value exceeds that (and 7831552xx definitely does!) you'll get an overflow if that value is attempted to be inserted into the integer field.

Even if it's simply Dim as an Integer with the VBA doing the moving around it will error out with an overflow.

[hammerman]

PMFJI - The overflow is probably because the FK in the second table is set to an integer value, that all the time your PK value is below 32768 (the maximum for an Integer) won't be a problem.
As soon as your PK value exceeds that (and 7831552xx definitely does!) you'll get an overflow if that value is attempted to be inserted into the integer field.

Even if it's simply Dim as an Integer with the VBA doing the moving around it will error out with an overflow.

Table design: table one: PK = auto-number, and is long integer; table 2: PK is integer; even still. somehow the pk in table one, (generated by appending data to this table first!) becomes a number that the type has not changed; it has always been long_int as Auto-Number format. Table two PK is not Auto-Number, and only is Integer; and is 'copied' from table 1, in second query. Problem still seems to be in table 1 generating an incremented auto-number that somehow went from the range of '1378_', to something in the 9 digit number range. Maybe I'm being anal, and maybe the problem will go away if I change the table 2 pk to a long integer; maybe it's the copying any long int into an ('short') int field type! I do not know. but I didn't change anything before the problem showed up. The illogical PK number in table one is created first. Shouldn't it be an incremented number from that tables previous highest PK (auto-) number? For every other record I've added to the table previously, (and there have been more than 10 dozen additions over the course of the year as I bought new instruments and added them into the dB), I've not seen this problem before. Why now? (Yes I did change the code, but that was long before the purchase activity this year. Problems don't lie dormant, then pop up unannounced. Do they?

I appreciate all the response, and the help. But they seem to just scratch the surface that I've already fumed over, and tried to discern the what and why for the last 2-3 days.

[Gicu]

Try to get all users out and compact and repair the back-end which should reset your autonumber(s). And while you have exclusive control to the back-end change the data type in the second table to long integer to match the first.

[June7]

And if still need help after that, consider providing the db so we can analyze it directly.

[hammerman]

Try to get all users out and compact and repair the back-end which should reset your autonumber(s). And while you have exclusive control to the back-end change the data type in the second table to long integer to match the first.

Unfortunately, (or fortunately?), this is not a split dB. it resides on one PC alone. Pc is on a corporate network, a large network.
I will change the data type in table 2. however, as mentioned above, the error seems to occur while writing to table 1. stay tuned.

Nope. changed the data type to long int, tried to add a record, and got the same error. So, something else is the issue. (Arrrghh!)

[Gicu]

Did you compact to reset the autonumber? I suggest you import the first table into a brand new Access file on your local drive, open the table and sort descending by the autonumber field to inspect if there are any stray records with out of range large numbers, close the table, run a compact and repair on the file then import this table back into the original. You can rename the old one first to have it as a backup.
Let us know.
Cheers,
Vlad

[hammerman]

Did you compact to reset the autonumber? I suggest you import the first table into a brand new Access file on your local drive, open the table and sort descending by the autonumber field to inspect if there are any stray records with out of range large numbers, close the table, run a compact and repair on the file then import this table back into the original. You can rename the old one first to have it as a backup.
Let us know.
Cheers,
Vlad

Wonderful suggestion. Sounds to be the simplest fix. Thank you and will let you know how that works before next weekend. Holidays' time off much needed.
whew!

[hammerman]

Did you compact to reset the autonumber? I suggest you import the first table into a brand new Access file on your local drive, open the table and sort descending by the autonumber field to inspect if there are any stray records with out of range large numbers, close the table, run a compact and repair on the file then import this table back into the original. You can rename the old one first to have it as a backup.
Let us know.
Cheers,
Vlad

After mulling this over, during the weekend and holiday, a quick question arises. Both of the two tables are linked by the auto-number in the first table being written as the foreign key in the second table. Would re-setting the auto number in the first table, by the method you described, guarantee the 2nd table would be aligned? I can't only do the one, I'm sure. But would the 2nd retain the same relationship, record for record, between the tables? This is critical because the records are not all consecutively numbered; some records have been deleted so there are gaps in the number sequences. If there should be a glitch, such that record 874 in the first table, (old number), becomes record 587, re-ordered, while record 874 in the 2nd table doesn't become record 587, how could that be mitigated, prevented from happening?

[June7]

Just running compact and repair should not change numbers, it should just reset the autonumber seed in each table for generating the next key value. As Vlad suggests, sort the table to find the largest existing value. Is it 32768? However, if you have changed the foreign key to long integer, this should no longer be an issue. So not sure C&R will fix issue but it shouldn't hurt.

[Gicu]

As June said doing a compact and repair will not affect your existing records, it will simply reset the next value of the autonumber. There is a small chance of you running into issues only if you deleted the LAST record in table 1 (the one with the autonumber) and left the orphan record in table 2, as that orphan record could get "re-attached" to a brand new record after compact and repair, but hopefully that was not the case. Make backup copies of the back-end before doing it and you should be fine. If you still have problems consider uploading a sample db with just the two empty tables and the code/ queries used to create the pair of records.

Cheers,
Vlad