Rocky Smolin
rockysmolin at bchacc.com
Tue Aug 30 09:04:35 CDT 2011
Well I proven to myself that when tblJobs goes to NewRecord so does tblProperty. Which is what I don't want it to do. But the workaround - unbinding those fields - is not horrible. Is this a case for a sort of hybrid bound/unbound form? Rocky -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dean Sent: August 30, 2011 6:37 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Once upon a time in the database I know how it feels to be stymied by a process you know full well should work, but that course is a sure thing and once done the issue will be a past memory. I believe I would do the same in your shoes. You always have the option to go back and investigate further when you have free time. I have many of those snippets laying around waiting for the day that I just have nothing better to do. I likely never get back to them, but every once in a while I see another post and suddenly "Voila!" that's it. Lo and behold I do learn something every now and again. Regards, Dean On Aug 30, 2011, at 9:28 AM, Rocky Smolin wrote: > 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 > > -- > 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