Changing the relationship depending on certain criteria

[cssilver]

I'm not sure if this is the right place to ask my question.<br>I have a report based on a query.&nbsp;&nbsp;The query joins one of 4 &quot;comment codes&quot; in table with the actual comment from the comment table. I want it to choose which of the 4 comment codes to join with the comment table depending on a) a certain criteria AND/OR b) which one the user chooses.<br><br>Can I do this in VBA?&nbsp;&nbsp;How?

 

[DougP]

First of all every table is faster looking up a ID field<br>Like:<br>Table1 &quot;Comments they pick&quot;<br>CommentID&nbsp;&nbsp;&nbsp;&nbsp;Comment itself<br>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Blah Blah<br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;More good stuff.<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;etc<br>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;etc etc<br>Table2 Comment you want to match up.<br>CommentID&nbsp;&nbsp;&nbsp;&nbsp;Comment itself<br>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;this goes with the Blah<br>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;this goes with the More good stuff.<br>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;this goes with the etc<br>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;this goes with the etc etc<br><br>If you put the comments in a combo box<br>and have the ID field hidden. Then when they pick one you can just match their &quot;1&quot; with your &quot;1&quot; and bingo they go together.<br><br>In the After update event of the combobox<br>---------------------<br>Private Sub Combo1_AfterUpdate()<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database, rst As Recordset, SQL As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;SQL = &quot;Select * From [YourCommentTable] where ID = &quot; & Combo1.Column(0)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = db.OpenRecordset(SQL)<br>&nbsp;&nbsp;&nbsp;&nbsp;Me!SomeTextbox = rst![Comment]&nbsp;&nbsp;'&lt; this was just looked up above<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;db.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[cssilver]

Thanks.&nbsp;&nbsp;I'll need to think about this one properly.&nbsp;&nbsp;Just a few questions on it:<br>1) The 2 tables you specified: I'm not sure I understand them.&nbsp;&nbsp;Is one where the user enters the code & the other where the codes & their related comments are stored?<br>2) If that's the case, I have that already.&nbsp;&nbsp;I don't need a combo box, because the user (data-entry person) doesn't care or even know what the comment is- the teacher has a printed list of available comments (printed from the comment file) & writes down the comment number for the data-entry person.&nbsp;&nbsp;What I need to do is print the comments that correspond to the code ONLY FOR THE CURRENT TERM.&nbsp;&nbsp;Or a teacher/parent/principal may request a copy of the first term's report card with the 1st term's comments, even though it is now the third term.<br>The way I did it until now is that each term, I manually went into the query & set up a different join each term (term 1, the join would be to term1comments, then in term2 I would manually change it to join to term2comments, etc)

 

[elizabeth]

Can you show us the table structures? Like:<br><br>tblStudent: StudentID(PK), Student Last Name,.... Comment1(FK), Comment2(FK),Comment3(FK),Comment4(FK)<br><br>tblComment: CommentCode(PK), CodeDescription<br><br>If it's like this, a simple way to do this would be to write a query with tblStudent fields and (joined) tblComment description, with the criteria being the CommentID that is chosen via the form. You could use a button that asks you which of the 4 reports you want, or 4 separate buttons.

 

[cssilver]

Yes, that is my table structure.&nbsp;&nbsp;But I don't understand how to write the query.<br>At the end of term1, I want to print the code description of comment1, at the end of term2, I want to print the code description of comment2, etc.<br><br>If possible, maybe using a slightly different query, I'd like the user to be able to select which comment (1,2,3 or 4) should be printed.<br><br>Caroline

 

[elizabeth]

Will every student in the report (assuming multiple students per report) be printing the same comment/are they in the same term? If so, you could have the report button pop up with a selection control that let's the report generating user choose the appropriate comment term. If it is different per student, you need to have something in the student record indicating which one to print. Most of the dbs I've heard of with this type of thing have a tblStudent and also a tblGrade, or something like that, in which the StudentID, ClassID, TermID, and Grade are stored. If you ran a query against this type of structure, the term would determine the comment.<br><br>If you are just building the structure at this time, maybe you'd like to start at design basics (table structures) to make sure you're set up with everything you need. If you're looking for that type of help, just describe the domain/operational functions you will be trying to achieve and we'll give you feedback on structure. Having the correct structure will make all future operations easier because for the most part you'll be working on solving solve run-of the-mill problems that lots of people can help you with instead of strange problems that people rarely run into.

 

[cssilver]

Unfortunately, I am NOT setting up the tables - I've inherited them.<br><br>The reports are 1 page per student.&nbsp;&nbsp;All students being printed will be in the same term, but not be printing the same comment.&nbsp;&nbsp;I do not have a &quot;termid&quot; field, but perhaps I could have the user input which term and then use the &quot;&&quot; operator to determine which field to actually use:<br><br>In pseudocode:<br><br>Input TermID<br>field.to.use = &quot;term&quot; +&termid+ &quot;comment&quot;<br><br>Then I could use DougP's code (or something like it) to print out the actual comment.<br><br>Does this make any sense?<br><br>Caroline

 

[elizabeth]

Sort of. Here's an example, using (I hope!) names of the table & fields I mentioned above:<br><br>1. Create a form (Form1) with an option group (Frame0) with values of 1, 2, 3, and 4.<br>2. Add a button (use the button wizard) that opens your report..<br>3. Enter the following code in the OnOpen event of your report:<br><i><br>Me!RecordSource = &quot;SELECT [tblStudent].[LastName], [tblComment].[CommentText] &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;FROM tblComment INNER JOIN tblStudent &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;ON [tblComment].[CommentCode] = [tblStudent].[Comment&quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Forms!Form1!Frame0 & &quot;];&quot;