Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1462
  • Last Modified:

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

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
gtrapp
Asked:
gtrapp
  • 5
  • 2
  • 2
  • +2
6 Solutions
 
Jerry MillerCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
clarkscottCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
gtrappAuthor Commented:
Thanks, great tips. Can you set the auto number to start a value, say 1000?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
RCUllrichCommented:
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
 
RCUllrichCommented:
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
 
gtrappAuthor Commented:
Thanks, I will try the code.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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.

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now