Dynamic Name Change of Report Fields

[ojasg]

In my main table, I have 10 variables with datatype "Yes/No".. each with a corresponding label "lblVar(i)" where i = 1-10. The same main table has a field called "Area". There is another table "tbQuestions" with two columns: "Question" and "Area".
On the main form, depending on which area is present in the recordset the 10 variables get assigned "questions" from the "tbQuestions"... and everything works flawlessly. This dynamic assignment of variables is made possible by the follow code.

\\START OF CODE\'Dynamically assign names to the variable for display and hide unused variables

Dim i As Integer
Dim strSQL As String
Dim rs As DAO.Recordset

'SQL String to pull the Questions
strSQL = "SELECT tbAuditQuestions.Question FROM tbAuditQuestions WHERE tbAuditQuestions.Area = " & Me.AreaID.Value & ";"
'Loop through records till you find the last record
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
With rs
rs.MoveFirst
i = 0
Do While Not rs.EOF
Me.Controls("lblVar" & i + 1).Caption = rs.Fields(0)
Me.Controls("Var" & i + 1).Visible = True
Me.Controls("lblVar" & i + 1).Visible = True
rs.MoveNext
i = i + 1
Loop
rs.Close
Set rs = Nothing
End With

\\END OF CODE\
My issues is when I try using this code in a report. I need each record to display a different set of questions (lblVars) depending on the area in that record but the report shows same questions for each record, taking the question values from the first record.
Which event should I put this code in so that each record runs the code and displays the corresponding questions for that record.

I know this is a bit tricky as I have extensively searched online without much help.. Hopefully you guys here have a solution.

Thank You!

 

[dhookom]

Tables have fields, not variables.

Where are you running the code? I assume it is some report section format event.

Duane
Hook'D on Access
MS Access MVP

 

[ojasg]

Yes.. What I meant is the field is dummy variable. It is actually a "Yes/No" field; I just have to display a different question next to Yes/No field depending on the area selected.
I have tried to run the code in every possible event on the report. What I need is for each record to run this code and change the label for the "Field" called "Var" to a particular string based on the associations in another table.
For example, if the Report shows two records, one for Area 1 and the other for Area 2,
then For Area 1 -->
Label for field "Var1" should shows as "Area1Q1"; for "Var2" as "Area1Q2" and so on.
For Area 2, in the same report just another record -->
Label for field "Var1" should show as "Area2Q1" and so on...

I have a table with two columns "Area" and "Question" set as follows
Area Question
1 Area1Q1
1 Area1Q2
2 Area2Q1
3 Area3Q1
and this is where I get my values for the dummy variables in the main form.
I hope this help to clarify my issues.

Thank You for offering to help.

 

[MajP]

From what you are saying, I assume this works in a form. Have you tried the section onformat or onPrint event?

 

[dhookom]

I would expect the code should be in the On Format event of the detailed section of the report, assuming the controls are in the detail section.

Duane
Hook'D on Access
MS Access MVP

 

[ojasg]

You guys are genius!
OnFormat works and yes the controls are in the details section.

Thank You So Much!

I don't post a lot of questions on here as most are answered in forums; but MajP believe me, you have answered almost every question I have posted here in last 5 years; including some posted by my co-workers.