Solved

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

Posted on 2010-11-17
11
1,428 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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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 how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

820 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