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

" 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
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com