[AccessD] Once upon a time in the database

I'm sure you are one of those incessant "class peddlers" as well... <grinning back>

Regards, Dean


On Aug 30, 2011, at 11:00 AM, Drew Wutka wrote:

> Sorry, you lost me at 'bound'.... <grin>
> 
> Drew
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
> Sent: Monday, August 29, 2011 10:16 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Once upon a time in the database
> 
> 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., ......
> 
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> The information contained in this transmission is intended only for the person or entity 
> to which it is addressed and may contain II-VI Proprietary and/or II-VI Business 
> Sensitive material. If you are not the intended recipient, please contact the sender 
> immediately and destroy the material in its entirety, whether electronic or hard copy. 
> You are notified that any review, retransmission, copying, disclosure, dissemination, 
> or other use of, or taking of any action in reliance upon this information by persons 
> or entities other than the intended recipient is prohibited.
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com