related record required

[FastLearnerIThink]

Hello, newbie here. First, would like to say that this forum is very useful and just by visiting as a guest, I have improved my knowledge of programming in Access greatly. Thank you all for sharing the knowledge.

I am developing an inventory type database. I have a form with a subform for receiving property. Main form, depicting user name and date of report, etc. works fine and I have managed to make everything in the main form work just the way I want it successfully using strictly VBA for the most part.

All table relationships seem to be accurate. But for some reason when I make an entry into the subform and try to move to next subform record prior to filling all the fields (testing purposes), I get the following error if certain specific fields are left null.

"You cannot add or change a record because a related record is required in table TblName."

Searched on Google and only thing I find is that the table of the field (lookup field) is indexed. I have tried removing the indexes but still same result.

Any help in resolving these annoying messages will be appreciated. Thanks!

 

[Eupher]

If you have enabled Referential Integrity in your table relationship, you cannot enter data in the child table's record until a record has been created in the parent table. Take a look at your table structure and relationships. If a field is the primary key in a relationship with referential integrity, data must be entered before you can enter a new record in the foreign key table.

Ken S.

 

[Remou]

Hi
I think this message usually means what it says. Let's say you have two tables, the table for your sub form (let's call it tblSub) and another table (tblOther). These two tables are related by a key (ID) and referential integrity is enforced. tblOther looks like this:
[tt]ID Desc
1 Blah
2 More Blah[/tt]
If I now try to add a record to tblSub:
[tt]ID OtherFld
3 Jam[/tt]

You will get the error in your post, because tblOther does not have an ID of 3. Phew! A long winded explanation!

 

[FastLearnerIThink]

Okay, trying to grasp what you mean, but not quite. Perhaps a bit too technical for me. Anyway, here is part of the table structure of my project.

tblJobRep (Main Form)
---------
JobRepID (PK)
fldJobNoID (Form control number)
fldEmplID (Employee's Name)

tblJobRepDet (Sub Form)
---------
JobRepDetID (PK)
fldJobRepID (FK: Links to tblJobRep's PK)
fldCondCodeID (Lookup Field to tblCondCode)

tblCond (table for Lookup)
---------
CondCodeID (PK)
fldCondCodeID (Links to tblJobRepDet CondCodeID)

Maybe you have better suggestions? Thanks again.

 

[Eupher]

What are the defined relationships between these tables?

Ken S.

 

[FastLearnerIThink]

Referential integrity enforced. This what you mean?

 

[FastLearnerIThink]

Eupher

Still can't figure it out. My problem is not being unable to enter data. Problem is if I enter data on the subform, I cannot leave specific fields null, and therefore will not allow me to save record. None of which have a Required property of yes in table. Seems to be happening specifically on combo fields with table/query select properties.

All tables related with Referential integrity enforced and a 1 to many relation.

tblJobRep(1) related to tblJobRepDet(many)
tblCondCode(1) related to tblJobRepDet(many)

MainForm recordsource = tblJobRep
SubForm recordsource = tblJobRepDet
cboCondCode (looks up value, limited to list with no option to append, from tblCondCode)

For each jobrepdet record there can only be one condcode, but for each condcode, there can be many jobrepid records. Likewise, for each jobrep record, there can be many jobrepdet records, but for each jobrepdet record, there can only be one jobrep record.

Remou

I believe what you are telling me is very precise, but unfortunately, cannot grasp what your telling me.

Really appreciate everyone's input on this.

 

[PHV]

I don't understand the meaning of
fldCondCodeID (Links to tblJobRepDet CondCodeID)
in tblCond.
I guess the schema should be:
tblJobRepDet
---------
JobRepDetID (PK)
fldJobRepID (FK: Links to tblJobRep's PK)
fldCondCodeID (FK: Links to tblCond's PK)

tblCond (table for Lookup)
---------
CondCodeID (PK)
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FastLearnerIThink]

PHV

You are right about the schema. Thanks for accurate clarification. Any idea how to resolve my problem, though?

Thanks.

 

[PHV]

You may consider create a "Null" condcode in tblCond which PK is the default value of the corresponding FK field in tblJobRepDet.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[FastLearnerIThink]

PHV
What I did.

On subForm Load, gave fldCondCode value of "". Repeated to all other problem fields. Problem went away, sort of. However, values entered in subform fields were remaining even when changing main form records, which led me to the real problem.

Problem: FK fldJobRepID in tblJobRepDetID was set to Not Required in table.

Fixed that, now need to force this to happen in VBA.

Will see if I can figure this out on on my own and let you know.

Thank you all for your valuable information and outstanding support.

-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno

 

[FastLearnerIThink]

Okay, Getting more and more puzzled here. And no end to my problem in sight.

Removed all the Ctrls = Null lines from subForm Load Event. Set the Master/Child links accordingly in subform control properties.

Removed the required property for FK in tblJobRepID to get rid of MS Access "Required Field" message when trying to click on the subform and MainForm is still blank (have other controls in place to prevent orphan records).

Error Message is back. This time, they appear when I click on the subform. Added Me.cboCondCode = Null in subForm's Load Event and the error message is gone. However, if I try to move to next record with fldCondCode null, error message comes back and cannot proceed to next record or save without making a selection.

Just what in hell am I missing here? What am I overlooking?


-- Fast Learner, I Think

Here's something to think about. How come you never see a headline like 'Psychic Wins Lottery!'? - Jay Leno