I need to create a report based on a table that has a primary key, 5 native fields and 12 foreign keys. I need more granualar control over how the report behaves than HideDuplicates and IsVisible will give me. The primary key need not be printed visibly but the other sixteen fields must occupy a single line on the report. I need the report to display textboxes because the CanGrow property will need to be used. Combo boxes for the foreign keys are therefore ruled out. Textboxes drawn from a query of the table in question at present return the (integer) foreign key value, not the required text values.
Moreover, depending on the value of the field, the value of fields earlier in the row, and the previous value of the field, the report must display the value, display "-", or be Visible=False. As you can see, I will need VERY granular control over the reports controls. I have little experience and even less luck manipulating objects like
Dim ctl as control
dim rs as recordset
set rs=OpenRecordset(some sql statement)
for each somedamnthing in rs
check it against criteria
decide what was needed
get the value and set it equal to ctl.value
populate the report
next do it again dammit
Nothing I have googled has really pointed out how Access reports operate in theory, and how to take things a step farther. Like on continuous forms, report controls are multiple instances of a single object whose values change with each row in the recordset. What I tried originally was to put controls from the underlying report query on the form, set them to Visible=false and then add unbound textboxes whose data source
That quickly got ugly with zillions of nested IIf statements to test multiple conditions. Next I tried creating custom functions to set the textbox's value. PROBLEM: I don't get multiple instances of the control. I only get the last value in the recordset in a single textbox.
There is an open question worth 500 points regarding that specific attempt to solve this at
Full points here will go to those who help create an answer that will aid other poor buggers like me who need very fine granular control of reports. Emphasis on theory and design are appreciated