473,320 Members | 1,699 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,320 software developers and data experts.

Using ControlSource Property with an expression

675 512MB
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
14 9981
ADezii
8,834 Expert 8TB
@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
3,532 Expert 2GB
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
8,834 Expert 8TB
@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
3,532 Expert 2GB
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
2,653 Expert 2GB
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
3,532 Expert 2GB
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
2,653 Expert 2GB
@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
OldBirdman
675 512MB
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
2,653 Expert 2GB
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
OldBirdman
675 512MB
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
3,532 Expert 2GB
@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
2,653 Expert 2GB
@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
OldBirdman
675 512MB
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
3,532 Expert 2GB
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

28
by: Daniel | last post by:
Hello =) I have an object which contains a method that should execute every x ms. I can use setInterval inside the object construct like this - self.setInterval('ObjectName.methodName()',...
3
by: Shelli Carol | last post by:
Good day, I have: 1) A table with numbered fields (1, 2, 3, etc.) 2) A form text box, whose ControlSource property contains an expression. This expression returns a number. If I just...
6
by: CFW | last post by:
References in Access and .mdb files have always been painful for me - just when I thought I was OK in an .mdb, I have a .adp I'm working on and I need to use Set db as CurrentDb - Constant "object...
9
by: Colin McGuire | last post by:
Hi, I have an report in Microsoft Access and it displays everything in the table. One column called "DECISION" in the table has either 1,2, or 3 in it. On my report it displays 1, 2, or 3. I want...
0
by: Paul T. RONG | last post by:
Hello there, there is a text box txt0, its controlsource is =( & ", " & ) it works in one mdb, but not in another. Where is the problem? please help. Paul
2
by: Ian Hinson | last post by:
Hi, In an MDB report I was able to obtain an Orders total in a Group Footer by setting a control's ControlSource to: =Sum(*) In the Detail section of the report are controls bound to: (1)...
13
by: ringer | last post by:
Hi, I have a text box on report where I need to have dsum return a total. The records I need the total from are not in the table that is the report's recordsource, and to complicate things...
2
by: DC | last post by:
Hi, I am using a GridView to present data in a DataTable, which I store only in ViewState and when the user hits the "OK" button the rows in the DataTable will be used to execute transactions. ...
2
by: ARC | last post by:
I have a ranking report where I want to sort it different ways depending on the option the user picks. On the On_Open event, I've tried everything I can think of and keep getting error 3071 "This...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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...
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.