Dean Davids
dnod at aol.com
Tue Aug 30 04:56:33 CDT 2011
I have had similar trouble with lookup queries as my recordsource. In my case it was always a matter of the record on the "one side" of the one to many relationship not having yet been saved and so attempted changes to the looked up fields would cause the error. If I follow your tale, it does not seem that is precisely your case but you can confirm. If so, you could disable or lock the property info fields while the new job is being created. If there are further complications, and you want to retain all your former functionality, perhaps you may go back to the original setup. Populate your unbound property info controls programmatically in the OnCurrent event and OnChange of the combo. You could even edit those fields in code, I would probably provide some form of navigation to the property form or a popup for that, myself. Dean S. Davids www.cmbscorp.com 954-868-4421 On Aug 30, 2011, at 12:46 AM, "Rocky Smolin" <rockysmolin at bchacc.com> wrote: > That might work. Actually, it would work except setting the FK because the > property wouldn't be selected at that point. In fact, the property may not > be selected for a job - it's not mandatory. > > I have an undo button which I'd have a problem with, though. The undo > button triggers a Me.Undo, which, if your editing works just fine - rolls > back all the unsaved changes of the current record. If Me.NewRecord = True > when the user hit the Undo button, works just fine - Me.Undo deletes the new > record. > > But if I save the new record when they click 'Add' then if they click Undo, > the new record wouldn't be deleted - just all the altered fields would be > set to null and an empty record would be left over in the table. Which is > messy. > > There's gotta be a reason why this error is being generated - this is > easy-peasy stuff. I'm not opposed to a workaround but in this case, if I > can figure out what's wrong I think I'll end up with a cleaner more > maintainable solution. > > I'm missing something here. > > Rocky > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson > (VBACreations.Com) > Sent: August 29, 2011 9:34 PM > To: 'Access Developers discussion and problem solving' > Subject: Re: [AccessD] Once upon a time in the database > > 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 > > -- > 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