473,325 Members | 2,608 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Changing the control source of a textbox

20
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenQuery "qry_Credits Notes"
  2. DoCmd.Close acQuery, "qry_Credits Notes", acSaveYes
  3. DoCmd.OpenForm "frm_Credits Notes"
  4. [Forms]![frm_Credits Notes]![Project Number] = Me.Project_Number
  5. [Forms]![frm_Credits Notes]![Notes].ControlSource = "= [Forms]![frm_Credits Notes]![SS 2]"
  6.  
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
Aug 20 '14 #1
16 6923
twinnyfo
3,653 Expert Mod 2GB
Try this:

Expand|Select|Wrap|Line Numbers
  1. [Forms]![frm_Credits Notes]![Notes].ControlSource = [Forms]![frm_Credits Notes]![SS 2]
This should assign the value and allow edits.
Aug 20 '14 #2
Xenver
20
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.
Aug 20 '14 #3
twinnyfo
3,653 Expert Mod 2GB
Opps - my mistake.

Expand|Select|Wrap|Line Numbers
  1. [Forms]![frm_Credits Notes]![Notes] = [Forms]![frm_Credits Notes]![SS 2]
But, no double equals - just one.
Aug 20 '14 #4
Xenver
20
I probably should have mentioned that the notes form, "frm_Credits 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
Aug 20 '14 #5
Xenver
20
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:

Expand|Select|Wrap|Line Numbers
  1. [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
Aug 20 '14 #6
twinnyfo
3,653 Expert Mod 2GB
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...
Aug 20 '14 #7
Xenver
20
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.
Aug 20 '14 #8
twinnyfo
3,653 Expert Mod 2GB
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.
Aug 20 '14 #9
Xenver
20
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
Aug 20 '14 #10
twinnyfo
3,653 Expert Mod 2GB
Unfortunately, this does little to clarify how your tables are set up. Are there fields for each type of credit? Is there on ly one notes field? What does the "Notes" button actually do (or what is it that you are wanting it to do in this case)?

You say
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
but you do not mention how that note is saved (or where).

If anything, I am trying to understand the structure of your DB, so that I can more properly guide you to a solution.
Aug 20 '14 #11
Xenver
20
There is a table which stores the notes for each credit called "tbl_Credits Notes" Each credit has its own column for notes, and there is a project number column to track what project the note is about. The notes are stored as a text string in this table.

The "Notes" button is located on a form which is used to view the credits for each project called "frm_Projects Credits. Each credit has a button on this form which opens up the form "frm_Credits Notes". It is this form which is continuous and has the text box for entering notes. I'm changing the control source so I don't have to make a form for making notes on each credit, instead i can use one form, change the control source of the text box, and the user is able to enter notes for different credits.

The "Notes" button runs a select query which filters the notes to show only those which have the same project number as the project being viewed. This select query is the record source for the note taking form "frm_Credits Notes". After the query has been run the note taking form is opened. The project number from the form "frm_Projects Credits" is carried over to the note taking form "frm_Credits Notes". The Content of a small text box is updated to show the name of the credit whiich notes are being taken on. The control source of the notes text box is then changed. The code i use to do all of this is this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.  
  3. DoCmd.OpenQuery "qry_Credits Notes"
  4. DoCmd.Close acQuery, "qry_Credits Notes", acSaveYes
  5. DoCmd.OpenForm "frm_Credits Notes"
  6. [Forms]![frm_Credits Notes]![Project Number] = Me.Project_Number
  7. [Forms]![frm_Credits Notes]![Notes].ControlSource = "[SS 1]"
  8. [Forms]![frm_Credits Notes]![CreditNumber] = "" & [Forms]![frm_Credits Notes]![Notes].ControlSource & ""
  9. End Sub
Here's an image to help give you a better idea.





is this any better?

I'm more than happy to keep trying to explain my purpose here if you want, but i found the solution to my problem back in post #6.


Thanks,

X
Aug 20 '14 #12
twinnyfo
3,653 Expert Mod 2GB
Just on the surface, it looks like yo uwoul dbe better off if the "Notes" section was a subform, linked to the specific credit type. This would allow you to just enter the notes for each credit, without the hassle of 1) moving between forms and (especially) 2) trying to change the control source of a control. In my opinion, it doesn't seem like this is at all the right direction to go, since you are always entering notes in the same table in the same column, correct? If that is the case, you are not changing the control source.

However, I still have no idea how youare tables are set up, because you keep talking about your forms. The forms are jsut the interface of your tables, and if your tables aren't set up correctly, this will all get you no where.

I could offer advice on how to set up these tables, but I still don't know how you differentiate between credits. I know that there is a jargon/language barrier with this as I am still clueless as to what a "credit" is and how you are tracking them--but that is truly immaterial. If I understand you table structure, I can hopefully guide you further down the road with this.
Aug 20 '14 #13
Xenver
20
I've scrubbed the database of real info added some fillers, i tried to remove all logos and identifying stuff, if you see any please let me know.

The database is still under construction so there are some buttons and stuff that don't work yet.

A copy of my table and form structre is attached.

Thanks,

X
Attached Files
File Type: zip LEED Project Database ex for twinnyfo.zip (2.52 MB, 122 views)
Aug 20 '14 #14
twinnyfo
3,653 Expert Mod 2GB
I'll have to look at this later tonight, as I can't open this at work.
Aug 20 '14 #15
twinnyfo
3,653 Expert Mod 2GB
Xenver,

From a database design perspective, it is clear that your data is not fully normalized. I recommend you look at the following topic for further information:

Database Normalization

As a general rule, your tables should be set up based on your projects table.

Expand|Select|Wrap|Line Numbers
  1. Field          Type  Notes
  2. ==========================
  3. ProjectKey     Long  Autonumber (PK)
  4. ProjectNumber  Text
  5. etc.
Then, your Credits table should merely list all the types of Credits available:

Expand|Select|Wrap|Line Numbers
  1. Field        Type  Notes
  2. ========================
  3. CreditKey    Long  Autonumber (PK)
  4. CreditCode   Text  (SS 1, SS 2, SS 3, etc.)
  5. CreditName   Text  (Scent Free Policy, Green Cleaning, etc.)
  6. Then you have a couple field with "?" and "N", whatever those are for
Your Project Credits Table would then be:

Expand|Select|Wrap|Line Numbers
  1. Field          Type     Notes
  2. =============================
  3. ProjCredKey    Long     Autonumber (PK)
  4. ProjectNumber  Long     FK to tbl_Projects
  5. Credit         Long     FK to tbl_Credits
  6. CreditAmount   Integer  Keeps track of credits
  7. CreditNote     Text     Thus, no need for a separate Notes Table
  8. etc.
I know that these recommendations amount to a total rework of your project. However, the further you go down this particular road, the more complicated (and frustrating) your database will become.

Just think if you have to add a new credit type of if an old one goes away? You would have to completely re-engineer all your tables and queries and forms. However, if you keep all your credit types and names in one table, adding and removing becomes simple and routine.

Please forgive me if I end up "not answering" your original question. But, based on your DB design, I don't consider that any "answer" would solve your overall DB design challenges.

Granted, these are just my thoughts, but I am certainly open to other thoughts from other experts here. Trust me, I have been exactly where you have been and learned the very hard way that DB normalization is there for a reason.

I hope that this post will give you much fodder to munch on and bring you forward in your DB skills.
Aug 20 '14 #16
NeoPa
32,556 Expert Mod 16PB
As a general rule we don't encourage OPs to set their own posts as Best Answer. There are some exceptions, but simply realising the code of the original question had an invalid reference wouldn't be one I'm afraid.

Principally, the Best Answer is there to help people find the answer to their own problems quickly and easily.
Aug 22 '14 #17

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Geoff Matthews | last post by:
I apologize for the basic question, but MS's documentation has been no help at all. I'm working on a database, and need to create a form for schedules, M-F, 8-4. I've settled on an easy way out,...
9
by: Jack | last post by:
In the control source of a textbox, is there a way to refer to a column of a combobox? For example: =.Column(2) Thanks, Jack
1
by: Bart Lateur | last post by:
In an Access form, I'd like to show the status on whether an item as entered in a textbox (and thus in its coupled record field) exists in a list in another table. I've tried: - Making the...
1
by: anuradhacv | last post by:
When can we give control source of a textbox =1?
2
by: SF | last post by:
Hi, I am new to VB.NET. I try to start my first project linking to an Access database. On my Form1, I have two fields (bound), Subject and Author. This was done by draginf the table into the...
1
by: questionit | last post by:
Is it possible to set the control source of a textbox to more than 1 field from 1- single table ? 2- more than 1 table ? Thanks
6
by: colin spalding | last post by:
Access 2003 I subform which lists financial transactions for a client in the main form, which i total in a textbox named "txtTotalPremium" in the subform footer with the Control Source "=Sum()";...
4
by: colin spalding | last post by:
Access 2003 I posted this last week; unfortunately, none of the suggested solutions cured the problem I have a subform which lists financial transactions for a client in the main form, which...
4
by: Lou O | last post by:
Is it possible to use the row (index) of a list box as control source Property for a text box? Example: Text1.ControlSource Property is set to "= List1.Column(0,2)" in design view. When I open...
15
beacon
by: beacon | last post by:
Hi everybody, Using Access 2003. I'm trying to dynamically set the record source for the current form and set the control source for a text box to input data to one of three tables. I have a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.