Avatar of pcalabria
Flag 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?
Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon
John Tsioumpris

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 field...like it is seeing it as Integer...Integer has a limit of 32,767 ...as soon as you reached it...no more Autonumber...on the other hand SQL has more distinct Inger datatypes...but the ODBC driver decides about the "mapping"

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

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.

Dale Fye

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

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?  

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

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.