When I close a form one value in one row in the underlying table gets changed

[samsquared]

Something strange is happening with a form that shows individual dividend payments and a total for the selected stock on the form. There are over 100 dividend transactions for about 10 stocks. Table tblDividends is pretty simple: autonumber column, ID that corresponds to the stock, date, amount, and a yes/no called "Exclude" (some I want to leave out for various reasons). Query qryDividends uses tblDividends columns StockID, date, amount and Exclude=No. It all works fine except for one specific situation.



Stock XXX has one positive dividend (like all the others) and one with a value of -$28.20 (withholding - Canadian stock). Here's the scenario

• tblDividends has the correct StockID link pointing to stock XXX
• I open frmDividendsByStock
• The stock selected in the combo box is XXX and the dividends listed are correct.
• I switch to stock YYY whose info is correct and close the form.
• When I look at tblDividends, the StockID for the -$28.20 row now points to stock YYY.
• When I re-open the form, the stock and info selected is YYY (with the erroneous -$28.20)
• I then switch to stock XXX (or ZZZ or any other) and close the form.
• When I look at tblDividends, the StockID now points to stock XXX (or ZZZ or the last stock before closing the form).

Here's what I've tried:

• Deleted and re-added the -$28.20; no change
• Added another negative dividend for XXX; it still modifies the -$28.20 row
• Deleted the -$28.20 entirely; it does the same thing for a different dividend from some other stock

I really have no idea why this is happening. As long as the form is up I can switch back and forth between stocks and tblDividends stays correct. The problem happens when I close the form.

Probably something simple...

[Micron]

If I follow correctly, the form is doing what it is supposed to do. As I see it,
- you open a form and the correct value is shown in the combo. That tells me the combo is bound to a field in some table.
- you select another value from that combo and close the form (one of the ways that a record is saved)
- you open the form and find that the value you last selected in the combo is now the value that is showing.
You are expecting something else to happen? When you change the combo value, you've edited the record IF you do anything to cause the edit to be saved. One thing that does that to any bound field that has been edited is to move off the record (e.g. navigate to another record) and certainly includes closing the form. If you don't want that to ever happen, don't bind the control to a field and use code/macro to save that field instead, or don't play with the data. If that combo is used only for searching/filtering records, it should not be bound to a field.

[Gicu]

I think you're going to have to post a sample db with the form but I suspect you have a "search" combo on the form to switch between stocks and that combo is bound to the stock field therefore editing the record. It should not be bound (search combos are unbound and placed in the header of the form to be searched).
Cheers,

[samsquared]

Wow - thanks for the quick replies, Micron and Vlad! The problem was that the combobox was bound to StockID. When I removed that it all started working correctly.

Vlad, this is the same form you helped me with on May 30. It was something simple.