Table Trouble? Not Sure.

[Alex2015]

I'm using Access 2010.

Explanation of tables:
tblCACs = the workers

tblConReq (consult requests) = main table that serves to supply a main form
tblConReq_CPRSStaff = serves to supply as a subform to main form
tblConReq_MedRecs = same as above
tblConReq_QualMang = same as above

tblConReqJunction = supposed to show which workers have which consult requests

My relationships = tblConReq has foreign keys for tblConReq_CPRSStaff, tblConReq_MedRecs, & tblConReq_QualMang.
tblConReqJunction has a foreign key for tblCACs & tblConReq.

***
The workflow idea is this: a user has a request & fills out a main form (based on tblConReq). After the request is submitted, three other departments fill in their own piece (subforms based off of tblConReq_CPRSStaff, tblConReq_MedRecs, & tblConReq_QualMang). I need to see which worker is assigned to which request (tblConReqJunction is supposed to tell me this).

I've built the forms & subforms for each piece. The first part of a user submitting a request works, but when I try to edit the record for one of the subforms, I get the following error message:

"You tried to assign the Null value to a variable that is not a Variant data type."

Pressing escape allows the value to be filled in though. Are my relationships broken? Is the fact that a record being created with the three subforms initially blank causing the problem?

 

[plog]

Its usually best to give a non-database overview of what this represents. Pretend its career day and you have to explain to 10 year olds what is it your organization does.

Also, post a screenshot of your relationships. There's a relationship tool in Access, set it up like you described and post the pic.

Last, if tblConReq_CPRSStaff, tblConReq_MedRecs and tblConReq_QualMang have the exact same structures, then yes your tables are improper.

Again, brief, non-database jargon explanation of your organization and a screenshot of relationships.

 

[MarlaC]

Make sure that your subforms either have Master/Child linked field relationships set or default values set for any required fields that you won't have users directly entering values for (foreign keys, etc.). For any fields involved in the master/child linked fields for the subform, you may want to validate that all values are present in the main form before allowing data entry in the subform.

In my experience, it's either failure to include a field in the linked fields between the forms or failure to ensure that the fields in the main forms have all values that will get you this error.