Overruling Expression in Text Box.

[NBVC]

Ok, Docman you are probably right, I have some Forms 101 to catch up on. I am not an Access user.. more of an Excel guy... but not really with Forms there either.

I can link directly with the ERP tables.. that is really what I want to do... I want it to be a live interaction with the ERP system.. so if new customers are added, then the table is automatically up to date... and if I add a new quote it automatically populates the Quote table, so that someone view Quotes in the ERP database can see that new quotes are added..... unfortunately though.. there are some elements in the Access quote I want to create that the built in Quote function in the ERP does not have.. so only some elements of the Access Quote would be updated in the ERP "fixed" table.

Hope that makes sense.

 

[KenHigg]

I think you simply need to create a quote table with a foreign key back to the erp table. Then I'm thinking have a form based on the erp table with a subform based on the Access quote table - ? I'd think twice before you edited the erp data. In fact I'd be surprised if you actually could...

 

[The_Doc_Man]

Actually, here is enough for you to understand the decision to be made. (What you do about it afterwards is a different story.)

On a form, any item is either BOUND or UNBOUND. If it is bound, it has memory of what it was the last time you updated it. If it is unbound, it is a new thing each time you bring it up. You can have a bound item which has a default value that is computed from some other value. But the first time you save the record OR NAVIGATE AWAY FROM IT (therebe doing an implied SAVE), you have overridden the computation. Whether you wanted to or not. So that means that on a new record, in the absence of any way to track this override, you WILL perform an override based on just about anything you do.

OK, now here is one way you can retain the override choice last longer. Add a Yes/No flag to the table that tells you when an override has occurred for this particular field. If the flag says NO then continue to compute the value for the item when you bring it up (probably by testing it in the Form_Current event). It is possible to have an invisible field on a form, so your flag can be there when you need it. However, when you perform the override, a {textbox}_LostFocus or {textbox}_AfterUpdate event could flip that hidden flag to YES. When you save the record this time, you save the flag that says the override has occurred. By having the flag separate from the data, it no longer matters whether the computation changes between the first and second times you bring up the record. Or the hundredth time. You compute until the override flag says STOP.

The reason this is more complex than normal is a property called "atomicity" - meaning "indivisibility" - of the data element. The value you are storing for the control of interest - from either a formula or an override value - probably looks very much the same. In fact the default and override HAVE to resemble each other because you only have one format property for one textbox. There is no place in that box to store a very DIFFERENT fact - that an override has occurred. You need a new place to store that separate fact. You have to think of a record as holding "facts" (attributes) of whatever that record represents. If you change to a new record by navigation, the only way to know whether to display the data in the record or use the default formula would be to track the override flag with the rest of the record.

I hope this gives you more insight into what you are trying to do and why it is such a bear. The "atomicity" rule can be a toughie sometimes. But it exists for a reason.

 

[NBVC]

Actually, here is enough for you to understand the decision to be made. (What you do about it afterwards is a different story.)

On a form, any item is either BOUND or UNBOUND. If it is bound, it has memory of what it was the last time you updated it. If it is unbound, it is a new thing each time you bring it up. You can have a bound item which has a default value that is computed from some other value. But the first time you save the record OR NAVIGATE AWAY FROM IT (therebe doing an implied SAVE), you have overridden the computation. Whether you wanted to or not. So that means that on a new record, in the absence of any way to track this override, you WILL perform an override based on just about anything you do.

OK, now here is one way you can retain the override choice last longer. Add a Yes/No flag to the table that tells you when an override has occurred for this particular field. If the flag says NO then continue to compute the value for the item when you bring it up (probably by testing it in the Form_Current event). It is possible to have an invisible field on a form, so your flag can be there when you need it. However, when you perform the override, a {textbox}_LostFocus or {textbox}_AfterUpdate event could flip that hidden flag to YES. When you save the record this time, you save the flag that says the override has occurred. By having the flag separate from the data, it no longer matters whether the computation changes between the first and second times you bring up the record. Or the hundredth time. You compute until the override flag says STOP.

The reason this is more complex than normal is a property called "atomicity" - meaning "indivisibility" - of the data element. The value you are storing for the control of interest - from either a formula or an override value - probably looks very much the same. In fact the default and override HAVE to resemble each other because you only have one format property for one textbox. There is no place in that box to store a very DIFFERENT fact - that an override has occurred. You need a new place to store that separate fact. You have to think of a record as holding "facts" (attributes) of whatever that record represents. If you change to a new record by navigation, the only way to know whether to display the data in the record or use the default formula would be to track the override flag with the rest of the record.

I hope this gives you more insight into what you are trying to do and why it is such a bear. The "atomicity" rule can be a toughie sometimes. But it exists for a reason.

I think this should give me the ammo to get out of this project

It seems over my head for my current skill level... (close to Novice).

Maybe I will go back to my Excel routes and figure something out with Queries and formulas directly in the sheets...

Thanks for your input guys.