Solved

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

Posted on 2010-11-17
11
1,418 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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
 

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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now