access reports 1

[thevenin]

I'll try to explain a simple example

Access 97 (probably the same for other versions of access)

Consider a report with a detail section (very narrow) containing a text box txtken.

I can drag and drop a field say A1 from the field list (icon in tool bar) into txtken and this will show a column of the field records when the report is opened. A1 reprsents the marks for a certain subject.

What I want to do is to be able to put the field A1 into txtken by writing an expression in the report open event. This should then
show a column of of the field records for A1 when the report is opened as explained above.

The reason for this is that in a school database I want to put different fields in txtken depending on the subject that is selected.
The way I intend to do this is to use a Select Cast statement. So, depending on the subject, I want to put different field references into txtken. For example if the subject is electronics then I want Field A1 to be be in txtken with the control source for txtken refering to Field A1. Similarly if the subject is control then I want Field B1 to be in txtken with the control source for txtken refering to Field B1.

I have had no success in writing the expression(s) in the report open event and yet I know it must be fairly simple.
I hope someone can help me

Thevenin

 

[dhookom]

This is an issue that I have noticed many times with "classes/subjects/grades". I assume your fields are subjects which is not a good, normalized structure.

I would normalize the structure before going any further. If you can't, you can create a union query that does this for you.
SELECT "Electronics" as Subject, FieldA1, ...
FROM tblSubjects
UNION ALL
SELECT "Control", FieldB1, ...
FROM tblSubjects
UNION ALL
....

You can then select the subject quite easily from the normalized view of your data.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Blorf]

I am not positive this will help, but if before you open the report, you run code to open it in design view, and change the value in the box to the correct field, close it, then run it, that should do the trick. Like below:

Function Modify_Report()
DoCmd.OpenReport "MyReport", acViewDesign

Rem What ever condition you use goes here:
M_Condition = InputBox("Enter 1 for ID, 2 for Num&quot

If M_Condition = 1 Then
Reports!MyReport!Field.ControlSource = "ID"
Else
Reports!MyReport!Field.ControlSource = "Num"
End If

DoCmd.Close acReport, "MyReport", acSaveYes

DoCmd.OpenReport "MyReport", acViewPreview
End Function



Ascii dumb question, get a dumb Ansi

 

[dhookom]

Thevenin,
If you don't want to report based on a normalized table structure, you could use a list box of field names and integer values like
1 SubjectA
2 SubjectB
3 SubjectC
4 SubjectD

Then, in the query, use a calculated column like:
TheSubject: Choose(Forms!frmA!optSubject, [FieldA], [FieldB], [FieldC], [FieldD])


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[thevenin]

To Blorf (programmer)

You are spot on. What I didn't realize is that I would have to change the field in design view and then open the report. I have not tried it yet but I am sure it will work.

Thevenin

 

[Blorf]

Cool.

Ascii dumb question, get a dumb Ansi