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.

8 comments
iandrew
iandrew

As I read it, each job is associated with one property. So without changing tables or queries, you can use frmProperty as a subform on frmJobs, linked PropertyId=fldPropertyId.

kevin.nichols
kevin.nichols

you will have to create a dropdown or new form that selects your property first before you can create your job. You never mention how you add new porperties. OR (I would go this route) the other way is to embed a window that shows the table properties that match. This way the main form should not give you any issues. Access treats the embedded window as a subform and you avoid the Join issue. Just query to fill the sub form with the matching data.

robo_dev
robo_dev

You cannot have one-to-many-to-one relationship. Maybe in California or Utah, but that aint allowed around here.... My first answer was that this was a trick question: the first mistake was using MS Access, the second mistake was implementing what a user requested, and the third mistake was lack of proper QA testing.

duffym
duffym

so many relationships...so little time....

hfeddema
hfeddema

I believe that you need a many-to-many relationship between Jobs and Properties here. Then you can use a subform bound to tblProperties to select as many properties as needed for a Job, or vice versa. See the sample database for my Access Archon #178, which can be downloaded from this link: http://www.helenfeddema.com/Files/accarch178.zip

Tony Hopkinson
Tony Hopkinson

see this one (or the reverse come up pretty often). Should have been a look up not a join. Inserting or updates of keys (if a surrogate wasn't used) through joins nearly always turns out to be an inflatable dart board solution, avoid and evade.

sunsesh
sunsesh

each tbljobs record may have only one propertyid from tblproperty but there is no guarantee that a property id needs to be associated with only one tbljobs record. Hence when the user tries to select a property, there could be several tbljobs records associated with it. (Remember that tblproperty and tbljobs could have a one:many relation). Hence the data base scream at and curse you: it does not know which tbljob record (of all records having the same property as selected) to associate to the form. One way to avoid it is to display concatenated jobid and the corresponding property id in parentheses or whatever and try to insert the job id for the form.

Ronald Hoek
Ronald Hoek

Common problem when using joins and adding data ;)

Editor's Picks