Data Management

Office challenge: Can you troubleshoot this database error?

This week, troubleshoot a mysterious and troublesome error after what should've been a quick and easy enhancement goes sour.

Once upon a time, a form of Jobs (frmJobs) was happily bound to a table of Jobs (tblJobs). Each job could be associated with a property (like real estate property, not an object property). There was also a Property form (frmProperty bound to tblProperty).

Now, there was also a combo box on frmJobs that listed all the properties in this database. It was bound to the FK fldPropertyID in tblJobs. tblProperty had PropertyID as an AutoNumber PK, you see. And tblJobs had a FK fldPropertyID pointing to the field PropertyID in tblProperty.

Everyone was happy.

Until one day when User decided to make a change - just a small enhancement he said. User wanted to display some of the fields in tblProperty on frmJobs.

"I grant your request," said the Master of the Database.

The Master turned to the awesome and powerful Right Join.

"I'll drop the fields from tblProperty onto frmJobs and change the form's Record Source to qryJobs, where qryJobs has a join between tblJobs and tblProperties. Thereby, it will return all records from tblJobs and only those records from tblProperty where the joined fields (fldPropertyID) are equal.

"Brilliant!" cried User as properties appeared as if by magic on frmJobs. And they were even editable! It was good.

Until User tried to add a new Job record.  When User went to New Record and selected a property from the combo box on frmJobs, the database bellowed loudly:

THE CURRENT FIELD MUST MATCH THE JOIN KEY PROPERTYID IN THE TABLE THAT SERVES AS THE ONE SIDE OF THE ONE-TO-MANY RELATIONSHIP.

When he tried, the Master of the Database also angered the database. It seemed as though the database wanted to create a record in tblProperty to go with the selected property in the combo box on frmJobs. But why? Thus began the Master's quest through the valley of troubleshooting.

The Master of the Database was loathe to admit his failure to User and so turned to his wise Council of Code Meisters, who would surely show him the error that eluded him lo these many unbillable hours. He waits there still.

Can you save the Master of the Database?

Thanks to Rocky Smolin for this week's creative Access challenge.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks