 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / March 2008beginner query question
hello to all I would like to restate a question I posted a few days ago in different terms. I received excellent advice from Allen Browne and John Vinson but, as a beginner, I'm having trouble implementing their suggestions. I have a form that shows a student's ID and name in the header. In the body of the form are 40 textboxes that will each accept an integer from 1 to 5. These are evaluation scores for 40 different criteria. The fields may also be left blank if they do not pertain to that particular student. I've called these 40 textboxes s1, s2, s3 .... s40. The form is bound to a query that looks like this: Value can be 1,2,3,4 or 5. Field runs from 1 to 40. ID Value Field bro_jon 4 1 bro_jon 3 2 bro_jon 5 3 bro_jon 5 4 bro_jon 5 ................... etc ........... bro_jon 4 40 sam_tyl 2 1 sam_tyl 4 2 ................... etc ........... QUESTION - For any given ID, say bro_jon, how do I get the value keyed into textbox s1, s2, s3.... on the form to be recorded as the Value for Field 1,2,3... ? Am I doing this wrong? appreciative of any help > hello to all > [quoted text clipped - 31 lines] > textbox s1, s2, s3.... on the form to be recorded as the Value for Field > 1,2,3... ? Am I doing this wrong? Could you be a bit more clear about what the fields mean and what the values mean? >hello to all > [quoted text clipped - 8 lines] >these 40 textboxes s1, s2, s3 .... s40. The form is bound to a query that >looks like this: What do *YOU* mean by the term "criteria"? In Access that's a term usually used to define an item being searched for. >Value can be 1,2,3,4 or 5. Field runs from 1 to 40. > [quoted text clipped - 9 lines] >sam_tyl 4 2 >................... etc ........... Is this the structure of your Table? Do you actually have fields named Value and Field? If so they're both reserved words, and should be changed. >QUESTION - For any given ID, say bro_jon, how do I get the value keyed into >textbox s1, s2, s3.... on the form to be recorded as the Value for Field >1,2,3... ? Am I doing this wrong? Well, by using 40 unbound (I presume - do textboxes S1 through S40 have anything in their Control Source property?) textboxes on your form, you are certainly making your job much more difficult! For one thing, you're casting the structure of your evaluation in concrete. Suppose next month you come up with a list of 45 criteria, 38 of them drawn from the existing 40, and 7 new ones? You want to redesign your form, change a whole lot of labels, etc.? ouch!! If so, you will need to put a command button on the Form. It will open a Recordset based on your table; it will need to poll through the 40 textboxes looking for non-NULL values, and if it finds one, use the AddNew method to create a new record in the table, copy the value from the ID and the textbox to it... fairly complex code. I would suggest a different database structure entirely. You could have three tables such as Students StudentID LastName FirstName <etc.> <you presumably already have such a table> Criteria CriterionNo <Number, Long Integer, Primary Key> Criterion <Text, what's on your textbox labels now> <other info about this criterion, e.g. range of valid values; you might want to have more flexibility to include yes/no criteria, date criteria, etc.> Evaluations StudentID <who's being evaluated> CriterionNo <what they're being evaluated on> Measurement <your 1-5 value, currently> You could use a Form based on Students with a continuous Subform based on a Query: SELECT Criteria.CriterionNo, Criteria.Criterion, Evaluations.CriterionNo, Evaluations.StudentID, Evaluations.Measurement FROM Criteria LEFT JOIN Evaluations ON Criteria.CriterionNo = Evaluations.CriteriaNo ORDER BY Criteria.CriterionNo; Have textboxes on the subform for Criteria.CriterionNo, Criteria.Criterion, and Evaluations.Measurement, and use StudentID as the subform's master/child link field. Set the Locked property of CriterionNo and Criterion to Yes - they should be for display only, not for editing! This subform will show you all 40 rows with the text meaning of each criterion, and have a textbox in which you can enter the 1 - 5 value (or you could use a Combo box instead to make controlled data entry easier). The advantage here is that your criteria can now be edited simply by editing the contents of the Criteria table; it's not necessary to change your Forms design at all, you can add new criteria, delete criteria, change the text of criteria at any time.  SignatureJohn W. Vinson [MVP] john - thank you for your detailed reply. It is very clearly presented. > >hello to all > > [quoted text clipped - 88 lines] > design at all, you can add new criteria, delete criteria, change the text of > criteria at any time. hello to all John - I've followed your advice to a "T" and have learned a lot. I have one remaining question that is giving me trouble still. My subform is based on the query you suggested, and it does indeed show 40 records with fields Criteria.CriterionNo, Criteria.CriterionDesc, Evaluations.Measurement. But, when this subfom is placed in the main form (frmStudents based on table Students) and linked by StudentID, then the subform only shows those records where the student already has a measurement in the Evaluations table. I really need it to show all 40 records so that I can enter the student's values in the Measurement field. Any hints? Much thanks > >hello to all > > [quoted text clipped - 88 lines] > design at all, you can add new criteria, delete criteria, change the text of > criteria at any time. Victoria: You really DON'T need to show all 40 evaluation records. If your subform has the Allow Additions property set to Yes, you can add a new evaluation record for the student displayed on the main form. Just scroll to the bottom of the displayed rows, and type into the blank row there. Ideally, you should make the control bound to the Criterion field a drop-down combo box, whose row source is the query: SELECT CriterionID, CriterionText FROM Criteria WHERE CriterionID NOT IN (SELECT CriterionID FROM Evaluation WHERE StudentID=ThisStudentID) This StudentID is a variable that contains the ID from the main student form. SignatureTedMi > hello to all > [quoted text clipped - 10 lines] > I > can enter the student's values in the Measurement field. Any hints? You need to make another left join to the students table. The student should be on the left side and your existing query should be on the right side. HTH; Amy |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.