Error Message - "You can not add or change a record..."

[Chezza]

Hi All,

Similar to many other posters, I don’t have a lot of experience with access (I'm using access 2003) and know little of VB, although I have picked up a bit while working on this project.

Here’s the set up. We receive many “little” shipments of four different versions of something. We combine these into one “big” shipment which we send out to be processed and want a total of each version in the big shipment.

Currently I have one main form for the big shipments, based off of the “big shipment” table and a subform, based off of a query. In the subform, the user selects the little shipments to be included in the big shipment from a combo box. Then the number of each version, for each little shipment, auto-fill and I get a grand total of each. I had everything working fine. I closed the program, without, I thought, making any changes (although obviously I did and saved them) shut down the computer, came back to work the next day, and when I selected a shipment from the combo box, I got the message

“You can not add or change a record because a related record is required in table ‘Big Shipments.’”

I’ve scoured my work and can’t figure out the problem, although I am sure it’s staring me in the face. None of my variables have a default of 0 and the link between the subform and main form is “RichardID.” Below are my tables and the query I used. There is a 1 to many relationship between both “little shipments” and “big shipments” and the table “line Items.” FYI- there are more tables, these are just the ones on this form.

Table 1 –Little Shipments
ShipmentID (key)
CtyShipID
CountyCode
CountyShipdate
RecievedDate
CHAs
NCHAs
OldCHAS
Refs

Table 2 – Big Shipments
RichardID (key)
TrackingNum
SentDate

Table 3 – Line Items
RichardID
ShipmentID

Query Shipment details:
SELECT [Line Items].ShipmentID, [Line Items].RichardID, [Little Shipments].CHAs, [Little Shipments].NCHAs, [Little Shipments].Refs, [Little Shipments].OldCHAs FROM [Little Shipments] RIGHT JOIN [Line Items] ON [Little Shipments].ShipmentID=[Line Items]. ShipmentID;

If you need more information, please tell me. I can’t think of anything else that might be relevant. Is my set-up correct (I wasn't sure about a line items table, but couldn't figure out any other way to set this up without repeating variables)? My query, a setting I have set to yes instead of no?

Thanks in advance for any help.

-Chezza

[ADezii]

Please define your Relationships between the Tables and the Key(s) involved.

[NeoPa]

Nicely laid out question Chezza. The one piece of info that is critical and missing though, is what ADezii asked for in his post.
Have you used the Relationships window to define links between tables?
We need to know the precise details of that.

[Chezza]

Hi,

Sorry about the dely in responding, I was pulled away from work earlier than expected.

After taking another look at the relationship table, I de-checked the referential integrity on both relationships (described below) and now it works perfectly. My question now, is why? I thought RF was supposed to insure I didn't enter a value in to a foreign key unless it existed as a primary key in another table.



Relationship Info:
There is a one to many relationship between "little shipments" and "line items" - Shipment ID is the key in "little shipments" and the FK in "line items."

The exact same thing is set up for "big shipments" and "line items".

I did forget include a column when I typed out the tables. "Line items" has an autonumber for a PK.

Thanks so much for pointing me in the right direction!

[ADezii]

Hi,

Sorry about the delay in responding, I was pulled away from work earlier than expected.

After taking another look at the relationship table, Ide-checked the referential integrity on both relationships (described below) and now it works perfectly. My question now, is why? I thought RF was supposed to insure I didn't enter a value in to a foreign key unless it existed as a primary key in another table.



Relationship Info:
There is a one to many relationship between "little shipments" and "line items" - Shipment ID is the key in "little shipments" and the FK in "line items."

The exact same thing is set up for "big shipments" and "line items".

I did forget include a column when I typed out the tables. "Line items" has an autonumber for a PK.

Thanks so much for pointing me in the right direction!

After taking another look at the relationship table, Ide-checked the referential integrity on both relationships (described below) and now it works perfectly. My question now, is why? I thought RF was supposed to insure I didn't enter a value in to a foreign key unless it existed as a primary key in another table.

It's exactly as you describe in that RI ensures that there can be no 'Orphaned' Records, namely a Record in a Child Table that has no matching Record in a Parent Table via the Primary Key(s) link. I'm not sure as to the 'why' but you may have solved a problem while opening the door to another. As it stands now, you can enter a Line Items Record with no corresponding Record (Parent) in Little Shipments. See what I mean?

[Chezza]

Yes, I completely understand what you are saying.

I was going to say, it doesn't matter because the only way one can enter information into the line items table is from the combo box on the form. But I was irked that I would then have a DB set up poorly. I rebuilt it anew, and it works perfectly, even with the RI. This was my first attempt at a DB in 5-6 years, so I made many mistakes during the building process and probably tweaked something on accident while learning.

Thanks for your help.

[ADezii]

Yes, I completely understand what you are saying.

I was going to say, it doesn't matter because the only way one can enter information into the line items table is from the combo box on the form. But I was irked that I would then have a DB set up poorly. I rebuilt it anew, and it works perfectly, even with the RI. This was my first attempt at a DB in 5-6 years, so I made many mistakes during the building process and probably tweaked something on accident while learning.

Thanks for your help.

It's all part of the learning process.