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.ActiveConnection = cn
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.ActiveConnection = cn
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("ClientPrice"), 0) > 0) And (wRS_Auction!Gross > 0) Then

      wLtGross = (wRS_Auction!Gross - wRS_Lot.Fields("ClientPrice"))
     
      If wRS_Client.Fields("AuctPercent") = True Then 'Tests for specific profit calc scenario
         wClProfit = Round(((wLtGross - (wPercent * wRS_Auction!Gross)) * wRS_Client.Fields("ProfitSplit")), 2)
         'Subtracts wPercent of AuctionGross from LotGross before calculating profit figures
      Else 'Normal profit calc
         wClProfit = Round((wLtGross * wRS_Client.Fields("ProfitSplit")), 2)
      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("ClientPrice") + wClProfit)
     
      If Nz(wRS_Client.Fields("RinSplit"), 0) > 0 Then 'Tests for specific Rinner profit calc scenario
         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() Then
   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
LVL 8
Galisteo8Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jmantha709Commented:
Is there any code on the CCFee control ?

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 ?
Sayad Aziz AhmadCommented:
remove Me.Dirty=False and check if it works or behaves as before.

Aziz
Galisteo8Author Commented:
!!!!
Experts -- I have coded my way to a solution!
!!!!

The problem, as has been noted in related thread http://www.experts-exchange.com/Databases/MS_Access/Q_21482128.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 http://www.experts-exchange.com/Databases/MS_Access/Q_21482128.html to be PAQ'd with the points refunded.
moduloCommented:
Closed, 250 points refunded.

modulo
Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.