Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access 2007: Index or primary key cannot contain a Null value

Posted on 2010-11-17
11
Medium Priority
?
1,445 Views
Last Modified: 2012-05-10
I have a Access database with a table that has a primary key. I have a form that dispalys this field, but I have the field read-only. I was hoping to have Access assign a unique number to this field, so the user does not have to enter it in. However, this field is blank and when I save the record Access says, "Index or primary key cannot contain a Null value".  

Can you have a column as a primary key with an auto identity (like SQL Server)? Do I have to write some VBA to populate this field with a unique number for the primary key?  What have others done?

Thanks.

0
Comment
Question by:gtrapp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 18

Accepted Solution

by:
Jerry Miller earned 336 total points
ID: 34161216
Things that I have done over the years:
1) don't use that field on your form, when the records go into the table it will add it if you chose 'auto-number' as the field type.
2) if you use it on the form, take off the read-only attribute and make it 'visible - no'. This provides you access to the field value and keeps your users from seeing / editing it.

Either way you have to set the field as auto-number in the table properties. It really depends on if you need to access the value from the form of if you are using queries to pull this field anywhere else you are needing it.

0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 668 total points
ID: 34161296
"Can you have a column as a primary key with an auto identity "
That is certainly the simplest solution to your situation ... use the Access Auto Number, as mentioned above.  You just have to keep in mind that ... it may not be completely sequential ... gaps may be present for various reasons.  But if that doesn't matter in you case, them you are good to go.  I personally love the Auto Number ... great for all sorts of things ... ID number, Customer Number, Reference Number, etc.

mx
0
 
LVL 20

Assisted Solution

by:clarkscott
clarkscott earned 332 total points
ID: 34163606
If you really need a field to contain specific data, create it and let the user (or code) populate it.  But also, add an autonumber to your table.  This way, you're not breaking any rules and you still have the required field.  Don't lock the field on your form (if users are responsible for entry).  You may want to make the  table field REQUIRED= TRUE
Scott C
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:gtrapp
ID: 34165939
Thanks, great tips. Can you set the auto number to start a value, say 1000?
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 668 total points
ID: 34167034
"Can you set the auto number to start a value, say 1000?"
Using the code below:


** VBA Code method - Example**
Place this code in a standard module. Do not name the module the same as the Function below.

Public Function mResetAutoNumber() As String
    Dim sSQL As String
    Dim lStartVal As Long, lIncrement As Long
    lStartVal = 1000   'change to your desired starting number
    lIncrement = 50    'change to your desired increment
    sSQL = "ALTER TABLE Table4 ALTER COLUMN AN COUNTER (" & lStartVal & ", " & lIncrement & ");"
    CurrentDb.Execute sSQL
    mResetAutoNumber = "Auto Number has been re-numbered"
End Function

The first number (lStartVal) is the starting value, and the second (lIncrement) is the increment.
The example above will start the Auto Number at 1000 and increment by 50.

To run the code one time, open the VBA Immediate Window, and type
this line (including the question mark, followed by Enter:

?Function mResetAutoNumber(<YourStartValueNumber>, <YourIncrementNumber>)
Example:
?Function mResetAutoNumber(500,3)

You can run this against an empty existing table, or to modify the next higher Auto Number value and increment.
It will *not* change any existing auto number values.


mx
0
 
LVL 3

Assisted Solution

by:RCUllrich
RCUllrich earned 664 total points
ID: 34171582
DatabaseMX:
Thanks for the function! If I ever had to reset an autonumber, I used an insert query but I like the function better.
Based upon your Immediate Window comment, you probably wanted to change the function declaration to:
Public Function mResetAutoNumber(ByVal lStartVal as long, lIncrement as long) as String

However, the function is still hardcoded to update an autonumber field named "AN" for a table named "Table4". To make the function flexible, I would add those as arguments to the function and change the declaration statement:
Public Function mResetAutoNumber(ByVal lStartVal as Long,
                                                          ByVal lIncrement as Long,
                                                          ByVal sTableName as String,
                                                          ByVal sFieldName as String) as String
and then adjust the construction of the SQL string statement to use the table name and Field Name parameters.
Thanks
Bob
0
 
LVL 3

Assisted Solution

by:RCUllrich
RCUllrich earned 664 total points
ID: 34171613
I have one more comment about the mResetAutoNumber function. I would add code that would prevent setting the autonumber to anything lower than the current highest value. If the autonumber is set to a lower number, you will eventually get an error when the system tries to use the "Next" autonumber and discovers that it is already in use.
The first step would be get the current maximum value of the autonumber field using DMax() and then exiting the function if the proposed number is lower than the current maximum value with an appropriate message that the "lStartVal" is not allowed for that table.
Bob
0
 

Author Closing Comment

by:gtrapp
ID: 34174171
Thanks, I will try the code.
0
 
LVL 75
ID: 34177376
"Based upon your Immediate Window comment,"
Good catch.  Not sure how that happened.  I will fix that.  I have that version in Notepad, but also in code somewhere.  Must have mixed and match.

thx.mx
0
 
LVL 75
ID: 34181582
OK ...  here is the revised code:

Public Function mResetAutoNumber(lStartVal As Long, lIncrement As Long) As String
   'Example: Set the Starting Value and Increment for a table
   '               named Table1ANTest and an AutoNumber field called AutoNum
    Dim sSQL As String
    sSQL = "ALTER TABLE [Table1ANTest] ALTER COLUMN [AutoNum] COUNTER (" & lStartVal & ", " & lIncrement & ");"
    CurrentDb.Execute sSQL
    mResetAutoNumber = "Auto Number has been re-numbered"
End Function

"I would add code that would prevent setting the autonumber to anything lower than the current highest value."

Of course, that would be a good idea, but for demonstration purposes I think I will keep it simple.

mx
0
 
LVL 75
ID: 34181584
Here is one other method also:

**Data Definition SQL Query method - follow these steps:

1) Open a new query in design view.
2) Menu>>View>>SQL View
3) Enter the following SQL - all on one line:
ALTER TABLE [SomeTableName] ALTER COLUMN [YourAutoCounterFieldName] COUNTER(<YourStartValueNumber>,<YourIncrementNumber>);

<YourIncrementNumber> is optional.  Default is one.

Example:  ALTER TABLE [Table1] ALTER COLUMN [AutoNum] COUNTER(300,5);

4) Menu>>Query>>Run

That's it.

mx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question