By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,782 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

Using ControlSource Property with an expression

100+
P: 675
I have a textbox on a continuous form that I want to display a calculated value. I have set the ControlSource Property = [text1] & "~" & [text2].

I would like to have my user be able to correct this value, and I will un-calculate it using VBA code.

According to Access(2000) Help, I may have an expression for a ControlSource, "The control displays data generated by an expression. This data can be changed by the user but isn't saved in the database." This is good, but I can't make it work. Events such as LostFocus, Click, MouseDown, Enter, Exit can be trapped, but nothing can be entered into the textbox. I get an attractive chime sound.

Of course, my actual problem is more complex, but this simple example illustrates it nicely. Table:
1 AAA aaa
2 BBB bbb
3 XXX xxx
Form has 3 textboxes, with ControlSources = text1, text2, and [text1] & "~" & [text2]

Any help here?

OldBirdman
Dec 11 '08 #1
Share this Question
Share on Google+
14 Replies


ADezii
Expert 5K+
P: 8,638
@OldBirdman
The syntax for changing the Control Source to an Expression for
<some field> on <some form> is:
Expand|Select|Wrap|Line Numbers
  1. Forms!<some form>![<some field>].ControlSource = "=[Text1] & '~' & [Text2]"
P.S. - You can, in fact, have the Control Source 'persist' if you so desire.
Dec 11 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
Help is incorrect! The Control Source is the Control Source! It cannot be changed by the user or thru code.

To do what you want and still have it editable, you need to assign the value using the AfterUpdate events of the two textboxes:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Text1_AfterUpdate()
  2.  If Not IsNull(Me.Text2) Then
  3.    Me.Text3 = [Text1] & "~" & [Text2]
  4.  End If
  5. End Sub
  6.  
  7. Private Sub Text2_AfterUpdate()
  8.   If Not IsNull(Me.Text1) Then
  9.    Me.Text3 = [Text1] & "~" & [Text2]
  10.  End If
  11. End Sub
This also allows the control to be saved to the underlying table if need be, and on a Continuous form (as well as Datasheet) it has to be bound to a field in the table if you want it to be tied to a given record. Otherwise, the value of Text3 for one record is the value of Text3 for all records!
Dec 11 '08 #3

ADezii
Expert 5K+
P: 8,638
@missinglinq
Hello Linq, unless I am totally reading this Thread incorrectly, the Control Source can most definately be changed in the manner in which I described and under the OPs condition, namely:
"The control displays data generated by an expression. This data can be changed by the user but isn't saved in the database."
The Expression will also generate unique values for each Record in the Continuous Form.
Dec 11 '08 #4

missinglinq
Expert 2.5K+
P: 3,532
That right, the Control Source can be changed by code, as you've indicated, but from the OP I, perhaps mistakenly, thought he was simply talking about assigning another value to it, not actually changing the value.

And despite what Help says, if you have an expression set as a Control Source, the user cannot edit the control by going into the filed and entering data.

Linq ;0)>
Dec 11 '08 #5

FishVal
Expert 2.5K+
P: 2,653
Fathers,

I have a feeling that there is no robust method to do it on continuous form.
Is it worth all efforts at all?

Regards,
Fish.
Dec 11 '08 #6

missinglinq
Expert 2.5K+
P: 3,532
As I said in my original post, it has to be bound in order to work on a Contnuous form, which means that the OP would be storing redundant information. While valid arguments can be made at times for storing calculated values, doing so for what appears to be display purposes only is not one of them.

Linq ;0)>
Dec 11 '08 #7

FishVal
Expert 2.5K+
P: 2,653
@missinglinq
Amen.

Of course, my actual problem is more complex, but this simple example illustrates it nicely. Table:
1 AAA aaa
2 BBB bbb
3 XXX xxx
Form has 3 textboxes, with ControlSources = text1, text2, and [text1] & "~" & [text2]
Could you anyway tell us your actual problem. It could be well no perfect solution here.

Regards,
Fish
Dec 11 '08 #8

100+
P: 675
Thank you for all your attention to this.

The user sees text3, which contains a calculated value. Text1 and text2 may have .visible=false. I presented a very simple table, but text1 might be LastName and text2 might be FirstName. Then text3.ControlSource = text1 & ", " & text2. User enters "Travis James" in text3 and the BeforeUpdate event would assign text1="James" and text2="Travis". User enters "Travis, James" and text1="Travis" and text2="James".

Post #2 - is correct, the ControlSource can be set with VBA code with the syntax shown. This misses this point. The formula can be done in design view, no code necessary. I'm not sure what 'persist' does.

Post #3 - Sorry, but this is incorrect. ControlSource can be changed thru code. The code presented does not do what I asked, which was "I would like to have my user be able to correct this value", which is the calculated field text3. Also, Access is correct with continuous forms, and each record has its own correctly calculated value in text3.

Post #4 - This makes the same observation that I do in my Post#3 comment above.

Post #5 - Again, I want a field to display a calculated value, and I want the user to be able to enter a value into that field. I will then parse the entry, and assign new values to text1 and text2.

On SingleForm view, this can be done using an unbound textbox. Text3 would be updated when the form's OnCurrent event occurred, and text1 and text2 would be updated when text3.AfterUpdate occurred. For continuous forms, this does not work, as all occurrences of text3 would contain the same value.

I notice that for Access 2003 VBA "http://msdn.microsoft.com/en-us/library/aa224120(office.11).aspx" has the same statement that data can be changed but is not saved.

Post #6 - I thought it was worth it, or I wouldn't have asked.

Post #7 - If I wanted to display names in FirstName <space> LastName order, but ORDER BY LastName, FirstName I would need a display field. If my data came from another source (Internet, text document, etc.) and was entered by Copy - Paste, the display/entry field would be useful (but not required).

Post #8 - Perfect solution? The only solution presented is to create a display field in the table, which is the largest field in the table. Am I to believe that Access Help is wrong, "msdn.microsoft.com/en-us/library" for Access 2003 VBA is wrong, or that a bug has existed for about a decade?

Actual problem is for a kiosk in a movie rental store, and the updating of the database will be done by the store clerks. Customer scans alphabetic list of titles, selects one, and gets Store Location, MPAA Rating, etc. Clerk must deal with complex titles. I thought I could use a subform, continuous forms, to show the first letter and the name as it should appear. Clerk could add as many alternate names as desired, probably cut/paste from original name but some movies have multiple titles) and I would parse it, capitalize it, and decide display letter(L~). Clerk could override.

Movie #1 - 3 Display Names - Cut/Paste from original name
N~ National Geographic: Inside American Power: The White House
I~ Inside American Power: The White House
W~ The White House

Movie #2 - 4 Display Names - Cut/Paste from original name
N~ National Geographic: Beyond the Movie: The New World: Nightmare in Jamestown
B~ Beyond the Movie: The New World: Nightmare in Jamestown
N~ The New World: Nightmare in Jamestown
N~ Nightmare in Jamestown

Movie #3 - 1 Display Name - Remove leading article(The)
F~ The Fight for the White House

Movie #4 - 2 Names - Entered separately
S~ Street King
K~ King Rikki

Movie #5 - 2 Names - Entered separately, one with leading article(The)
S~ Serenity
F~ The Firefly Movie

Movie #6 - Simple Name - Remove leading article(The)
P~ The Patriot

Store patron selects "S" on touchscreen and gets:
"Serenity"
"Street King"
On touch screen, Customer selects from this list

OK, so that is the actual problem. I didn't think you all wanted to have to wade thru all this, as solving my simple example would solve the problem. For my personal use, I get the movie titles from the internet, paste into a textbox, and parse. For movies with multiple names, I use continuous forms so I can see all the names at a glance (usually <4).
Dec 11 '08 #9

FishVal
Expert 2.5K+
P: 2,653
Hello, O~ Old:Bird:Man. ;)

Is that
National Geographic: Inside American Power: The White House
text entered by user or it is calculated one?

If so, then the problem is quite opposite to that you've initially asked.
The text as it entered should be considered as primary data source.
Master letter could be determined via simple logic and optionally stored if user is supposed to have final decision on that.

But ... hmm ...

From what you've posted I've got a feeling that your table structure is not optimal.
Would you like to discuss it?

Regards,
Fish.
Dec 11 '08 #10

100+
P: 675
Entered by User. In this case, text1 is txtFirstLetter="N", text2 is txtArticle="", txtTitle="National Geographic: Inside American Power: The White House" and txtDisplay = "=txtArticle & txtTitle".
But user then enters the other 2 titles implied by this title, and "The White House" becomes txtFirstLetter="W", txtArticle="The ", txtTitle="White House" so txtDisplay="The White House". If user changed (txtDisplay)(if he could) the word "The" to "a", then txtArticle would change to "A ", so txtDisplay would change, overwriting user's "a White House" with "A White House".
I can force this on SingleForms, but not on ContinuousForms.

OldBirdman
Dec 11 '08 #11

missinglinq
Expert 2.5K+
P: 3,532
@OldBirdman
You've proved this yourself, by the inability of the user to edit the data. And yes, there are bugs that have existed for a decade or more! DoCmd.Close comes to mind. It'll dump a record that fails validation without giving the user any warning. And mistakes in Access Help are legion in number.

Linq ;0)>
Dec 11 '08 #12

FishVal
Expert 2.5K+
P: 2,653
@OldBirdman
IMHO

[txtTitle] - should be a primary data source entered by user and stored in table as is
[txtFirstLetter] - should be calculated and, if final decision is upon user, then stored in table
[txtArticle] - I have no idea how it could be useful


Regards,
Fish
Dec 11 '08 #13

100+
P: 675
OK - Let's quit this thread. I have experimented with using a query with a calculated field, but same problems arise. I just thought maybe I didn't understand controls with calculated values, but apparently I understand very well.

OldBirdman
Dec 11 '08 #14

missinglinq
Expert 2.5K+
P: 3,532
As I said, you'd already found out the answer; you just wanted to be sure it was Access and not you! And guess what? It's Access! It's just one of those limitations you've got to work within.

I'll close the thread now.

Good luck with the app!

Linq ;0)>
Dec 11 '08 #15

This discussion thread is closed

Replies have been disabled for this discussion.