Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Changing the relationship depending on certain criteria

Changing the relationship depending on certain criteria

Changing the relationship depending on certain criteria

I'm not sure if this is the right place to ask my question.
I have a report based on a query.  The query joins one of 4 "comment codes" 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.

Can I do this in VBA?  How?

RE: Changing the relationship depending on certain criteria

First of all every table is faster looking up a ID field
Table1 "Comments they pick"
CommentID    Comment itself
1            Blah Blah
2            More good stuff.
3            etc
4            etc etc
Table2 Comment you want to match up.
CommentID    Comment itself
1            this goes with the Blah
2            this goes with the More good stuff.
3            this goes with the etc
4            this goes with the etc etc

If you put the comments in a combo box
and have the ID field hidden. Then when they pick one you can just match their "1" with your "1" and bingo they go together.

In the After update event of the combobox
Private Sub Combo1_AfterUpdate()
    Dim db As Database, rst As Recordset, SQL As String
    Set db = CurrentDb
    SQL = "Select * From [YourCommentTable] where ID = " & Combo1.Column(0)
    Set rst = db.OpenRecordset(SQL)
    Me!SomeTextbox = rst![Comment]  '< this was just looked up above
End Sub


Ask me how Bar-codes can help you be more productive.

RE: Changing the relationship depending on certain criteria

Thanks.  I'll need to think about this one properly.  Just a few questions on it:
1) The 2 tables you specified: I'm not sure I understand them.  Is one where the user enters the code & the other where the codes & their related comments are stored?
2) If that's the case, I have that already.  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.  What I need to do is print the comments that correspond to the code ONLY FOR THE CURRENT TERM.  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.
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)

RE: Changing the relationship depending on certain criteria

Can you show us the table structures? Like:

tblStudent: StudentID(PK), Student Last Name,.... Comment1(FK), Comment2(FK),Comment3(FK),Comment4(FK)

tblComment: CommentCode(PK), CodeDescription

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.

RE: Changing the relationship depending on certain criteria

Yes, that is my table structure.  But I don't understand how to write the query.
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.

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.


RE: Changing the relationship depending on certain criteria

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.

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.

RE: Changing the relationship depending on certain criteria

Unfortunately, I am NOT setting up the tables - I've inherited them.

The reports are 1 page per student.  All students being printed will be in the same term, but not be printing the same comment.  I do not have a "termid" field, but perhaps I could have the user input which term and then use the "&" operator to determine which field to actually use:

In pseudocode:

Input TermID
field.to.use = "term" +&termid+ "comment"

Then I could use DougP's code (or something like it) to print out the actual comment.

Does this make any sense?


RE: Changing the relationship depending on certain criteria

Sort of. Here's an example, using (I hope!) names of the table & fields I mentioned above:

1. Create a form (Form1) with an option group (Frame0) with values of 1, 2, 3, and 4.
2. Add a button (use the button wizard) that opens your report..
3. Enter the following code in the OnOpen event of your report:

Me!RecordSource = "SELECT [tblStudent].[LastName], [tblComment].[CommentText] " & _
                              "FROM tblComment INNER JOIN tblStudent " & _
                              "ON [tblComment].[CommentCode] = [tblStudent].[Comment" & _
                               Forms!Form1!Frame0 & "];"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close