How do I eliminate "Index or Primary Key cannot contain a null value?"

When you use bound forms, you must use the forms OnError event to trap any errors that occur.  The error number is passed to that event and based on that, you can decide what to do about it and if you want the standard Access dialog to appear or not.

JimD.

Oops, scratch that.  You need error handling code in the routine where your adding the record.  Example:


    Dim rs As Recordset
 
    On Error Goto ErrorHandler

    Set rs = CurrentDb.OpenRecordset("tblrollout", dbOpenDynaset)

    With rs
        If rs.RecordCount > 0 Then
          .MoveLast
        End If
        .AddNew
        !Item = Forms!frmUpdateTests1.txtTestid
        !UnitID = Me.[UnitID]
        !Key = Trim(Me.UnitID) & "-" & Trim(Forms!frmUpdateTests1.txtTestid)
        !Qty = 1
        !orddate = Date
        .Update
    End With

Sub_Exit:
    On Error Resume Next

    If not rs is nothing then
      rs.close
      set rs = nothing
   End If

   Exit sub

ErrorHandler:
   If err = xxxx then
      ' do something
      Resume
   Else
     Msgbox "Unexpected error"
     Resume Sub_exit
   End If

JimD.

Sharing names of controls and table columns is fine, there is no need to differentiate them.

Should you happen to view another normalized production DB, you will rarely see keys that have meaning (dates, names, item numbers). Why? Because once issued, the value of such a column cannot be changed without also changing the value in all of the related tables, a daunting, unnecessary task.

Do yourself a favor, and at least consider my suggestion in your next DB design.

Wow, so much to reply to...  Thanks all for your input.

So - barring a discussion on primary keys and naming controls, what is the best way to escape the error?  And in what event should the code be placed?

@JDettman - I've added Error trapping but don't know what to do to escape the error.  I tried Resume, Resume Next, I tried unbinding the form as @Badotz suggested.  Here is where I'm at:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler

   
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("tblrollout", dbOpenDynaset)
    With rs
        If rs.RecordCount > 0 Then
          .MoveLast
        End If
        .AddNew
        !Item = Forms!frmUpdateTests1.txtTestid
        !UnitID = Me.[txtUnitid]
        !Key = Trim(Me.txtUnitid) & "-" & Trim(Forms!frmUpdateTests1.txtTestid)
        !Qty = 1
        !orddate = Date
        .Update
    End With
   
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
   
Exit_ErrorHandler
    Exit Sub
   
ErrorHandler:
    If Err.Number = 3058 Then
        Resume Next
    Else
        MsgBox Err.Description
        Resume Exit_cmdClose_Click
    End If

End Sub

In the code editor, place the cursor on

With rs

and press F9. This will set a breakpoint, and the debugger will stop there when it reaches that line.

Run your form, and when the debugger kicks in, you can press the F8 key to step through the code.

"what to do to escape the error. "
You can't.  You have to fixed the problem.  And that error is not rendered in the Form Before Update event, but instead in the Form Error event - as are several others (but not all) - as noted above.

The error message you are getting:
"Index or Primary Key cannot contain a null value?"
... is very specific and means just what it says.

mx

>Sharing names of controls and table columns is fine, there is no need to differentiate them.

If, and only if, they are bound to one another.  Otherwise, sharing names leads to unnecessary programming mistakes,  maybe even the one that is introducing nulls in this case, (though, I doubt it).  

To illustrate: if me.UnitID references a column in the form's underlying recordset and a textbox by the same name is subsequently added, then me.UnitID suddenly references the textbox value and any coding depended on the original reference will break.  In other words, the reference is ambiguous.  It's like naming every male in the family "Don". If there is only one Don in the room, nobody gets confused, but as soon as the second one walks into the room we need to be more careful.   Often the ambiguity is masked because the control is bound to the column by the same name.  But if not, look out...

>Should you happen to view another normalized production DB, you will rarely see keys that have meaning (dates, names, item numbers).

Are you kidding me!  First, let's be clear--this assertion is not relevant to the opening question. Second, I don't think that's really true, but I will say that object-\ orientation has fueled an unfortunate trend in that direction. And Microsoft's persuasion has been one of the worst offenders.

Surrogate keys have their place, for sure--mainly for technical or performance reasons. But even if we embrace the knee jerk approach of adding such key to every table, that does not mean keys on "meaningful data" should go away. Keys represent business constraints and those don't evaporate because the table designer decides to add an autonumber column.

I've worked on this forum for some time and by far the most prevalent dbms question involves getting rid of "duplicates".  There's already a unique identifier, so really, we have no duplicates, right?  Think again.














 

"knee-jerk" approach? Really?

Before we both burst into flame, my point is that changing the value of a FK that has meaning can be an arduous task, because of relationship(s) to other table(s).

I have no problem with indexing meaningful columns for efficiency, but I am not likely to change my position regarding the grief such columns introduce when used as FK.

Thank you Dgmg.   I appreciate sticking to the question at hand, because there are as many opinions about best practices as there are programmers.  

I still didn't get an answer that was complete enough for me to replicate and get working.  But, you did give me some clues.

What I ended up doing is leaving the subform bound, created invisible fields that represent tblrollout.key, tblrollout.qty, tblrollout.orddate.  Then on the on change event of txtunitid, I ran my code, populating these other fields.  That way, when I leave my record, the record has only been added once, and most importantly, the key has already been populated.  It's not ideal.  But, if anyone has any better, and complete ideas, I'd love to hear them.

Thanks so much for your time today!

Did you try debugging your code?

sarahellis,
You are getting close.  My question is about the invisible fields, that I presume you are using to populate the key, qty, and orddate before you leave the record (form's before update event?)    I don't think they are necessary.  How are those invisible fields populated?


 

@Badotz - yes.  It would run through all my code fine but, as soon as it finished, I'd get the error.  It
actually WAS because, as dgmg said, " twoi updates are occurring:  one from your recordset code and one because the form is bound.  "  So, I figured I either had to unbind the form, which I tried, and failed miserably - or make the form complete the required fields prior to updating.  It was just the best I could figure out.

@dgmg - What the table wants, is the "key", much like Timothy Leary.  ; )

Private Sub txtUnitID_LostFocus()
On Error GoTo Err_txtUnitID_LostFocus

    If Me.Dirty Then
        If txtUnitid > 0 Then
                txtItem = Forms!frmUpdateTests1.txtTestid
                txtKey = Trim(Me.txtUnitid) & "-" & Trim(Forms!frmUpdateTests1.txtTestid)
                txtQty = 1
                txtOrdDate = Date
        End If
    End If

Exit_txtUnitID_LostFocus:
    Exit Sub
Err_txtUnitID_LostFocus:
        MsgBox Err.Description
        Resume Exit_txtUnitID_LostFocus
   
End Sub

I appreciate advice when the question is addressed specifically, rather than an unwanted critique of my preferences in coding.  I appreciate that these people may volunteer their time, but while two Experts are arguing over irrelevant issues, my client's clock is ticking.   It's unprofessional.  That's fine on free sites. I expect more from a site that I have to pay for.

>Before we both burst into flame,

I'm OK.  As long as these debates don't get personal, I think they are healthy. I'm here to learn as well as expouse my views.  My only regret is cluttering up the thread with side-issues that, while important to us, aren't germaine to the question.  

>Knee jerk approach

No apologies for that.  I've seen many designs, including Microsoft tutorials, that promote surrogate keys on every table regardless of the business considerations.  And NEVER do they address the loss of integrity when the natural business keys are demoted.  

My position is that a natural key supporting the business integrity of the data is more important than a surrogate key for programming convenience.  Additionally, they are not mutually exclusive as the "knee jerk" methodology portends.

>I have no problem with indexing meaningful columns for efficiency, but I am not likely to change my position regarding the grief such columns introduce when used as FK.

My cause was not about indexing meaningful columns for effiency, it was about leaving  keys on meaningful columns for data integrity.

As for the grief, I am somewhat sympathetic.  But most of that originates from poor (or absent) analysis during key selection in the first place.  I don't think the convenience of a surrogate key is a good reason to short change that analysis.  One really cannot normalize without understanding each and every key and their functional dependencies. Throwing surrogate keys in the mix makes it more complicated, not less.

Purests contend that a good natural key never changes.  I'm not sure I can go quite that far, but I do believe we should aspire to keys that don't change and processes that don't change them.  I am not opposed to using "cascade update" and I am not opposed to introducing an immutable key when one is needed to better support the business,  But I consider that discovery of a "missing identifier": a key that the business needs, but did not formerly recognize.  That's conceptually and culturally a world of difference from a surrogate key which, by definition, has no business meaning.

Finally, I'm not opposed to surrogate keys either, when they make sense and do not compromise the data integrity.





 

 

 





 


 












 

Rest assured, it won't happen again.

I have rarely seen the need for anything other than the Access AutoCounter for use as a Primary Key.

mx

>>I appreciate advice when the question is addressed specifically, rather than an unwanted critique of my preferences in coding.  

Fair enough.

>>while two Experts are arguing over irrelevant issues, my client's clock is ticking

Part of the results you get are discussions of practices. This is not a bad thing, and quite possibly clicks the lightbulb in the Asker's head.

>>It's unprofessional

I what way? While we strayed a bit from your original question, the discussion about how-to and why-should-you were germane to the topic. Forgive me if I point out what I believe to be issues with a DB.

>>I expect more from a site that I have to pay for.

Well, there's the rub - typically, when expectations are not met, there is conflict. Since only you know what your expectations are, what did you expect ;-)

Seriously, we are not robots; emotions are involved. Sometimes questions seem to go awry, but the end result is often more enlightening than the solution to the asked question.

I suggested stepping through the code because that is always where I start.