By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,761 Members | 1,879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,761 IT Pros & Developers. It's quick & easy.

Insert into contains autonumber field that is primary key

P: 2
I have seen a number of responses to other questions that indicate I should be able to do the following to insert a new record into an Access 2007 database and have my primary key autonumber field populated automatically:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO D_INVOICE (Org_Code, Prog_Code, [Year], Balance, Base_Rate, Affiliate_Rate, Cost_Per_Intern, Number_of_Interns, Date_of_Invoice, Date_Due, Date_Paid, Amount_Paid, Credit_Offset, Entered_By )
  2. SELECT Org_Code, Prog_Code, Year, Balance, Base_Rate, Affiliate_Rate, Cost_Per_Intern, Number_of_Interns, Date_of_Invoice, Date_Due, "" AS Date_Paid, "" AS Amount_Paid, Credit_Offset, [Entered By] AS Entered_By
  3. FROM q_org_invoicing_records;
What actually happens is that a record is inserted into my table, but the primary key (which is autonumbered) is left blank. Can anyone explain why this might be happening. The primary key is not contained in the insert.
Mar 14 '12 #1

✓ answered by jac1

I have figured out the problem, although I still don't understand the behavior. The field "" as Amount_Paid was actually incompatible because Amount_Paid is configured as a number. I changed it to 0 and the autonumbering is now working as expected.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,489
I would suggest checking the design again carefully. An AutoNumber field should work exactly as you expected it to.
Mar 15 '12 #2

P: 2
I have figured out the problem, although I still don't understand the behavior. The field "" as Amount_Paid was actually incompatible because Amount_Paid is configured as a number. I changed it to 0 and the autonumbering is now working as expected.
Mar 15 '12 #3

NeoPa
Expert Mod 15k+
P: 31,489
jac1:
The field "" as Amount_Paid was actually incompatible because Amount_Paid is configured as a number.
I assume you mean (value "" of). If so, then that certainly makes sense.
Mar 15 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.