Dean
dnod at aol.com
Tue Aug 30 09:05:36 CDT 2011
looks to be exactly what you will wind up with. Regards, Dean On Aug 30, 2011, at 10:04 AM, Rocky Smolin wrote: > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com