Changing the relationship depending on certain criteria
Changing the relationship depending on certain criteria
(OP)
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?
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
Like:
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
rst.Close
db.Close
End Sub
DougP
dposton@universal1.com
Ask me how Bar-codes can help you be more productive.
RE: Changing the relationship depending on certain criteria
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
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
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.
Caroline
RE: Changing the relationship depending on certain criteria
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
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?
Caroline
RE: Changing the relationship depending on certain criteria
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 & "];"