Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-07-2018, 06:31 AM   #1
ZenDiagram
Newly Registered User
 
Join Date: Aug 2018
Posts: 37
Thanks: 23
Thanked 0 Times in 0 Posts
ZenDiagram is on a distinguished road
Can't edit fields in form that uses calculated controls

Greetings,

Here is my issue:
I can't edit certain fields on my form because they are part of a control's calculated expression ( i think that's what the problem is).

I created an underlying table with fields that don't need to be calculated.
I made a form from this table and added calculated controls that use the fields from the tables in their expressions.
Now I can't edit any of the fields on the form.

How can I get around this?

Thanks.

ZenDiagram is offline   Reply With Quote
Old 09-07-2018, 06:35 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,037
Thanks: 10
Thanked 3,862 Times in 3,805 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Can't edit fields in form that uses calculated controls

This should help:

http://allenbrowne.com/casu-14.html
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-07-2018, 06:45 AM   #3
ZenDiagram
Newly Registered User
 
Join Date: Aug 2018
Posts: 37
Thanks: 23
Thanked 0 Times in 0 Posts
ZenDiagram is on a distinguished road
Re: Can't edit fields in form that uses calculated controls

pbaldy,

Here is the issue though: I have close to, if not more than, 200 fields and a query would most likely not support that.
I know I shouldn't use Access like Excel, but in this case I have to.

ZenDiagram is offline   Reply With Quote
Old 09-07-2018, 06:54 AM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Can't edit fields in form that uses calculated controls

of course you can edit it, can you bring a sample.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-07-2018, 06:55 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,037
Thanks: 10
Thanked 3,862 Times in 3,805 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Can't edit fields in form that uses calculated controls

You have to, or you don't know how not to? I'm not sure how the number of fields affects this issue. Can you attach the db here?
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-10-2018, 09:45 AM   #6
ZenDiagram
Newly Registered User
 
Join Date: Aug 2018
Posts: 37
Thanks: 23
Thanked 0 Times in 0 Posts
ZenDiagram is on a distinguished road
Re: Can't edit fields in form that uses calculated controls

I'm going to make a query based on the table and add my calculated fields to the query.

My boss wants this feature as part of the database, so even if access isn't supposed to operate as an excel sheet, I will have to get around that somehow.

Question:
Once I create the aforementioned query, I will need to create a form as a user-friendly way to modify said query.
1) I do create the form based on the query, right? And not the table?
2) Will I be able to edit the fields on the form if they are bound to an expression/calculation?

I'm having a hard time cutting down my database to send as an example, so bear with me here.

Thanks.
ZenDiagram is offline   Reply With Quote
Old 09-10-2018, 09:51 AM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,847
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Can't edit fields in form that uses calculated controls

you can edit the rest of the field but not the calculated.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
ZenDiagram (09-10-2018)
Old 09-10-2018, 09:58 AM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,037
Thanks: 10
Thanked 3,862 Times in 3,805 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Can't edit fields in form that uses calculated controls

As a rule, you should create the database using good database design fundamentals, then create interfaces that allow the users to work with it the way they want.

1) Some people use queries exclusively behind forms. I don't have a problem using the table directly as long as the form is opened filtered so it isn't pulling all records.

2) No, you can't edit calculated fields, you have to edit the underlying fields. In other words, I can't edit a field that's the calculation Price*Quantity. I have to edit price or quantity.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-10-2018, 10:02 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Can't edit fields in form that uses calculated controls

How do you get up to 200 fields? Are you doing something like calculating x for each month of the year? Or calculating y for each department?

Maybe posting a picture of the form with data will help us to see your problem.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-11-2018, 07:27 AM   #10
ZenDiagram
Newly Registered User
 
Join Date: Aug 2018
Posts: 37
Thanks: 23
Thanked 0 Times in 0 Posts
ZenDiagram is on a distinguished road
Re: Can't edit fields in form that uses calculated controls

i get around 150 fields because I have many calculations I am performing.
2019 tax: ([assessed land value 2019]/ 1000) * [tax rate 2019]
from 2019-2015. plus many other calculations.

I started doing my calculated fields in a query but I accidentally clicked the "update" in the Query Type toolbox. Now all my fields are gone and it is functioning as a query update- with the little pencil and drill tool icon next to the query name.

How can I revert back?!?!
ZenDiagram is offline   Reply With Quote
Old 09-14-2018, 11:03 AM   #11
Jeffr.Lipton
Newly Registered User
 
Join Date: Sep 2018
Posts: 25
Thanks: 24
Thanked 1 Time in 1 Post
Jeffr.Lipton is on a distinguished road
Re: Can't edit fields in form that uses calculated controls

If you want a calculated field that you can edit, leave it unbound and in the appropriate events (after update for the fields used to calculate, and on Open for the form) add Me.[calculated_field] = [expression]. Having this field be editable is bad design, because then it's uncertain if the field contains the calculated value or the edited value.

It looks like you should have a separate table that has the Year as the primary key, and any data specific to that year (like assessed land value and tax rate) as columns.
Then use a sub-form to show the yearly values used.
Jeffr.Lipton is offline   Reply With Quote
The Following User Says Thank You to Jeffr.Lipton For This Useful Post:
ZenDiagram (09-14-2018)
Old 09-14-2018, 08:45 PM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Can't edit fields in form that uses calculated controls

Updating an unbound control is pointless since the value would never be saved.

Having bound and unbound controls on a form does not prevent the bound controls from being updateable. On the other hand, you can never type into a control that has a control source that starts with the "=" sign. Those controls are not bound but they are calculated.

You could leave the ControlSource empty making the control unbound and not calculated and populate the value using code. However, I'm not sure what good it would do you to type over a calculated value since it wouldn't be saved anyway.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
Jeffr.Lipton (09-15-2018)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Darn plus signs in calculated controls/fields... EternalMyrtle Forms 17 02-06-2014 12:19 PM
Calculated Controls on a Filtered Form jleval Forms 1 09-18-2012 10:51 PM
Can't edit query which has calculated fields capsula4 Queries 2 01-08-2008 04:33 AM
Can't edit controls in the Form ions Forms 5 12-26-2007 07:47 AM
Using Calculated fields in other controls - help please!! commandolomo Reports 2 03-03-2005 08:39 AM




All times are GMT -8. The time now is 09:46 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World