cannot add or change a record because a related record is required

[Knowlton]

I have a subform with a combo box that will not add a record. The form's record source is tblRetreadDetails.

tblRetreadDetails structure:
RetreadDetailsID     Autonumber, Primary key
RetreadID            Number, foreign key to tblRetreads
TireID               Number, foreign key to Tires table
RetreadOptionsID     Number, foreign key to tblRetreadOptions

tblRetreadOptions structure:
RetreadOptionsID
Brand
Tread
Style
TreadDepth

The relationship is one(tblRetreadOptions) to many(tblRetreadDetails).

The combo's control source is RetreadOptionsID.
The combo's rowsource is:

SELECT DISTINCT tblRetreadOptions.RetreadOptionsID
   , tblRetreadOptions.Tread
   , tblRetreadOptions.Brand
   , tblRetreadOptions.Style
   , tblRetreadOptions.TreadDepth
FROM tblRetreadOptions
WHERE (((tblRetreadOptions.Brand)
   =[forms]![frmRetreads]
      ![frmRetreadDetails].[form]
      ![cboRetreadBrand]))
ORDER BY tblRetreadOptions.Tread;

When trying to add a record I get an error message:
"You cannot add or change a record because a related record is required in tblRetreadOptions".

The record is in the table or it would not be an option in the combo. What am I missing?
Thanks!

[zmbd]

What this means is that in the record you are editing the field [tblRetreadDetails ]![RetreadOptionsID] either doesn't have a value set or the value you have selected is not a valid. Given that only the valid values are showing then it's a setting in the control - most likely the bound column.

Go back into your CBO and make sure the BOUND column is set for the column that contains the [tblRetreadOptions]![RetreadOptionsID]

[Knowlton]

The bound column is 1. Column count is 5. Column widths are 0";1";1";0";0". I'm using columns 4 and 5 to populate other controls AfterUpdate. Limit To List is set to yes.

I'm unable to see anything different in the settings for this one than in numerous others I have.

[zmbd]

Please check:

[tblRetreadDetails]![RetreadOptionsID]
Number, foreign key to tblRetreadOptions

verify this in database tools, table relationships

The combo's control source is
RetreadOptionsID.

verify that this is [tblRetreadDetails]![RetreadOptionsID]
and NOT [tblRetreadOptions]![RetreadOptionsID]
(I tend to name fields with..
tbl_exampletable
[exampletable_pk]
[exampletable_FK_relatedtable_pk]
or
[exampletable_FK_relatedtable]

I learned the hardway to not use the same field name in multiple tables; thus even if I use [PK] or [ID] in every table it's always prefaced with the table name. Took me two weeks to figure out why an embedded query kept failing and I had the wrong table/field referenced.

The combo's rowsource is:
[tblRetreadOptions]![RetreadOptionsID]

verfiy that this is actually the value being set in the bound column... easy, temporarily set widths from 0;1;1;0;0 to all 1" so that you can see the first value.

[Knowlton]

In the edit relationships window - table/query:tblRetreadOptions!RetrreadOptionsID
related table/query - tblRetreadDetails!RetreadOptionsID

Referential integrity is enforced with cascade update and delete.

The combos control source is tblRetreadDetails!RetreadOptionsID. The forms control source is tblRetreadDetails and not a query that includes tblRetrteadOptions.

For what it may or may not be worth, for a while now, I have been having problems with Access closing. If I open a file and only look at something or maybe print a report, when I close it, Access closes completely, but if I enter records and then close, the file closes but Access remains open and will only close by using the task manager to force it to close. I am using Access 2003.
Thanks a bunch for your help!

[Knowlton]

I forgot to mention that the value being set is the RetreadOptionsID number.

[zmbd]

Tied up at the momement so let me digest things...

In the meantime: IFF you don't have a docmd.close in the on close events then it sounds like your installation is damaged beyond the basic repair.

I usually don't recommend this; however, you may be due for a complete un-install/re-install of the software.

Before you do this; make a new, blank database, import all of the items from the old database into the new database.
See if this fixes things...


If not... U/R-Install Office.

Once done, you'll need to run update to receive all of the critical updates. Or you can download the file from Microsoft here: Microsoft Office Service Pack 3 MAKE SURE YOU READ EVERYTHING ON THIS PAGE!

I can not stress enough the need to backup your harddrive, or at least the importaint files BEFORE you start anything. Gather all of your restore disks for the PC and be prepared to nuke and re-install on the rare oddball fatal event.

[zmbd]

In the edit relationships window (...)
tblRetreadOptions!RetrreadOptionsID

I'm guessing this is a typo??

[Knowlton]

Yes that is a typo. Sorry

[Knowlton]

I had actually thought about recreating my form to see if that made any difference. It seems like I remember having some issues another time and just started over and everything worked fine.
Take time to do your work. I've got other things going on also so I'm not in a rush.

[zmbd]

Ok
so
looking at what you've posted, I'm back down to something got broken.

1) Decompile option. Make the shortcut as given in this MS KB article: http://support.microsoft.com/kb/819780
However, Instead of altering the file's icon, I make a new shortcut on the desktop and enter the information.
Make a backup of the database you are working with and then drag and drop the copy on the new decompile.
Once open perform compact and repair
save the file
open using the "normal" method.

2) recreate the database using a new database file and then importing the tables etc...

3) complete U&R-Office. Down load the SP3 from MS first and follow the instructions in the download page.

[Knowlton]

I think you're right about something is broken. After reading through the article, I have experienced "Access has encountered a problem and needs to close".

I have a couple of questions for clarification.
1)I have a split database so do I do this for the data file, the program file or both?

2)You say to make the shortcut as given in the MS KB article. I didn't understand anything in it about a shortcut. I guess it may make sense as I go through it but the process makes me a bit nervous. I'm guessing the process is to repair the file(s) and then uninstall and reinstall Office so the file(s) don't damage Access?

Sorry for the trouble.

[zmbd]

1) Unless the backend has anything other than tables it should be ok.

2) Sorry, that made sense in my head at the time.
a) You will need this information:
- msaccess.exe
- "drive\path\" to access
- /decompile
so for my install on a windows7(64b) it reads:
- msaccess.exe
- "C:\Program Files (x86)\Microsoft Office\Office14" (include the quotes for spaces and symbols)
- /decompile
b) right click on your desktop, new, shortcut
the wizard may open... enter just the path to msaccess and name it decompile - let it finish, if no wizard then enter the properties as follows.
c) find the shortcut on the desktop and right click - properties
shortcut tab
Target = msaccess.exe /decompile
Startin = "C:\Program Files (x86)\Microsoft Office\Office14"
(this is for me... you will need your location)
[apply]
things will alter just a tad, that's ok
[ok]
At this point the icon should change to the Access icon and the word "decompile" for the title.

YEA

Now make a copy of the frontend
Drag the copy on-top of the new decompile
the file should open.
1) compact and repair
2) vbe - compile
save the file


Open copy using the normal method. If all goes well then archive your original file, rename and backup the "repaired" copy.

Now if this works then you shouldn't need to do anything else... only if there continues to be wierdness with office/access would you need to go with the U/R-Office route. Before doing the U/R check that this is a problem with all of your database files not with just this one.

The reason I do this "decompile" with a desktop shortcut is that I do some heavy coding and testing; thus, I occationally get things fouled beyound the normal reboot the pc fix and it's a pain to keep going in and makeing the /decompile at the file level then remove it etc... (BTW: My 1st step in all troubleshooting, close everything, shutdown windows and pc, COLD boot - 90% "cure" rate. :)

[Knowlton]

Sorry for taking so long but I have been tied up with other things. Compact and repair made no difference. I found that I had added another reference to the RetreadOptions table because sometimes there is a different retread applied from what was requested and I wanted to track this. In adding that field, I neglected to remove the "0" default and that was causing the problem.
Thanks again for your help!