Can't edit fields in form that uses calculated controls (1 Viewer)

ZenDiagram · Sep 7, 2018

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.

pbaldy · Sep 7, 2018

This should help:

http://allenbrowne.com/casu-14.html

ZenDiagram · Sep 7, 2018

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.

arnelgp · Sep 7, 2018

of course you can edit it, can you bring a sample.

pbaldy · Sep 7, 2018

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?

ZenDiagram · Sep 10, 2018

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.

arnelgp · Sep 10, 2018

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

pbaldy · Sep 10, 2018

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.

Pat Hartman · Sep 11, 2018

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.

ZenDiagram · Sep 11, 2018

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?!?!

Jeffr.Lipton · Sep 14, 2018

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.

Pat Hartman · Sep 15, 2018

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.

Can't edit fields in form that uses calculated controls (1 Viewer)

ZenDiagram

Registered User.

pbaldy

Wino Moderator

ZenDiagram

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

pbaldy

Wino Moderator

ZenDiagram

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

pbaldy

Wino Moderator

Pat Hartman

Super Moderator

ZenDiagram

Registered User.

Jeffr.Lipton

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread