INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Set field control source for report causing error

Set field control source for report causing error

(OP)
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

RE: Set field control source for report causing error

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

RE: Set field control source for report causing error

(OP)
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

RE: Set field control source for report causing error

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

RE: Set field control source for report causing error

(OP)
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

RE: Set field control source for report causing error

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

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 

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

RE: Set field control source for report causing error

(OP)
The questions will change every year with evry state.

RE: Set field control source for report causing error

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

RE: Set field control source for report causing error

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

RE: Set field control source for report causing error

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

RE: Set field control source for report causing error

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close