Changing the control source of a textbox

[Xenver]

Hi All,

I have many buttons on a form, i want each of these buttons to open the same form but change the control source of the textbox on the form so i can use it to update multiple fields and not have to create a bunch of forms to do the same thing. All of the fields being updated are on the same table, the control source of the update form is a select query which is based on that table.

my current code is this:

DoCmd.OpenQuery "qry_Credits Notes"
DoCmd.Close acQuery, "qry_Credits Notes", acSaveYes
DoCmd.OpenForm "frm_Credits Notes"
[Forms]![frm_Credits Notes]![Project Number] = Me.Project_Number
[Forms]![frm_Credits Notes]![Notes].ControlSource = "= [Forms]![frm_Credits Notes]![SS 2]"
 

only the bottom line is to do with this problem.

My problem is that after i change the control source when i try to enter data in the text box i get a beep and the bottom bar displays

"Control can't be edited; it's bound to the expression '[Forms]![frm_Credits Notes]![SS 2]'.

any ideas on how i can change the control source and still enter data in the textbox?

let me know if anything's unclear.

Thanks,

X

[twinnyfo]

Try this:

[Forms]![frm_Credits Notes]![Notes].ControlSource = [Forms]![frm_Credits Notes]![SS 2]

This should assign the value and allow edits.

[Xenver]

Thanks for the response!

When I exclude the quotes and double equals like you have above I get a error:

Run-time error '13': Type mismatch


Thanks.

[twinnyfo]

Opps - my mistake.

[Forms]![frm_Credits Notes]![Notes] = [Forms]![frm_Credits Notes]![SS 2]

But, no double equals - just one.

[Xenver]

I probably should have mentioned that the notes form, "frm_Credit s Notes" is a continuous form. When i use the above code all the textboxes update to mirror each other and the text i enter doesn't show up in the table.

Thanks,

X

[Xenver]

twinnyfo,

Thanks so much for your help on this and my past questions, i managed to figure out the code i need by messing around a bit, i got this:

[Forms]![frm_Credits Notes]![Notes].ControlSource = "[SS 2]"

working after i realized that there was no item on the form named SS 2, that item is in the query.

Thanks again, im sure I'll have more questions in the future!

X

[twinnyfo]

Yes, continuous forms will update all records.

On a side note, I am curious why you want to automatically update the value of this field if you also want to edit it manually...

[Xenver]

I'm not updating the value of the field, I'm updating the fields control source. The textbox is being used to enter notes/comments on various project credits, i want to update its control source so i don't have to make about 50 forms ( that'show many credits i have) that perform essentially the same function.

[twinnyfo]

I guess I'm not following exactly what you want to do.... And why. I don't understand the reason behind constantly changing the control source of a text box. If you have multiple fields that have essentially the same data, why not just use one field?

In terms of database design, there is no value in that.

[Xenver]

Ok, to help you understand why i wanted to do this i'll start by giving you a little context about what the database is actually for.

I work for a large engineering company, many of the buildings we design pursue certification under a system called LEED. LEED is a green building rating system which helps to encourage environmentally friendly building design. Basically to achieve LEED certification you have to implement certain design features in the building, water use reduction, energy efficiency, occupant comfort measures, etc. the things you can do to achieve LEED certification are broken down into credits, achieving each credit will grant a certain number of points, and the amount of points a building recives determines if it can be LEED certified, and what level it can be certified at (certified, silver, gold, or platinum).

Because of the large number of LEED buildings we design we want to track the credits each project pursues and if they are successful in achieving them, and if they are not successful, why. The form which has the buttons is used to enter the points being pursued on each credit for a single project, there is a "Notes" button for each credit. I wanted to change the control source of the text box so that when the "Notes" button on each credit is clicked it would open the notes form and the user could enter notes which would be stored and associated with the correct credit.

So, I don't have multiple fields with essentially the same data, the notes on each credit will be different, i just didn't want to have to create a form to make notes on each credit.

Hope that helps to clarify my purpose in this.

Thanks,

X