[AccessD] Once upon a time in the database

William Benson (VBACreations.Com) vbacreations at gmail.com
Mon Aug 29 23:33:57 CDT 2011


Create a New Job button which puts a record in the Jobs table, set FK
fldPropertyID = the propertyid on the form, then requery the recordsource
and position the form using bookmark on the record you have created. It is
how I would handle it and not even blink (til someone here told me I am
wrong....)

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Monday, August 29, 2011 11: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




More information about the AccessD mailing list