[AccessD] Once upon a time in the database

Yes, Dean, you're onto it here I feel.

Regards
Steve

-----Original Message----- 
From: Dean Davids
Sent: Tuesday, August 30, 2011 3:58 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Once upon a time in the database

Are you sure your combobox is bound to the FK in tblJobs not the primary key 
in tblProperties?


Dean S. Davids
www.cmbscorp.com
954-868-4421

On Aug 29, 2011, at 11:16 PM, "Rocky Smolin" <rockysmolin at bchacc.com> wrote:

> Dear List:
>
> It was so simple for so long.
>
> A form of Jobs (frmJobs) was bound to a table of Jobs (tblJobs).  Each job
> could be associated with a property (like real estate property, not an
> object property).
>
> So there was a Property form (frmProperty bound to tblProperty).
>
> There was a combo box on frmJobs for with all the properties listed and
> bound to the FK fldPropertyID in tblJobs.
>
> tblProperty had PropertyID as an autonumber PK.  And tblJobs had a FK
> fldPropertyID pointing to the field PropertyID in tblProperty. Because the
> Master of the Database had inherited the app from a troll who knew not the
> naming conventions, the PK in tblProperty did not have the fld prefix 
> which
> the Database Master gave to the FK in tblJobs.
>
> But no matter.  And everyone in the database was happy.  For a very long
> time.
>
> Until one day when the User decided that he wanted a change - a small
> 'enhancement' he said.  (dark clouds gather)
>
> The User wanted some of the fields in tblProperty to be displayed on
> frmJobs.
>
> 'No worries' said the Master of the Database.  'I'll merely drop the 
> fields
> from tblProperty on to frmJobs  and change the Record Source of frmJobs 
> from
> tblJobs to qryJobs, where qryJobs had a join from tblJobs to tblProperties
> which included all records from tblJobs and only those records from
> tblProperty where the joined fields (fldPropertyID) were equal - the 
> awesome
> and powerful 'Right Join.'
>
> 'Brilliant', cried the User as property fields showed up on frmJobs.  And
> were even editable!
>
> Until...the User tried to add a new Job record.
>
> When he went to NewREcord and selected a property from the combo box on
> frmJobs, the User generated the error: 'The current field must match the
> join key 'PropertyID in the table that serves as the 'one' side of the
> one-to-many relationship.'
>
> Much to his chagrin the Master of the Database generated the very same 
> error
> on his machine.  And worse yet, was unable to fathom the cause of the 
> error.
>
>
> Apparently the program was trying to create a record in tblProperty to go
> with the selected property in the combo box on frmJobs.  But why?
>
> The Master of the Database was loathe to admit his failure to the User and
> so turned to his wise Council of CodeMeisters, who would surely in an
> instant show him the error that eluded him lo these many unbillable hours.
>
> And so he waited in anticipation of the obvious solution which the Master 
> of
> the Database could not, for the professional life of him, see for himself.
>
> And so with profuse thanks in advance, he remains, etc., etc., ......
>
>