Full Version: Update Control with a combination of 2 other Controls
UtterAccess Forums > Microsoft® Access > Access Forms
stefaroni
I've got three different controls on a form. I want to be able to update one with a combination of the data that is input into the other two, but only for the current record. I have no idea how to do it.
For example,
Control 1 = "8/13/07" Control 2 = "Contacted person, left message"
Control 3 = "8/7/07 Contacted person, everything fine"
I want to be able to push a command button on the form and have it update Control 3 to
"8/7/07 Contacted person, everything fine
8/13/07 Contacted person, left message"
I don't want it to override what's already in Control 3, just add on to it.
*I only want it to do this for the current record.*
Is it possible?
theDBguy
Welcome to UA!
You should be able to use:
Me.Control3 = Me.Control3 & Chr(13) & Me.Control1 & " " & Me.Control2
HTH.
stefaroni
That worked great in the form!
However, Control 3 is a field in a table, and I need that field to be updated as well. The above code made it possible to display what I want, but I need it to save in the table as well. Any ideas? I thought about an update query, but I don't know how to update just the current record with an update query.
Oh, the Chr(13) shows up as a box and doesn't start a new line...is that what's supposed to happen?
balaji
A carriage return line feed combination is chr(13) & chr(10) (in that order), not just chr(13). So, try:
e.Control3 = Me.Control3 & Chr(13) & chr(10) & Me.Control1 & " " & Me.Control2
theDBguy
Sorry about that. Balaji is right, you need to use the Chr(13) & Chr(10) combination. Is Control3 bound to a field in your table? If not, change its control source to your table field and it should update the table for you. Do not enter the code in the control box; put it in the AfterUpdate event of Control2. HTH.
stefaroni
Me.Control3 = Me.Control3 & Chr(13) & chr(10) & Me.Control1 & " " & Me.Control2
On the above expression, is Me. representative of the form name?
balaji
In VBA code, me. automatically refers to the object behind which the code is present. So, if you have this code behind a form, me. refers to properties and controls on that form.
stefaroni
I am ECSTATIC!!! Thank you so much - everything works perfectly!
theDBguy
Glad we could help. Good luck.
stefaroni
Okay, I've got one more for you that's pretty similar to what you came up with above.
I've got a control that's going to hold a date.
I want a button that will take that date and copy it into control1, but only if control1 is null. If control1 is not null, I want it to copy into control2 if it's null. If control2 is not null, copy to control3...and on and on. Does that make sense?
Any ideas?
NoahP
Sorry to tell you, but you should not be storing this data again in the 3rd control. It's not normalized and will wind up causing headaches sooner or later.
nytime you're storing the same data in more than one field of a record, you've 99.9999% likely to have structure problems that will cause no end of problems down the road. You really need to look at the structure and make sure it's properly normalized before continuing.
stefaroni
The thing is, the date field will be continually changing...but I need to record what was once there before it changes. The two controls will only hold the same data for a limited amount of time.
So, that being said, do you think this is possible?
NoahP
If you're recording a history, then you need a related history table with a date field and the comment. Then the 3 fields in your current table would not be necessary.
The link in my signature will take you to a post with a number of links on database design and normalization that can help you understand why you need to do this.
stefaroni
Thanks, I appreciate your help!
NoahP
You are welcome!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.