Solved Store data from text box to control source (3 Viewers)

[TMK]

So I ave a form were, one text box consist of a control source equation, in this equation it is a If statement that auto updates as another 2 fields in the form change. Now I want this data calulated from the If statement also to be stored in another textbox where the control source is a field in a query.

I USED THE AFTER UPDATE AND ON CHANGE EENT BUT IT DOESNOT WORK, IT CHANGES ONLY IF U MANULLY CHANGE OR UPDATE IT NOT AUOMATICALLY FORM THE EQUATION.
how do i do it?

 

[MajP]

Is it possible that it is updating, but you do not see it because the form might need to refresh. Please provide the after update code.
Try adding in the after update
me.refresh

That assumes the change was made, but not displayed. If the change was never made we would need to see the after update.

 

[pbaldy]

You would need to use the update events of the 2 controls that feed the calculation. As you've found, the update event of the control with the calculation doesn't fire.

 

[TMK]

Is it possible that it is updating, but you do not see it because the form might need to refresh. Please provide the after update code.
Try adding in the after update
me.refresh

That assumes the change was made, but not displayed. If the change was never made we would need to see the after update.

I will try and lyk
Thanx!

 

[TMK]

So what must happen here is the Iff Statement textbox(BalanceStock) will display "possible" or "not", is quantity smaller than stock available on and WHAT I NEED IS after tht answer comes its status "possible" or "not" must be pasted in textbox(Stock), automatically after BlanceStock value changes.

 

[TMK]

You would need to use the update events of the 2 controls that feed the calculation. As you've found, the update event of the control with the calculation doesn't fire.

yes, it doesn't fire, but if i remove the if statement and make the text box unbound, and i enter data manually, the after update event works, but not with an automatic calculation like if statement

 

[The_Doc_Man]

"I USED THE AFTER UPDATE AND ON CHANGE EENT BUT IT DOESNOT WORK, IT CHANGES ONLY IF U MANULLY CHANGE OR UPDATE IT NOT AUOMATICALLY FORM THE EQUATION."

When the control source for a text box is not a bound field, but rather is an expression (formula), it is read-only. From this article:

TextBox.ControlSource property (Access)

Office VBA reference topic

learn.microsoft.com

"If the ControlSource property setting is an expression, the value displayed is read-only and not saved in the database."

 

[pbaldy]

yes, it doesn't fire, but if i remove the if statement and make the text box unbound, and i enter data manually, the after update event works, but not with an automatic calculation like if statement

Read the first sentence of my post again.

 

[MajP]

I understand the issue now and that is normal behavior per MS

AfterUpdate macros and event procedures run only if you change the data in a control. This event does not occur when a value changes in a calculated control. AfterUpdate macros and event procedures for a form run only if you change the data in one or more controls in the record.

Follow @pbaldy advice.

 

[TMK]

Read the first sentence of my post again.

ohh ryhttt, i got u, lemme try tht

 

[June7]

Why save this calculated value to table?

 

[TMK]

Why save this calculated value to table?

i wanna use tht value in another calculation

 

[TMK]

You would need to use the update events of the 2 controls that feed the calculation. As you've found, the update event of the control with the calculation doesn't fire.

So I tried do what you said, but i encountered a new problem,the if statment uses the condition "StockOnHand<Quantity", here the Quantity is a stored value, but the "StockOnHand" is a dlookup value which is also not saved, what do ido then?

For this form i use a query(orderdtl) as a record source, and the "StockOnHand" uses a dlookup to fetch the data from another query(Stock), i could try to add the field "StockOnHand" from the "Stock" query to the "Orderdtl" query to store it in a field ryht, but the problem is i get vba errors after i join the 2 queries, and all the form calculations started to get #errors.

 

[The_Doc_Man]

It always depends on the design of the DB, but generally we recommend a "transaction" style of inventory where you keep all inventory adds and removes, and using a SUM() database aggregate, you compute the balance.

If you search this forum for "Inventory" you will find hundreds of entries and dozens of lengthy discussions on why this is a preferred method. Storing a "stock-on-hand" value is usually NOT the best choice because of the possibility of failing to update that stock value and thus getting the item count out of sync with reality.

 

[GPGeorge]

It always depends on the design of the DB, but generally we recommend a "transaction" style of inventory where you keep all inventory adds and removes, and using a SUM() database aggregate, you compute the balance.

If you search this forum for "Inventory" you will find hundreds of entries and dozens of lengthy discussions on why this is a preferred method. Storing a "stock-on-hand" value is usually NOT the best choice because of the possibility of failing to update that stock value and thus getting the item count out of sync with reality.

In the Northwind Developer Edition template, we demonstrate a method of maintaining inventory that depends on regular stocktakes to minimize the number of records that must be included in the calculation of adds and removes to improve performance.

Periodic stocktakes provide accurate On-hand counts as of the date of the stock take. That allows you to sum from only those orders (sold) and purchase orders(bought) since that last stocktake date. This minimizes the number of records in the aggregate calculations to speed things up.

Because the regular stocktakes also account for inventory shrinkage or other unexplained changes to stock in the warehouse, it provides a more accurate picture than sales and purchases only would give you.

This method is documented by Allen Browne on his website.

You can also install Northwind Developer Edition template in Access and study the implementation there.

 

[TMK]

i was able to bypass the error with a timer event, it works as desired!