[AccessD] Once upon a time in the database

#2 would work except that the real estate is so bloody tight on this form
that I don't have room to segregate the tblProperty fields from the tblJobs
fields.  

I'll try #1 in the morning when my head's a bit clearer but why would that
make a difference?

R

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: August 29, 2011 8:42 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Once upon a time in the database

Two options that I can think of immediately:

1.  (Non editable Property fields)
On the Jobs form, make the recordsource of the  Properties combo a query
which includes the required additional fields 

Set the additional column widths in the combobox to 0

Create a few textboxes with their source "=cboProperty.Column(2)" etc.

or

2.  (Editable Property fields)
Put a subform on frmJobs with a recordsouce of tblProperties.
     
Set the ParentLink to cboProperty and the child link to fldPropertyID

--
Stuart

On 29 Aug 2011 at 20:16, Rocky Smolin 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., ......
> 
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com