Set field control source for report causing error

[Eprice]

Hi, I am trying to set a control source for a field on an Access 2010 report by using the field number. I don't know what the field name will be. I can return the field name on the report but get an error trying to set a fields control source to that field. The error I get is "You can't set the Control Source property in print preview or after printing has started". I put this code in the Detail Onformat event. Any ideas. Lisa

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim r Set r = CurrentDb().OpenRecordset("tblTemp")
FieldCount = r.Fields.Count
If FieldCount > 30 Then
Me.txt1 = r.Fields(30).Name
Me.txtValue1 = r.Fields(30).Value
End If
If FieldCount > 31 Then
Me.txt2 = r.Fields(31).Name
Me.txtValue2.ControlSource = r.Fields(31).Value
End If
End Sub

 

[MajP]

How about on the reports on open event? I do not see a reason you would need to do it in the format event.

 

[Eprice]

If I put the code in the reports Open event I get this error
You can't assign a value to this objec pointing to
Me.txt1 = r.Fields(30).Name

 

[dhookom]

I would try renaming the field in a query so the report required no code other than to possibly make one or more controls visible. The column/field names would always be the same.

Duane
Hook'D on Access
MS Access MVP

 

[Eprice]

Thats the whole problem. I don't know what the column(field) names are. This is what's happening. Its a review program where the questions change for each state. I let them create their own table adding the fields needed for each state and naming the table with the state and the year completed. A combox box on a form lists all tables in the database. They select which review they want, add records and answer the quesitons (which are the feilds they created). Now I need to get those fields on a report, not knowing what the field names are since they are different for each review. I am trying to use the field number. There is probably an easier way and it all works up to the report part. lisa

 

[dhookom]

Your primary issue might be the un-normalized structure of your tables. I would create tables of states, questions, and answers (at a minumum):

[pre]tblStateQuestions
=====================
stqStQID primary key autonumber
stqStaID link to table of states
stqQuestion actual text of question
stqSequence order of question within the state

tblAnswers
======================
ansAnsID primary key autonumber
ansStQID link to tblStateQuestions.stqStQID
ansValue actual answer value[/pre]

There would never be a need to change any table structures. You would only update data, not objects.

Duane
Hook'D on Access
MS Access MVP

 

[Eprice]

The questions will change every year with evry state.

 

[MajP]

I am putting money on txt1 is a label and not a textbox. So:
me.txt1.caption =

 

[dhookom]

Eprice,
Do you want to maintain data or maintain queries, reports, forms, tables, and code? I would choose to maintain data. Consider reading this article.

Duane
Hook'D on Access
MS Access MVP

 

[Eprice]

txt1 and txtValue1 are both unbound text boxes and yes we must keep the table data for five years.

 

[dhookom]

Add a year field to the table of questions and never touch a design again. It's a huge step to create the normalized tables but it's much more sustainable in the future.

Duane
Hook'D on Access
MS Access MVP