Index or Primary Key - Null Value

[caferacer]

Hi All,

I am running up against an error ‘Index or Primary Key Cannot Contain Null Value’. In the dialog box it doesn’t give the RTE number just the description, OK & Help buttons. After some searching the description matches RTE 3058.

I have identified why it’s happening and through a process of elimination it’s happening in the Before Update event of a subform. I have tried to catch it in an error handler so I can have a bespoke dialog box to inform the user exactly why it’s happened and then undo, but it just goes straight to the dialog box.

Any ideas how I can catch this?

Thanks

Mark

Access 2010.

 

[pbaldy]

Would this work for you?

http://www.baldyweb.com/BeforeUpdate.htm

 

[gemma-the-husky]

if you have a form/subform with the links between the two forms set correctly in the form design - then the fk part of the subform should be set to correspond with the main form wen the subform record saves.

If the link either cannot be set in this way, or is wrongly declared, then you will probably get this error, and you probably need to set it manually (the before update event is probably best)

 

[The_Doc_Man]

I agree with the other comments. I'll say it differently. If you have a true parent-child table relationship with the main form as the parent and the sub-form based on the child table, you must store something in the parent FIRST before you attempt to do something in the child table in the sub-form. You cannot create a record in a child table if there is no corresponding record in the parent table. If the form and sub-form parallel the parent-child table relationship, they are simply telling you that you are doing something in the wrong order.

 

[caferacer]

Hi All,

Yes, you are all correct, thats exactly what's happening. The code that pbaldy provided has enabled me to flag to the user this problem through a bespoke message box and improved the situation dramatically. It has now eliminated the standard Access dialog box I described. SO many thanks for that.

What has appeared though, in its place, is another Access dialog box saying "you tried to assign a null value to a variable that is not a Variant data type" (RTE 3162). Again no debug option, once you click okay, my new bespoke message box pops up immediately and so isn't a major problem (I hope). I have tried to pinpoint what event this new message is being generated from, but no luck.

I do believe its connected with the same issue.

Any ideas?

edited adding "Variant".

 

[spikepl]

"you tried to assign a null value to a variable that is not a data type" is nonsense and cannot possibly have been generated by Access, so please paste the actual error message.

 

[caferacer]

Apologies missed out 'variant'.

you tried to assign a null value to a variable that is not a Variant data type.

 

[pbaldy]

What is your code exactly? If you have error handling you won't get the debug option. If that's the case, temporarily comment out the On Error line and see what happens.

 

[caferacer]

Hi,

I found this, which I have used and solves the problem.

http://allenbrowne.com/ser-31.html (Subform records with no main form record).

Thanks all for taking the time and offering help.

Regards