[AccessD] Once upon a time in the database

Dean dnod at aol.com
Tue Aug 30 08:37:05 CDT 2011


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





More information about the AccessD mailing list