Error 3162

[gregch]

I have an inspection database that I am having problems with. My main (Inspection) form has a subform (Owner). These forms are based on Inspection and Owner tables. The Owner table has a primary key field called Owner ID. The inspection table has a foreign key called Owner ID. This is set up as a one to many relationship.

On my inspection form, the subform (Owner) is linked to the main form (Inspection) by owner id. When I add a new record and try to enter data into the Owner subform, I receive the following error: You tried to assign the null value to a variable that is not a variant data type. Once I exit from this error message, I can finish entering the data and everything is fine. How do I fix this error or trap it so that it does not show up? Any help would be greatly appreciated.

 

[missinglinq]

Which is the 'one' and which is the 'many?'

 

[gregch]

The one would be the Owner table and the many would be the Inspection table.

 

[missinglinq]

That's what I thought, given your stated scenario. Your problem, I think, is that you've got the cart before the horse! In a one-to-many relationship, the 'one' (your Owners) should be displayed in the Main Form, while Inspections, the 'many,' should be in the Subform.

You have to enter the 'one' side, the 'parent Record,' as it were, before you can enter any of the 'many,' or 'child' Records. Switch these tow around, with the link being the OwnerID field, and the Access Gnomes should stop complaining!

Linq ;0)>

 

[VilaRestal]

Or perhaps, if the form is destined to be based on an Inspection then, because each inspection can only have one owner, the owner would be represented by a single simple control - a combobox probably - not a subform.

With that in place, you could in fact keep the owner subform (if you want to be able to view or edit the selected owner's details from the Inspection form). It should just not be a continuous form and not Data Entry and not Allow Deletions. Once an owner is selected in the combobox, the details of that owner would appear in the subform.

 

[gregch]

Thank you for the replies!

I could make the Owner subform the main form and the Inspection form a subform, but I have another subform called contact info that is set up the same way as the Owner subform. The user that I am writing this for wants to be able to enter Owner and contact info right into the Inspection form and have the ability to select an owner from a combo box for owners that have already been entered into the system. Do you have any recommendations on how to tackle this problem? I have the combo boxes set up for the Owners and Contacts. The only problem is entering new data in the Owner and Contact subforms.

Thanks again missinglinq and VilaRestal!

 

[VilaRestal]

I think it would be easiest to have an Add New Owner button that would open a dialog version of the Owner Subform with Data Entry set (or even just an input box that would get the name of the new owner and then create a new record with that name). Once that's closed (hence opening as dialog) set the value of the combobox to the newly entered Owner.

There'd be a bit of code in that but nothing compared to crow-baring the subform into allowing you to do data entry there.

 

[missinglinq]

Or perhaps, if the form is destined to be based on an Inspection then, because each inspection can only have one owner... you could in fact keep the owner subform

Each Inspection has One Owner, but that Owner can have Many Inspections! Per the OP

The one would be the Owner table and the many would be the Inspection table.

This is a classical one-to-many relationship, with 'one' the Owner to 'many' Inspections, so the Main Form simply has to hold the Owner and the Subform the Inspections!

That's how this kind of relationship is displayed; One Owner in the Main Form with Many Inspections in the Subform. You move from OwnerA to OwnerB and all Inspections for OwnerB appear, automatically, in the Subform. This is what the entire concept of Main Form/Subform is all about.

Linq ;0)>

 

[VilaRestal]

But you can reverse it and display the owners details in the inspection form as a subform (display the details of the one as a subform in the form of the many). There's no rule against that and is commonly done and works fine so long as the subform isn't data entry and does not allow additions or deletions.

And to prove it here's a demo:

 

[missinglinq]

addy999's SPAM has been reported to the powers that be!

Linq ;0)>