Galisteo8
asked on
Resync preventing form navigation?
I have an Access ADP -- Access 2000 front-end with SQL 2000 back-end.
On one of my forms there are several controls whose values can be updated by users, and each of them run a couple of lines of code in their AfterUpdate event before invoking the same function called RecalcRequery. Among other things, the RecalcRequery function "refreshes" the form with a Resync command. (I used to use Refresh, but in an ADP that bounces the form back to the first record in its recordset, and the bigger the recordset got the longer the Refresh took.)
It was after I switched to the Resync approach that the problem described below started.
Two controls (call them EbayListFee and CCheck) each invoke the RecalcRequery code in their AfterUpdate events. When the user updates EbayListFee, its AfterUpdate runs and everything seems fine, and then the user can search for another record to look at by clicking in a control and using Ctrl-F. That's all fine, it works great. BUT.......... if the user updates CCheck, and then its AfterUpdate event runs, and then the user tries to search for another record, the form remains "stuck" on the current record. It can't find another record, nor can the user advance through records using the nav buttons. It is simply stuck. The form must be closed and re-opened.
Now, the odd thing about it is that the EbayListFee and CCheck AfterUpdates are nearly identical -- remember, they BOTH invoke the same RecalcRequery function! So, why would one "stick" my form and the other one not? All the calculations and updates occur without incident; the only problem seems to be going to a new record after using CCheck.
Below is my code. This is driving me nuts -- please help! Sorry for the length, but I've posted the AfterUpdate events for both EbayListFee and Ccheck, as well as the RecalcRequery function which they both call.
--Galisteo8
--------------------------
EbayList Fee AfterUpdate---------------
Private Sub EbayListFee_AfterUpdate()
If Not IsNull(Me.WinBid.Value) Then
Me.Dirty = False
RecalcRequery
End If
End Sub
--------------------------
CCheck AfterUPdate---------------
Private Sub CCheck_AfterUpdate()
If Me![CCheck] = True Then
Me![CCFee] = Me![WinBid] * 0.03
Else: Me![CCFee] = 0
End If
Me.Dirty = False
RecalcRequery
End Sub
--------------------------
The RecalcRequery function------------------
Private Sub RecalcRequery()
'Recalcs AuctionGross and Lot GrossProfit as needed, updating those values via a SQL transaction
'and also requeries Access forms to refresh data in AuctionForm and LotForm (if open).
Dim cn As ADODB.Connection
Dim wRS_Auction As ADODB.Recordset
Dim wRS_Lot As ADODB.Recordset
Dim wRS_Client As ADODB.Recordset
Dim wPayback As Double
Dim wAuGross As Double
Dim wLtGross As Double
Dim wClProfit As Double
Dim wRprofit As Double
Dim wPercent As Double
wPayback = 0
wAuGross = 0
wLtGross = 0
wClProfit = 0
wRprofit = 0
wPercent = 0.01
Set cn = CurrentProject.Connection ' Connects to SQL database to update LOT and AUCTION tables
cn.CursorLocation = adUseServer 'to ensure lock on recordset
'Open and lock the records
Set wRS_Auction = New ADODB.Recordset
Set wRS_Auction.ActiveConnecti
wRS_Auction.LockType = adLockPessimistic
wRS_Auction.CursorType = adOpenDynamic
wRS_Auction.Open "SELECT * FROM Auction where EbayNum = '" & Me.EbayNum.Value & "'"
Set wRS_Lot = New ADODB.Recordset
Set wRS_Lot.ActiveConnection = cn
wRS_Lot.LockType = adLockPessimistic
wRS_Lot.CursorType = adOpenDynamic
wRS_Lot.Open "select * from Lot where LotNum = '" & Me![LotNum] & "'"
Set wRS_Client = New ADODB.Recordset
Set wRS_Client.ActiveConnectio
wRS_Client.LockType = adLockPessimistic
wRS_Client.CursorType = adOpenDynamic
wRS_Client.Open "select ProfitSplit, RinSplit, AuctPercent FROM Client INNER JOIN LOT ON CLIENT.FirstName = LOT.Client WHERE LotNum = '" & Me![LotNum] & "'"
cn.BeginTrans
If ((Me![WinBid] > 0) And (Me![TotalPaid] >= Me![WinBid])) Then
wAuGross = Nz(Me![WinBid], 0) - Nz(Me![EbayListFee], 0)
wAuGross = wAuGross - Nz(Me![EbayComm], 0)
wAuGross = wAuGross - Nz(Me![CCFee], 0)
wRS_Auction!Gross = wAuGross
wRS_Auction!PaidStatus = True
wRS_Auction!ClientRefund = 0
wRS_Auction!RinnerRefund = 0
wRS_Auction.Update
Else
wRS_Auction!Gross = 0
wRS_Auction!PaidStatus = False
wRS_Auction!ClientRefund = 0
wRS_Auction!RinnerRefund = 0
wRS_Auction.Update
wRS_Lot!GrossProfit = 0
wRS_Lot!Payback = 0
wRS_Lot!RinnerProfit = 0
wRS_Lot!LotPaid = False
wRS_Lot.Update
End If
If (Nz(wRS_Lot.Fields("Client
wLtGross = (wRS_Auction!Gross - wRS_Lot.Fields("ClientPric
If wRS_Client.Fields("AuctPer
wClProfit = Round(((wLtGross - (wPercent * wRS_Auction!Gross)) * wRS_Client.Fields("ProfitS
'Subtracts wPercent of AuctionGross from LotGross before calculating profit figures
Else 'Normal profit calc
wClProfit = Round((wLtGross * wRS_Client.Fields("ProfitS
End If
'special loss calc scenario for 5lot-Paul
If wLtGross <= 0 And wRS_Lot.Fields("Client") = "5lot - Paul" Then
wClProfit = wLtGross
End If
wPayback = (wRS_Lot.Fields("ClientPri
If Nz(wRS_Client.Fields("RinS
wRprofit = wLtGross * wRS_Client!RinSplit
Else
wRprofit = wLtGross - wClProfit
End If
wRS_Lot!GrossProfit = wLtGross
wRS_Lot!Payback = wPayback
wRS_Lot!RinnerProfit = wRprofit
wRS_Lot!LotPaid = True
wRS_Lot.Update
Else
wRS_Lot!GrossProfit = 0
wRS_Lot!Payback = 0
wRS_Lot!RinnerProfit = 0
wRS_Lot!LotPaid = False
wRS_Lot.Update
End If
wRS_Auction.Close
Set wRS_Auction = Nothing
wRS_Lot.Close
Set wRS_Lot = Nothing
wRS_Client.Close
Set wRS_Client = Nothing
cn.CommitTrans
cn.Close
Set cn = Nothing
' Requery LotForm, if it's open (determined in the clsContext module), in order to
' send new values to LotForm controls immediately
Dim oF1 As Form
If goContext.isLotFormLoaded(
Set oF1 = goContext.anLotForm
oF1.Recordset.Resync adAffectCurrent, adResyncAllValues
End If
' Requery AuctionForm to send new values to AuctionForm controls
Dim of2 As Form
Set of2 = goContext.anAuctionForm
of2.Recordset.Resync adAffectCurrent, adResyncAllValues
End Sub
On one of my forms there are several controls whose values can be updated by users, and each of them run a couple of lines of code in their AfterUpdate event before invoking the same function called RecalcRequery. Among other things, the RecalcRequery function "refreshes" the form with a Resync command. (I used to use Refresh, but in an ADP that bounces the form back to the first record in its recordset, and the bigger the recordset got the longer the Refresh took.)
It was after I switched to the Resync approach that the problem described below started.
Two controls (call them EbayListFee and CCheck) each invoke the RecalcRequery code in their AfterUpdate events. When the user updates EbayListFee, its AfterUpdate runs and everything seems fine, and then the user can search for another record to look at by clicking in a control and using Ctrl-F. That's all fine, it works great. BUT.......... if the user updates CCheck, and then its AfterUpdate event runs, and then the user tries to search for another record, the form remains "stuck" on the current record. It can't find another record, nor can the user advance through records using the nav buttons. It is simply stuck. The form must be closed and re-opened.
Now, the odd thing about it is that the EbayListFee and CCheck AfterUpdates are nearly identical -- remember, they BOTH invoke the same RecalcRequery function! So, why would one "stick" my form and the other one not? All the calculations and updates occur without incident; the only problem seems to be going to a new record after using CCheck.
Below is my code. This is driving me nuts -- please help! Sorry for the length, but I've posted the AfterUpdate events for both EbayListFee and Ccheck, as well as the RecalcRequery function which they both call.
--Galisteo8
--------------------------
EbayList Fee AfterUpdate---------------
Private Sub EbayListFee_AfterUpdate()
If Not IsNull(Me.WinBid.Value) Then
Me.Dirty = False
RecalcRequery
End If
End Sub
--------------------------
CCheck AfterUPdate---------------
Private Sub CCheck_AfterUpdate()
If Me![CCheck] = True Then
Me![CCFee] = Me![WinBid] * 0.03
Else: Me![CCFee] = 0
End If
Me.Dirty = False
RecalcRequery
End Sub
--------------------------
The RecalcRequery function------------------
Private Sub RecalcRequery()
'Recalcs AuctionGross and Lot GrossProfit as needed, updating those values via a SQL transaction
'and also requeries Access forms to refresh data in AuctionForm and LotForm (if open).
Dim cn As ADODB.Connection
Dim wRS_Auction As ADODB.Recordset
Dim wRS_Lot As ADODB.Recordset
Dim wRS_Client As ADODB.Recordset
Dim wPayback As Double
Dim wAuGross As Double
Dim wLtGross As Double
Dim wClProfit As Double
Dim wRprofit As Double
Dim wPercent As Double
wPayback = 0
wAuGross = 0
wLtGross = 0
wClProfit = 0
wRprofit = 0
wPercent = 0.01
Set cn = CurrentProject.Connection ' Connects to SQL database to update LOT and AUCTION tables
cn.CursorLocation = adUseServer 'to ensure lock on recordset
'Open and lock the records
Set wRS_Auction = New ADODB.Recordset
Set wRS_Auction.ActiveConnecti
wRS_Auction.LockType = adLockPessimistic
wRS_Auction.CursorType = adOpenDynamic
wRS_Auction.Open "SELECT * FROM Auction where EbayNum = '" & Me.EbayNum.Value & "'"
Set wRS_Lot = New ADODB.Recordset
Set wRS_Lot.ActiveConnection = cn
wRS_Lot.LockType = adLockPessimistic
wRS_Lot.CursorType = adOpenDynamic
wRS_Lot.Open "select * from Lot where LotNum = '" & Me![LotNum] & "'"
Set wRS_Client = New ADODB.Recordset
Set wRS_Client.ActiveConnectio
wRS_Client.LockType = adLockPessimistic
wRS_Client.CursorType = adOpenDynamic
wRS_Client.Open "select ProfitSplit, RinSplit, AuctPercent FROM Client INNER JOIN LOT ON CLIENT.FirstName = LOT.Client WHERE LotNum = '" & Me![LotNum] & "'"
cn.BeginTrans
If ((Me![WinBid] > 0) And (Me![TotalPaid] >= Me![WinBid])) Then
wAuGross = Nz(Me![WinBid], 0) - Nz(Me![EbayListFee], 0)
wAuGross = wAuGross - Nz(Me![EbayComm], 0)
wAuGross = wAuGross - Nz(Me![CCFee], 0)
wRS_Auction!Gross = wAuGross
wRS_Auction!PaidStatus = True
wRS_Auction!ClientRefund = 0
wRS_Auction!RinnerRefund = 0
wRS_Auction.Update
Else
wRS_Auction!Gross = 0
wRS_Auction!PaidStatus = False
wRS_Auction!ClientRefund = 0
wRS_Auction!RinnerRefund = 0
wRS_Auction.Update
wRS_Lot!GrossProfit = 0
wRS_Lot!Payback = 0
wRS_Lot!RinnerProfit = 0
wRS_Lot!LotPaid = False
wRS_Lot.Update
End If
If (Nz(wRS_Lot.Fields("Client
wLtGross = (wRS_Auction!Gross - wRS_Lot.Fields("ClientPric
If wRS_Client.Fields("AuctPer
wClProfit = Round(((wLtGross - (wPercent * wRS_Auction!Gross)) * wRS_Client.Fields("ProfitS
'Subtracts wPercent of AuctionGross from LotGross before calculating profit figures
Else 'Normal profit calc
wClProfit = Round((wLtGross * wRS_Client.Fields("ProfitS
End If
'special loss calc scenario for 5lot-Paul
If wLtGross <= 0 And wRS_Lot.Fields("Client") = "5lot - Paul" Then
wClProfit = wLtGross
End If
wPayback = (wRS_Lot.Fields("ClientPri
If Nz(wRS_Client.Fields("RinS
wRprofit = wLtGross * wRS_Client!RinSplit
Else
wRprofit = wLtGross - wClProfit
End If
wRS_Lot!GrossProfit = wLtGross
wRS_Lot!Payback = wPayback
wRS_Lot!RinnerProfit = wRprofit
wRS_Lot!LotPaid = True
wRS_Lot.Update
Else
wRS_Lot!GrossProfit = 0
wRS_Lot!Payback = 0
wRS_Lot!RinnerProfit = 0
wRS_Lot!LotPaid = False
wRS_Lot.Update
End If
wRS_Auction.Close
Set wRS_Auction = Nothing
wRS_Lot.Close
Set wRS_Lot = Nothing
wRS_Client.Close
Set wRS_Client = Nothing
cn.CommitTrans
cn.Close
Set cn = Nothing
' Requery LotForm, if it's open (determined in the clsContext module), in order to
' send new values to LotForm controls immediately
Dim oF1 As Form
If goContext.isLotFormLoaded(
Set oF1 = goContext.anLotForm
oF1.Recordset.Resync adAffectCurrent, adResyncAllValues
End If
' Requery AuctionForm to send new values to AuctionForm controls
Dim of2 As Form
Set of2 = goContext.anAuctionForm
of2.Recordset.Resync adAffectCurrent, adResyncAllValues
End Sub
remove Me.Dirty=False and check if it works or behaves as before.
Aziz
Aziz
ASKER
!!!!
Experts -- I have coded my way to a solution!
!!!!
The problem, as has been noted in related thread https://www.experts-exchange.com/questions/21482128/Can't-search-from-a-field-form-is-stuck.html, centered on the unholy combination of a control value assignment (Me![Number2] = Me![Number1] * 0.03) in one control's AfterUpdate event, and a Resync statement in the form's OnActivate event. However, the controls in question were related to payments being entered into a subform. So... I automated the calculation of the CCFee in the subform.
It sure would be easier to see it than to describe it.... But it works just like my payment total, which Ragoran helped me with many months ago when I first got this AuctionForm designed: Payment amounts are totaled in the subform via a puplic property that makes the total available to a control on the main AuctionForm, where it can be accessed by the AuctionForm's RecalcRequery function. Likewise, credit card type payments are now being "counted" in a puplic property in the subform, and that total is made available to a control on the AuctionForm. If the count > 0, then the AuctionForm's RecalcRequery function calculates the CCFee, stores it in the underlying table, and the value updates to the form during a Resync statement at the end of RecalcRequery.
That's the gist of it, anyway. For business reasons, I needed to leave a Resync in the AuctionForm's OnActivate event, so the solution focused on getting that CCFee calculation out of the main form control events. I still don't know WHY the problem was occuring (especially since no one could duplicate it), but it is a problem no longer.
I appreciate everyone's attention to this "undocumented feature" of Access, especially as we eliminated every possibility for what was causing it.... ;) Since I found a solution (or, rather, a better way of coding my forms to eliminate the problem), I will submit for this question and https://www.experts-exchange.com/questions/21482128/Can't-search-from-a-field-form-is-stuck.html to be PAQ'd with the points refunded.
Experts -- I have coded my way to a solution!
!!!!
The problem, as has been noted in related thread https://www.experts-exchange.com/questions/21482128/Can't-search-from-a-field-form-is-stuck.html, centered on the unholy combination of a control value assignment (Me![Number2] = Me![Number1] * 0.03) in one control's AfterUpdate event, and a Resync statement in the form's OnActivate event. However, the controls in question were related to payments being entered into a subform. So... I automated the calculation of the CCFee in the subform.
It sure would be easier to see it than to describe it.... But it works just like my payment total, which Ragoran helped me with many months ago when I first got this AuctionForm designed: Payment amounts are totaled in the subform via a puplic property that makes the total available to a control on the main AuctionForm, where it can be accessed by the AuctionForm's RecalcRequery function. Likewise, credit card type payments are now being "counted" in a puplic property in the subform, and that total is made available to a control on the AuctionForm. If the count > 0, then the AuctionForm's RecalcRequery function calculates the CCFee, stores it in the underlying table, and the value updates to the form during a Resync statement at the end of RecalcRequery.
That's the gist of it, anyway. For business reasons, I needed to leave a Resync in the AuctionForm's OnActivate event, so the solution focused on getting that CCFee calculation out of the main form control events. I still don't know WHY the problem was occuring (especially since no one could duplicate it), but it is a problem no longer.
I appreciate everyone's attention to this "undocumented feature" of Access, especially as we eliminated every possibility for what was causing it.... ;) Since I found a solution (or, rather, a better way of coding my forms to eliminate the problem), I will submit for this question and https://www.experts-exchange.com/questions/21482128/Can't-search-from-a-field-form-is-stuck.html to be PAQ'd with the points refunded.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you comment out RecalcRequery in the CCheck_AfterUpdate procedure, is the problem still there ?
If you comment out everything except the M.Dirty & RecalcRequery lines, is the problem still there ?