"You cannot add or change a record" - intermittent error
Posted on 2011-09-29
My VB 2008 app uses Access databases, with multiple tables but two that are primarily used (the parent, of course, and one child table). I am intermittently getting the error "You cannot add or change a record because a related record is required in table 'Header'." Normally everything is fine; the code doesn't change between when it works and when it doesn't.
Looking at the situation in more detail with the debugger, I can see something that may be linked to the error. I'm using OleDbDataAdapters to .Fill and .Update the main database. The .Update for the parent table (Header) works fine. After I finish that update, I reset the .RowFilter on a Datagrid that contains the records for one of the child tables to match the new parent record's rowID that's coming from saving the record to disk (autoincrement field). When I .Update that child table, I get the error message. If I immediately stop with the debugger, I can see that the Datagrid's .RowFilter has been reset, but the child record is no longer displayed. Yet when I look at the child record in the datatable, it has the correct rowID for the foreign key.
The bizarre thing is that the error is very sporadic. I can run the exact code to save a new entry, the same day or different days, and it runs fine. I'm at a loss to figure out how to correct the save process. (It happens only when saving a new set of records, parent and children.) Any ideas on what could be happening and how to resolve it?