Solved After goto record return to the origin of the subroutine (1 Viewer)

JCRamirez

New member
Local time
Today, 17:33
Joined
Sep 15, 2020
Messages
14
I'm new in this Forum so I hope I'm posting in the correct place.

I'm working in a form but some how when I use the docmd for going to a record then the subroutine will start again. The interesting thing is that when I go to a new record the situation doesn't happen.

The code that I'm having is in a form that is a granchild form and will run when the form loads

My code is like this

Code:
Private Sub Form_Current()

Me.MinNetRateBox = ""
Me.MinSellingRateBox = ""
Me.Min45NetRateBox = ""
Me.Min45SellingRateBox = ""
Me.Plus45NetRateBox = ""
Me.Plus45SellingRateBox = ""
Me.Plus100NetRateBox = ""
Me.Plus100SellingRateBox = ""
Me.Plus300NetRateBox = ""
Me.Plus300SellingRateBox = ""
Me.Plus500NetRateBox = ""
Me.Plus500SellingRateBox = ""
Me.Plus1000NetRateBox = ""
Me.Plus1000SellingRateBox = ""
Me.PivotWeightBox = ""
Me.PivotNetRateBox = ""
Me.PivotSellingRateBox = ""

CountRec = DCount("*", "RateAirlineTable", "[RateAirlineQuotationid]=" & Forms![QuotationForm]![QuotationIDBox])
  
   If CountRec >= 7 Then (Point A)
      Me.AddRatesButton.Caption = "Save Rate"
      Me.ClearAllButton.Caption = "Delete Rate"
      Me.AirlineCombo.SetFocus
      DoCmd.GoToRecord , , acGoTo, 2 (After here will return to point A)
      Me.RateNumberBox = "Rate 01" (Point B)
      Me.MinNetRateBox = DLookup("NetRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='Min'")
      Me.MinSellingRateBox = DLookup("SellingRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='Min'")
      Me.Min45NetRateBox = DLookup("NetRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='-45'")
      Me.Min45SellingRateBox = DLookup("SellingRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='-45'")
      Me.Plus45NetRateBox = DLookup("NetRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='+45'")
      Me.Plus45SellingRateBox = DLookup("SellingRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='+45'")
      Me.Plus100NetRateBox = DLookup("NetRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='+100'")
      Me.Plus100SellingRateBox = DLookup("SellingRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='+100'")
      Me.Plus300NetRateBox = DLookup("NetRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='+300'")
      Me.Plus300SellingRateBox = DLookup("SellingRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='+300'")
      Me.Plus500NetRateBox = DLookup("NetRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='+500'")
      Me.Plus500SellingRateBox = DLookup("SellingRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='+500'")
      Me.Plus1000NetRateBox = DLookup("NetRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='+1000'")
      Me.Plus1000SellingRateBox = DLookup("SellingRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab='+1000'")
      Me.PivotWeightBox = DLookup("RateSlab", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab<>'Min'" & "AND RateSlab<>'-45'" & "AND RateSlab<>'+45'" & "AND RateSlab<>'+100'" & "AND RateSlab<>'+300'" & "AND RateSlab<>'+500'" & "AND RateSlab<>'+1000'")
      Me.PivotNetRateBox = DLookup("NetRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab<>'Min'" & "AND RateSlab<>'-45'" & "AND RateSlab<>'+45'" & "AND RateSlab<>'+100'" & "AND RateSlab<>'+300'" & "AND RateSlab<>'+500'" & "AND RateSlab<>'+1000'")
      Me.PivotSellingRateBox = DLookup("SellingRate", "RateAirlineSlabTable", "RateAirlineid=" & Me.RateAirlineIDBox & "AND RateSlab<>'Min'" & "AND RateSlab<>'-45'" & "AND RateSlab<>'+45'" & "AND RateSlab<>'+100'" & "AND RateSlab<>'+300'" & "AND RateSlab<>'+500'" & "AND RateSlab<>'+1000'")

(After finish will return to point B)    
  
   Else
      Me.AddRatesButton.Caption = "Add Rate"
      Me.ClearAllButton.Caption = "Clear All"
  
      DoCmd.GoToRecord , , acNewRec
      Me.RateNumberBox = "Add New Rates"
   End If
  

End Sub

EDIT: ADDED CODE TAGS - (also moved to correct topic) - The_Doc_Man
 
Last edited by a moderator:

CJ_London

Super Moderator
Staff member
Local time
Today, 08:33
Joined
Feb 19, 2013
Messages
16,553
the form current event is triggered every time you go to another record, not just when the form loads - if you just want it to run once on load, use the form load event
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:33
Joined
Feb 28, 2001
Messages
27,001
I have moved your post from the Watercooler to the Modules and VBA section.

Now there is the matter of some code and annotations that don't seem to match what you were trying to say.

DoCmd.GoToRecord , , acGoTo, 2 (After here will return to point A) No it won't.

There is no reason for a change of execution control following a "GoTo Record." Your code in that line will fall through to point B after that "GoTo Record."

What is it supposed to do IN ENGLISH? You showed us a wall of code that "diddles" with the current record. I am not exactly sure on this one because it is a fine point. If the Form_Current event triggers a record change, you WILL get another activation of Form_Current but I don't remember which instantiation finishes first in that case. I know that an error trap cannot interrupt another error trap but I'm not sure if a non-trap event routine that triggers another event defers to the new event or finishes first and then starts the new event. Worse, though, is that since this is the Form_Current triggering another Form_Current, you would have to worry about any local variables in that routine - if there were any.
 

JCRamirez

New member
Local time
Today, 17:33
Joined
Sep 15, 2020
Messages
14
the form current event is triggered every time you go to another record, not just when the form loads - if you just want it to run once on load, use the form load event

Thanks, I could solve my issue with your suggestion. I don't know why I couldn't figure out before
 

JCRamirez

New member
Local time
Today, 17:33
Joined
Sep 15, 2020
Messages
14
I have moved your post from the Watercooler to the Modules and VBA section.

Now there is the matter of some code and annotations that don't seem to match what you were trying to say.

DoCmd.GoToRecord , , acGoTo, 2 (After here will return to point A) No it won't.

There is no reason for a change of execution control following a "GoTo Record." Your code in that line will fall through to point B after that "GoTo Record."

What is it supposed to do IN ENGLISH? You showed us a wall of code that "diddles" with the current record. I am not exactly sure on this one because it is a fine point. If the Form_Current event triggers a record change, you WILL get another activation of Form_Current but I don't remember which instantiation finishes first in that case. I know that an error trap cannot interrupt another error trap but I'm not sure if a non-trap event routine that triggers another event defers to the new event or finishes first and then starts the new event. Worse, though, is that since this is the Form_Current triggering another Form_Current, you would have to worry about any local variables in that routine - if there were any.

Thanks for your suggestion, but I followed whtat London said and I could fix my problem
 

Users who are viewing this thread

Top Bottom