[AccessD] I should be able to do this - resolved

Darryl,
Thanks, that is a terrific story.  Yes, I think I finally have this 
lesson learned.  :-)
T

Tina Norris Fields
tinanfields at torchlake.com
231-322-2787


On 12/7/2011 5:49 PM, Darryl Collins wrote:
> " and in my heart of hearts I did know it."
>
> Heh... You know Tina, over the years I have had many of these moments, and in every instance it has come back to bite me.  And the longer you leave it, the greater the pain.  These days when I get that feeling, I make sure I fix the problem immediately - even if it seems like a lot of unnecessary work.  I guess the golden rule is nothing should be fixed and everything can (and probably will) change.
>
> I have a great example of "months in the year"  Sure, everyone knows they should be 12, but I had one year, in one company where they had to make it 11 months in a year instead  - they need to do this as the company had been purchased by another company who used a different report cycle.  To get the cycles aligned they had to effective drop a month.  In short,  everyone who had stuff hardcoded regarding months and periods etc had a lot of work to do...  A good example of something that will never ever change, changing.
>
> Cheers
> Darryl.
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tina Norris Fields
> Sent: Thursday, 8 December 2011 8:33 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] I should be able to do this - resolved
>
> Dear Friends,
>
> Of course, you were right, I should not have had the Stu_Counselor field in the students table in the first place.  There's a story behind this and a lesson learned again and again, but I think for the last time, this time.  This all began as a little database for a counselor friend of mine, just to track the students for whom he and his colleague were responsible.  There were never going to be any more counselors, so I did make a lookup field in the students table that had a value list - just the two counselors' names.  Okay, I should have known that there would ultimately be other counselors, and in my heart of hearts I did know it.
>
> When the remodeling time came, I did add a table for the counselors and a numeric field in the students table.  BUT, I overlooked the existing references to the Stu_Counselor field in the queries that underlay the reports.  So, when I saw that records were not appearing for the new counselors, in those queries, I started down the wrong path, trying to force updates into the Stu_Counselor field based on the Counselor_ID selection.  I didn't want to walk through all the queries and all the reports to find the old links to the Stu_Counselor field.  Yet, that is the only real solution.
>
> I am almost finished examining every query and report now.  Thank you all for gently saying "WHAT!!! You shouldn't be doing THAT!!!"  You are right.  I won't do it again.
>
> T
>
> Tina Norris Fields
> tinanfields at torchlake.com
> 231-322-2787
>
>
> On 12/4/2011 1:47 PM, Tina Norris Fields wrote:
>> Dear Friends,
>>
>> Okay, I have a form with a combo-box for selecting the counselor who
>> will be assigned to the student whose record is displayed in the
>> form.  Once that is updated, I want the matching name of the counselor
>> to placed in the student's record.
>>
>> So combo-box is looking into the tblCounselor, which has two fields
>> Counselor_ID and Counselor_LName.  Once the choice is made, I want to
>> update the field Stu_Counselor in the tblStudent with the name found
>> in tblCounselor.Counselor_LName.
>>
>> I'm writing gibberish in my AfterUpdate event and I need to be
>> rescued.  Any help waking up my brain?
>>
>> Thanks,
>> T
>>