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?
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
Last Comment
pcalabria
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"
ASKER
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?
<<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?
ASKER
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
When I go to the table itselfI thought you were doing it on the SQL
ASKER
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?
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?
ASKER
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.
If Me.Dirty Then Me.Dirty = False
immediately after creating the new record and the problem was solved.
Thanks all.