Update Working Onesided - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Update Working Onesided

Martin Fairbairn

12 months ago

I have the following 2 update commands

CurrentDb.Execute "UPDATE tblequity SET tblequity.Inventory = tblequity.Inventory - " & TQty & ", tblequity.EquityCost = tblequity.EquityCost - " & TPrice & " WHERE tblequity.pkEquity = " & Me.fkEquityOut, dbFailOnError

CurrentDb.Execute "UPDATE tblequity SET tblequity.Inventory = tblequity.Inventory + " & TQty & ", tblequity.EquityCost = tblequity.EquityCost + " & TPrice & " WHERE tblequity.pkEquity = " & Me.fkEquityIn, dbFailOnError

The first one works perfectly, the second updates nothing. My MsgBox confirms that the ID #s are right. Any suggestions. I would put more code but the system objects!!

Me.Posted = 1
        Me.fkCapital.SetFocus
        Me.btnPostTxn.Visible = False
    End If
    Me.Refresh
End Sub

	Alex Hedley @Reply 12 months ago
Try `Dim mySQL ...` `mySQL = "your sql"` `MsgBox mySQL` then `CurrentDb.Execute mySQL, dbFailOnError`

	Martin Fairbairn OP @Reply 12 months ago
Thank you, Alex. I tried that and it still didn't work. then I had the bright idea of changing the identity of the transferee and it worked perfectly :-) So the problem is not in the update procedure. It's somewhere in my record linking ....

	Martin Fairbairn OP @Reply 12 months ago
OK. I found the problem and it is the same in both my accounting updates and my share transaction updates. The update procedure works perfectly but the receiving field won't accept the update if it is Null. If it is 0, it accepts the update. Why is that? I guess I could set the fields to 0 on creation of the record with an OnCurrent procedure but what is the explanation for that issue?

	Alex Hedley @Reply 12 months ago
The idea was that using `MsgBox mySQL` you'd be able to see what SQL was being generated. You could then test it manually or look for possible issues.

	Kevin Robertson @Reply 12 months ago
Martin NZ Function

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View