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

sarahellis
sarahellis used Ask the Experts™
on
I have a form that displays an Item.  It has a subform that should show all units with this item, and allow users to add units to the item.  I want the subform to show any records, who's item matches the record that the form is currently displaying.  TblRollout has a unique field "key" which is a combination of the UnitID and ItemID.  

The recordsource for the subform is: "select Unitid, item from tblRollout order by item" It is a dynaset.
I can't find a place to put the code that adds the record without getting the above error.  I've tried in the form Before Update, in the textbox Before Update, and lots of other places.  The record does actually get added to the table, right before the error appears.  I think it has to do with the subform trying to add the record on its own.  I'd really appreciate any help you can offer.  Thank you!

The code that adds the record is:
    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.[UnitID]
        !Key = Trim(Me.UnitID) & "-" & Trim(Forms!frmUpdateTests1.txtTestid)
        !Qty = 1
        !orddate = Date
        .Update
    End With
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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.
Top Expert 2007
Commented:
Each table should have, as its first column, id, an AutoNumber. This column has one purpose: to identify rows within the table.

Composite keys can then be made up of id and any FK (id from a master table.

Doing this frees your data columns to be just that: data. Dates, names, item numbers; all of these may change, but the structure of your DB will not.

If you are using bound forms, you might want to consider un-binding them. You'll have to write the CRUD code yourself, but the benefit is that you will know from where all of the DB activity comes.

Barring that, put a breakpoint in the code and step through it.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Your table's key is fine; an ID column and/or autonumber key is NOT a necessity.

Based on your unfiltered recordsource, my hunch is that you have a bound subform which means twoi updates are occurring:  one from your recordset code and one because the form is bound.  

Without a peek at your forms, it's a little difficult to adise, but I don't think you need that recordset insert at all.  You just want to make sure that the master child link of the subform control references the Item key in the main form and that the UnitID field of the child form is bound to a control source, namey UnitID

FWIW, does this statement beg explanation to you:
!UnitID = Me.[UnitID]

I find it confusing and suggest you do not use the same names for your controls and your columns.  I find this easier to understand and less prone to coding mistakes:

!UnitID = me.[txtUnitID]

Top Expert 2007

Commented:
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.

Author

Commented:
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
Top Expert 2007

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"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

Commented:
>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.














 













Top Expert 2007

Commented:
"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.

Author

Commented:
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!
Top Expert 2007

Commented:
Did you try debugging your code?

Commented:
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?


 

Author

Commented:
@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

Author

Commented:
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.

Commented:
>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.





 

 

 





 


 












 


Top Expert 2007

Commented:
Rest assured, it won't happen again.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

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

mx
Top Expert 2007

Commented:
>>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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial