Link to home
Create AccountLog in
Avatar of pcalabria
pcalabriaFlag for United States of America

asked on

auto number not working in access db

I have an access 365 application with an SQL server backend.

My quotes form is tied to a QUOTES table and the quotenumber field is the sql upsize equlivalent of an auto number field.

Something has happens to the database such that adding a new record to the quotes table docmd.gotorec ,,acRecNew no longer populates the quote field with a new quote number (it used to work)

When I go to the table itself and add a new record the quote field populates as expected

I have done a c&r on the database and a DBC checkdd on the SQL table.. any ideas?
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Well unless there something like a linking issue i would suspect that maybe you have stumbled on a limit...regarding what Access "sees" the Autonumber it is seeing it as Integer...Integer has a limit of 32,767 soon as you reached more Autonumber...on the other hand SQL has more distinct Inger datatypes...but the ODBC driver decides about the "mapping"
Avatar of pcalabria


John.. I don't think so.  Did you see this?
<<When I go to the table itself and add a new record the quote field populates as expected>>

BTW..  I have 359,387 rows with distinct numbers in my auto number field.
Access considers it to be the type LONG but SQL type int
I think I should have a range to more than 2 billion
Any other ideas?

I have 290 controls on the form, and it is used as a sub form.
Can this be it?

The system is now throwing an error at the line: me.quotenumber
Access reports it cannot find the field: quotenumber
I have deleted the field, compacted the database, and then recreated it.  Same error

The actual error now is:

The expression you entered has a field, control, or property name that <myappname> can't find
End or Debug options available.

Debug option takes me to this line:
If Not IsNull(Me.QuoteNumber) And Me.QuoteNumber <> "" Then

Yes, I do have a field called QuoteNumber on this form.

Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
When I go to the table itself  
I thought you were doing it on the SQL
John.. yes, SQL is the BE. I was referring to the link in Access

Dale, that makes complete sense.  Using Access I could generate a new quote then use the quotenumber immediately, in SQL the code that worked before no longer works..  

I've used autonumber to generate my quotenumbers in access since before some of you were born... its worked fine until now... of course, this is an SQL conversion problem...

So Dale's comment completely explains why the quotenumber is not displaying... the solution seems as though it will be to run some code to save the record after I create it to cause the autonumber to populate...  can you, or anyone help?  

Thanks Dale and John... Dale hit the nail on the head.. Access was immediately generating the autonumber field when used as a BE.. when I migrated to SQL the prior code no longer worked... Thanks to Susan Harkins in an article I found online I added the code:
If Me.Dirty Then Me.Dirty = False
immediately after creating the new record and the problem was solved.

Thanks all.