Solved

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

Posted on 2010-11-17
11
1,433 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 84 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 167 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 83 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 167 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 166 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 166 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

622 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