Rocky Smolin
rockysmolin at bchacc.com
Tue Aug 30 08:28:42 CDT 2011
Yep. Checked it again. The property combo box is bound to the FK in tblJobs. In fact, I just deleted the PK from the query that is the record source for frmJobs because I really didn't need it there and got the same result. I think I'm going to have to unbind those property fields on the form, go back to tblJobs as the record source, write a couple of modules - GetPropertyFields and PutPropertyFields - which I can call from anywhere that will update the property fields in the property table in the after update event of any of the unbound text boxes, and get all the values in the Current event and the After Update event of the combo box. R -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dean Sent: August 30, 2011 6:13 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Once upon a time in the database Are you sure you have the FK from tblJobs? If you are getting that value from your recordsource query and in that query you inadvertently pulled the PK from tblProperties rather than tblJobs, this behavior would make sense. Any additions would be trying to add a new Property, not assign a property to the job. I know I am repeating myself and chance are you have confirmed that is not the case, but it just seems to fit from what I see. Regards, Dean On Aug 30, 2011, at 8:50 AM, Rocky Smolin wrote: > OK here's a clue - I put two textboxes on the Jobs form and bound one > to the FK on tblJobs and one to the PK in tblProperty. > > When I click the add button which runs DoCmd.GoToRecord , , acNewRec, > the textbox bound to the FK in tblJobs shows (Autonumber) - which is correct. > But the text box which shows the PK in tblProperty ALSO shows (Autonumber). > > > IOW, BOTH tables are going to the new record. > > So now I see the problem, I don't know what a clean solution is (I can > think of a couple kludgy ones) > > Rocky > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dean > Sent: August 30, 2011 5:29 AM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Once upon a time in the database > > It really should be workable. That is why I say that it didn't seem > precisely the same as my own experience. The key, I would suspect is > the datasource and rowsource of the combo. I would trace these back > through the query to be sure that the datasource is coming from > tblJobs, not tblProperties. Then be sure the rowsource is coming from > tblProperties, not tblJobs or your recordsource query. > Essentially that should make it no different from your original setup. > The problem would come when a user tries to select a property before > saving a new job and that can be handled programmatically by disabling > the combo or invoking me.Dirty = false at the appropriate times. > > to me, without seeing the form, I would put money on some sort of > mixup in those primary and foreign keys. > > Regards, Dean > > > On Aug 30, 2011, at 8:09 AM, Rocky Smolin wrote: > >> It goes back to the UI on the other thread. From the user's point of >> view, Undo restores the data to the condition before they started to >> make and changes - including adding a record. The command button >> works equally well for both adding and editing. >> >> So it's not me that has to delete an added record - it would be the >> user for whom the Undo doesn't work consistently any more. >> >> >> R >> >> -----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 10:15 PM >> To: 'Access Developers discussion and problem solving' >> Subject: Re: [AccessD] Once upon a time in the database >> >> Why do you need undo for new records... you can delete >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky >> Smolin >> Sent: Tuesday, August 30, 2011 12:47 AM >> To: 'Access Developers discussion and problem solving' >> Subject: Re: [AccessD] Once upon a time in the database >> >> 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 >> >> -- >> 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 > > -- > 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