Update question

[djeeten]

Hi,

I have this form and subform.

For each record on the subform, a value has to be filled in (amount).

If you fill in a value in a textfield in the subform and then hit the tab key, or place the cursor to another field on the subform/main form, the records in the tables are updated automatically.

But, when I fill in a value in a textfield in the subform and then press a button(confirm) on the main form(with the cursor still being in the textfield in the subform), the form(together with its subform of course) is closed and the tables are not updated correctly.

I've been trying to requery, refresh or move the focus, but no luck so far.

Any help is very welcome,

Thanks in advance,

dj.

 

[Trendsetter]

What is the code you are using behind the "Confirm" button?

 

[djeeten]

I just update some tables after clicking on the confirm button right now.

//Update the amounts of the products in table tblProducts
stSql = "UPDATE tblProducts INNER JOIN tblOrderlines ON tblProducts.ProductId = tblOrderlines.ProductId SET AmountInSupply = (AmountInSupply - tblOrderlines.AskedAmount) WHERE tblOrderlines.OrderId =" & Me!OrderId

//Update the asked amounts in table tblOrderlines (in case a customer would change the amount later)
stSql2 = "UPDATE tblOrderlines SET LastAmount = AskedAmount WHERE OrderId=" & Me!OrderId

//Update the budgets of the customers
stSql3 = "UPDATE tblBudget SET Budget ='" & Me!NewBudget & "' WHERE CustomerName ='" & Forms!frmLogon!CustomerName & "' AND ProductGroupId =" & Group

NewBudget is a calculated value: (budget before the order) -(the total amount of this order).

-----------
The main form is based on the tables tblOrders & tblCustomers, and the subform is based on the tables tblOrderlines & tblProducts.

When pressing the confirm button while the focus is still in a field(that has just been filled in/changed) on the subform, the values are saved correctly in the tables tblOrders, tblOrderlines and tblProducts, but the budget is not saved correctly. When the button is pressed, the budget is set to its original value (its value before the order).

--------------------

These are the calculations that take place:

On the subform:
---------------
* OrderlineTotal: there's a total amount for each orderline (each ordered product with its value and the asked amount). This total amount is: price * AskedAmount

* OrderTotal: there's also a total amount of the order. this is the sum of the total amounts of all orderlines on the subform.

On the main form:
-----------------

* OrderTotal2: this takes the value of OrderTotal on the subform.

* NewBudget: Budget(not a calculated field) - OrderTotal2

----------------------------------------

When the confirm button is pressed, the Budget is also set equal to NewBudget.

---------------------

Hope you understand it,

Thanks in advance,

dj.

 

[Trendsetter]

Quote1:
When the button is pressed, the budget is set to its original value (its value before the order).
Quote2:
When the confirm button is pressed, the Budget is also set equal to NewBudget.
NewBudget: Budget(not a calculated field) - OrderTotal2

These statements appear to be contradicting each other!

 

[djeeten]

Trendsetter said:
-----------------------------------
Quote1:
When the button is pressed, the budget is set to its original value (its value before the order).
Quote2:
When the confirm button is pressed, the Budget is also set equal to NewBudget.
NewBudget: Budget(not a calculated field) - OrderTotal2

These statements appear to be contradicting each other!
-----------------------------------

Answer:

I'm sorry for confusing you.

The action under Quote2 is happening because of my code.

The action under Quote1 takes place automatically. It is not something I want to happen! The action under Quote1 also only takes place when you have just filled in or changed a textfield on the subform, without moving the cursor from this textfield (so without moving the focus from the record you are updating on the subform).
-------------------------------------------------------

Let me give you an example:

You have choosen a product (from a listbox on the mainform) and it is added to the subform. Now, on the subform, you need to fill in the amount of products(AskedAmount) you wish to order in a textbox.

If you leave the cursor in the textbox where you just filled in the amount, and hit the confirm button on the main form, the Budget (also on the mainform) is not saved correctly. (Budget = NewBudget, but the NewBudget(a calculated value) gets the original value, and not the calculated value)

If you first hit the tab key (or move the focus) before pressing the confirm button, the Budget is saved correctly.
(Budget = NewBudget(the correct calculated value))

It's quite difficult to explain, but I hope you understand it a bit better.

Thanks in advance,

dj.

 

[djeeten]

I said:
-------------------------------------------
If you leave the cursor in the textbox where you just filled in the amount, and hit the confirm button on the main form, the Budget (also on the mainform) is not saved correctly. (Budget = NewBudget, but the NewBudget(a calculated value) gets the original value, and not the calculated value)
-------------------------------------------

Hi,

I just found out why this happens. If you hit the confirm button on the main form without moving the focus from the 'AskedAmount'field you just filled in/changed on the subform, the calculated field 'OrderTotal' on the subform is NULL. This is why the budget didn't change as well.

Any idea why this could happen?

Thanks in advance,

dj.

 

[Trendsetter]

It feels as though the field with the cursor focus is not writing to the tables and putting a Null into the tables - this would then have the knock effect as you describe.

You could force the system to check that there is an entry in that field and if there is to move the focus or do a save before completing the confirm button instructions.

Keep trying!!!!!!!!!!!!