Using ControlSource Property with an expression

[OldBirdman]

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

[ADezii]

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

The syntax for changing the Control Source to an Expression for
<some field> on <some form> is:

Forms!<some form>![<some field>].ControlSource = "=[Text1] & '~' & [Text2]"

P.S. - You can, in fact, have the Control Source 'persist' if you so desire.

[missinglinq]

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:

Private Sub Text1_AfterUpdate()
 If Not IsNull(Me.Text2) Then
   Me.Text3 = [Text1] & "~" & [Text2]
 End If
End Sub

Private Sub Text2_AfterUpdate()
  If Not IsNull(Me.Text1) Then
   Me.Text3 = [Text1] & "~" & [Text2]
 End If
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!

[ADezii]

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:

Private Sub Text1_AfterUpdate()
 If Not IsNull(Me.Text2) Then
   Me.Text3 = [Text1] & "~" & [Text2]
 End If
End Sub

Private Sub Text2_AfterUpdate()
  If Not IsNull(Me.Text1) Then
   Me.Text3 = [Text1] & "~" & [Text2]
 End If
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!

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.

[missinglinq]

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

[FishVal]

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.

[missinglinq]

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

[FishVal]

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

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

[OldBirdman]

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.ControlSo urce = 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.AfterUpda te 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).

[FishVal]

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.

[OldBirdman]

Entered by User. In this case, text1 is txtFirstLetter= "N", text2 is txtArticle="", txtTitle="Natio nal Geographic: Inside American Power: The White House" and txtDisplay = "=txtArticl e & 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

[missinglinq]

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?

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

[FishVal]

Entered by User. In this case, text1 is txtFirstLetter= "N", text2 is txtArticle="", txtTitle="Natio nal Geographic: Inside American Power: The White House" and txtDisplay = "=txtArticl e & 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

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

[OldBirdman]

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

[missinglinq]

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