asked on

Error 3058: Index or primary key cannot contain a Null value

I have a simple database where I keep information about marketing prospects. Each prospect has a Company_ID that is auto-generated. I have a form to add a new company.

Private Sub AddRecord_Click()
    Dim dbProspects As DAO.Database
    Dim rstCompany_Names As DAO.Recordset
    Dim rstCompany_Info As DAO.Recordset
    
   Set dbProspects = CurrentDb
   
'Enter New Company Name
   Set rstCompany_Names = dbProspects.OpenRecordset("Company_Names")
   rstCompany_Names.AddNew
   rstCompany_Names("Company_Name").Value = Name_to_Add
   rstCompany_Names.Update

Open in new window

Everything was working fine. Then, I needed to add a list of new companies so I appended the list to the end of the table along with the Company_IDs for these new names. Now, I can't add a new company using the form because I am getting "Error 3058: Index or primary key cannot contain a Null value".

I assume this problem is related to the fact that I added those records and now that field is not categorized as an auto-generated field. How do I get it to go back to auto-generating the Company_ID? Alternatively, I suppose I could do a DMax and increment to generate my own Company_IDs but would prefer the auto-generation.

Thanks for any help!

- Susan

clarkscott

Is your primary key an "autonumber" or do you manually enter it?
Scott C

kobys

ASKER

I imported the table of Company_ID and Company_Name from Excel. Tried changing the data type in Design View to "autonumber" but you can't change the data type.

ASKER CERTIFIED SOLUTION

clarkscott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kobys

ASKER

Thanks Scott.

I ended up doing my own auto increment

MaxCoID = DMax("[Company_ID]", "Company_Names")
MaxCoID = MaxCoID + 1
rstCompany_Names("Company_ID").Value = MaxCoID

Cheers, Susan