Form will not process more than 564 records 3

[bustersports]

Thanks for reading and any suggestions you may have.

I have a form that will have anywhere from few hundred records, up to about 20,000. For some reason, it will not step through more than 564 records. In troubleshooting, I even put the max records to 500, close the form, and start again. It will then still max out at a total of 564 between opening the form twice. I have another very similar form, that will process all records with no problems. Below is my code for the NON-working form, below it is the correctly working form. Any suggestions as what may be causing the problem?


NON-Working Form

Private Sub cmdCalculatePayment_Click()

Dim X As Integer
Dim Y As Integer
Dim Z As Integer
Dim RecordCount As Boolean

DoCmd.GoToRecord , "", acFirst
Z = txtRecordsFound + 1
Y = Z - 1
For X = 1 To Y

If [Forms]![frmSelectChargeAccessorials].txtTotalRatesFoundAccessorial = 0 Then

Call NROF

Else

If txtCostBasis = "CostBasis" Then
Call CostBasis

Else

If txtCostBasis = "CPP" Then
Call WeightTimesRate

Else
If txtCostBasis = "CPK" Then
Call WeightTimesRate

Else

If txtCostBasis = "WeightTimesRate" Then
Call WeightTimesRate

Else

If txtCostBasis = "CWT" Then
Call CWT

Else

If txtCostBasis = "FlatCharge" Then
Call FlatCharge

Else

If txtCostBasis = "Container" Then
Call PerUnit

Else

If txtCostBasis = "HAWB" Then
Call PerUnit

Else
If txtCostBasis = "Per Container Per Day" Then
Call PerUnit

Else

If txtCostBasis = "PerDay" Then
Call PerUnit

Else

If txtCostBasis = "PerHour" Then
Call PerUnit

Else

If txtCostBasis = "Per KG Per Day" Then
Call PerUnit

Else
If txtCostBasis = "PerMonth" Then
Call PerUnit

Else

If txtCostBasis = "Per Mile" Then
Call PerUnit

Else

If txtCostBasis = "Per Shipment" Then
Call PerUnit

Else

If txtCostBasis = "Per Trip" Then
Call PerUnit

Else

If txtCostBasis = "PerUnit" Then
Call PerUnit

Else

If txtCostBasis = "Cost Per Cubic Meter" Then
Call PassThrough

Else
If txtCostBasis = "PassThrough" Then
Call PassThrough

Else

If txtCostBasis = "Percent" Then
Call PassThrough

Else

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

DoCmd.Hourglass True

DoCmd.GoToRecord , "", acNext

txtRecordsFound.Requery

Next X

DoCmd.Hourglass False

Requery

End Sub


Correctly Working


Dim X As Integer
Dim Y As Integer
Dim Z As Integer
Dim TariffNumber As Integer

[Forms]![frmSelectChargeFreight]![subfrmSelectChargeFreight4].Requery
TariffNumber = [Forms]![frmSelectChargeFreight].txtTariffNumber

DoCmd.GoToRecord , "", acFirst
Z = txtRecordsFound + 1
Y = Z - 1
For X = 1 To Y

[Forms]![frmSelectChargeFreight]![subfrmSelectChargeFreight5].Requery

[Forms]![frmSelectChargeFreight]![subfrmSelectChargeFreight4].Requery

[Forms]![frmSelectChargeFreight]![subfrmSelectChargeFreight5].Requery 'txtRatesFound

'txtRatesFound.Requery

If txtRatesFound = 0 Then

txtPostAuditComments = "NROF"
txtPostAuditRefund = Paid
optComplete = -1
optPostAuditClaim = -1
txtAdjCorrectAmt = 0
optComplete.SetFocus
cboReason = 5

Else

If TariffNumber = 1 Then 'Rate is cost basis

Call CostBasis

Else

If TariffNumber = 2 Then 'CPP or CPK, weight times rate

Call WeightTimesRate

Else

If TariffNumber = 3 Then 'Rate is CWT

Call CWT

Else

If TariffNumber = 4 Then 'Flat charge

Call FlatCharge

Else

If TariffNumber = 5 Then 'Per Unit (Per Hour, Per Month, Per Month, etc)

Call PerUnit

Else

If TariffNumber = 6 Then 'Pass Through or Percent or Cubic Meter

Call PerUnit

Else

End If
End If
End If
End If
End If
End If
End If

DoCmd.SetWarnings True

'txtRatesFound.Requery
txtNumberRatesFound = txtRatesFound

DoCmd.GoToRecord , "", acNext

'txtRecordsFound.Requery

Next X

DoCmd.Hourglass False

End Sub

 

[SkipVought]

hi,

This might be a bit cleaner.

Private Sub cmdCalculatePayment_Click()

     Dim X As Long
     Dim Y As Long
     Dim Z As Long
        
     DoCmd.GoToRecord , "", acFirst
     Z = txtRecordsFound + 1
     Y = Z - 1
     
     For X = 1 To Y
    
         If [Forms]![frmSelectChargeAccessorials].txtTotalRatesFoundAccessorial = 0 Then
        
            Call NROF
        
         Else
            
            Select Case txtCostBasis
                Case "CostBasis"
                    Call CostBasis
                Case "CPP", "CPK", "WeightTimesRate"
                    Call WeightTimesRate
                Case "CWT"
                    Call CWT
                Case "FlatCharge"
                    Call FlatCharge
                Case "Container", "HAWB", "Per Container Per Day", "PerDay", "PerHour", "Per KG Per Day", _
                    "PerMonth", "Per Mile", "Per Shipment", "Per Trip", "PerUnit"
                    Call PerUnit
                Case "Cost Per Cubic Meter", "PassThrough", "Percent"
                    Call PassThrough
            End Select
            
         DoCmd.Hourglass True
        
         DoCmd.GoToRecord , "", acNext
        
         txtRecordsFound.Requery
    
     Next X
    
     DoCmd.Hourglass False
    
     Requery

 End Sub

 

[dhookom]

First, please use TGML to properly format your code blocks. Your posting is nearly unreadable in its current state. Just select your code and click the code icon above the input box. Then always Preview your post prior to clicking the Submit Post button.

Private Sub cmdCalculatePayment_Click()
 
    Dim X As Integer
    Dim Y As Integer
    Dim Z As Integer
    Dim RecordCount As Boolean
 
    DoCmd.GoToRecord , "", acFirst
    Z = txtRecordsFound + 1
    Y = Z - 1
    For X = 1 To Y
    If [Forms]![frmSelectChargeAccessorials].txtTotalRatesFoundAccessorial = 0 Then
        Call NROF
      Else
        If txtCostBasis = "CostBasis" Then
        Call CostBasis
    Else
    If txtCostBasis = "CPP" Then
        Call WeightTimesRate

You might want to try some trouble-shooting faq705-7148 before posting a question. It helps us narrow down possible solutions.

You can also reduce the complexity of your code using Select Case in place of all your nested If Else End If like:

Select Case Me.txtCostBasis
    Case "CostBasis"
        Call CostBasis
    Case "CPP"
        Call WeightTimesRate
    Case "CPK"
        Call WeightTimesRate
    Case "WeightTimesRate"
        Call WeightTimesRate
    Case "CWT"
        Call CWT
    Case "FlatCharge"
        Call FlatCharge
    Case "Container"
        Call PerUnit
    Case "HAWB"
        Call PerUnit
    Case "Per Container Per Day"
        Call PerUnit
    Case "PerDay"
        Call PerUnit
    Case "PerHour"
        Call PerUnit
    Case "Per KG Per Day"
        Call PerUnit
    Case "PerMonth"
        Call PerUnit
    Case "Per Mile"
        Call PerUnit
    Case "Per Shipment"
        Call PerUnit
    Case "Per Trip"
        Call PerUnit
    Case "PerUnit"
        Call PerUnit
    Case "Cost Per Cubic Meter"
        Call PassThrough
    Case "PassThrough"
        Call PassThrough
    Case "Percent"
        Call PassThrough
End Select

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Skip,
Good call on the combining of Cases. I hadn't notice the similar Calls to the same functions.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

1) Although it is not your issue, that code is a kind of a mess. I believe the following is what you meant.

Public Sub Test()
   Dim X As Integer
   'I do not get this. You do not need Y and Z because you never use them
   'Looks to me like Y is simply textRecordsFound (txtrecordsfound + 1 - 1 = txtrecordsfound
   'Dim Y As Integer 'not used
   'Dim Z As Integer 'not used
   'Dim RecordCount As Boolean ' not used
   DoCmd.GoToRecord , "", acFirst

   'Z = txtRecordsFound + 1
   'y = txtRecordsFound
   'Y = Z - 1
   
 For X = 1 To Me.txtRecordsFound
   If [Forms]![frmSelectChargeAccessorials].txtTotalRatesFoundAccessorial = 0 Then
     Call NROF
   Else
   Select Case Me.txtcostbasis
      Case "CostBasis"
        Call CostBasis
      Case "CPP", "CPK", "WeightTimesRate"
        Call WeightTimesRate
      Case "CWT"
        Call CWT
      Case "FlatCharge"
        Call FlatCharge
      Case "Container"
        Call PerUnit
      Case "HAWB", "Per Container Per Day", "PerDay", "PerHour", "Per KG Per Day", "PerMonth", _
      "Per Mile", "Per Shipment", "Per Trip ", "PerUnit"
         Call PerUnit
      Case "Cost Per Cubic Meter", "PassThrough", "Percent"
         Call PassThrough
      Case Else
         MsgBox "Illegal entry " & Me.txtcostbasis
    End Select
    End If
    DoCmd.Hourglass True
    DoCmd.GoToRecord , "", acNext
    txtRecordsFound.Requery
Next X
   DoCmd.Hourglass False
   Requery
End Sub

2) Cannot answer the problem without knowing what these calls do
3) This code is very inefficient. You should never work at the form level like this. You should either use a recordset or some sql query to do the updates.

something like this should be orders of magnitude faster

Public Sub Test2()
   Dim rs As DAO.Recordset
   Set rs = Me.RecordsetClone
   Do While Not rs.EOF
     If rs!something = 0 Then
     If [Forms]![frmSelectChargeAccessorials].txtTotalRatesFoundAccessorial = 0 Then
     Call NROF
   Else
   Select Case rs!costbasis
      Case "CostBasis"
        Call costbasis
      Case "CPP", "CPK", "WeightTimesRate"
        Call WeightTimesRate
      Case "CWT"
        Call CWT
      Case "FlatCharge"
        Call FlatCharge
      Case "Container"
        Call PerUnit
      Case "HAWB", "Per Container Per Day", "PerDay", "PerHour", "Per KG Per Day", "PerMonth", _
      "Per Mile", "Per Shipment", "Per Trip ", "PerUnit"
         Call PerUnit
      Case "Cost Per Cubic Meter", "PassThrough", "Percent"
         Call PassThrough
      Case Else
         Debug.Print "Illegal entry " & Me.txtcostbasis
    End Select
    End If
    rs.MoveNext
   Loop
   Me.Requery
End Sub

 

[MajP]

Sorry, everyone posted at the same time.
So big question what do these calls do.

 

[bustersports]

dhookm, thanks for the feedback on how to submit the question.

Skip, I now get a Next without For error message. I copied your code, never used Select Case statements before, does the "For" need to be someplace else? Thanks very much to both of you for very quick response.

Dim X As Long
Dim Y As Long
Dim Z As Long

DoCmd.GoToRecord , "", acFirst
Z = txtRecordsFound + 1
Y = Z - 1

For X = 1 To Y

If [Forms]![frmSelectChargeAccessorials].txtTotalRatesFoundAccessorial = 0 Then

Call NROF

Else

Select Case txtCostBasis
Case "CostBasis"
Call CostBasis
Case "CPP", "CPK", "WeightTimesRate"
Call WeightTimesRate
Case "CWT"
Call CWT
Case "FlatCharge"
Call FlatCharge
Case "Container", "HAWB", "Per Container Per Day", "PerDay", "PerHour", "Per KG Per Day", _
"PerMonth", "Per Mile", "Per Shipment", "Per Trip", "PerUnit"
Call PerUnit
Case "Cost Per Cubic Meter", "PassThrough", "Percent"
Call PassThrough
End Select

DoCmd.Hourglass True

DoCmd.GoToRecord , "", acNext

txtRecordsFound.Requery

Next X

DoCmd.Hourglass False

End If

Requery

 

[bustersports]

MajP,

What the code is doing is looking at an invoice in subfrm1, the finds the applicable rate based on a query in subfrm2. There are about a dozen variables in the query, not each has to match or be used. Ex: I query for zip code but only if it is a U.S. domestic shipment, otherwise it does not matter. Code is something like zipcode = subfrm1.zipcode or is null. The subfrm2 has all of the rating data in it, the subfrm1 has the billed invoice details.

Does that make more sense?

Thank you for your response.

 

[MajP]

The "end if" and next x are int the wrong order.

for
If
else
end if
next x

 

[bustersports]

Thank you MajP, that did the final trick. Thanks to all for your very quick responses.

 

[MajP]

Although that cleaned up your code, it should not have fixed anything. Did it? I would think the problem is how you are "processing" the reocords.
If you still have a problem. Can you provide an example of the code called? Also can you post the queries used in the subforms and what fields link the subforms to the main form?
The reason is this seems real inefficient. Processing should be done behind the scenes either by a recordset or a series of queries.
If you need to loop the records and then find child records for each record it is normally done by looping the main records. Then for each record you find some value like a primary key. Then you find the child record information using this value. Do something with this child query. Then go to the next parent record.

 

[bustersports]

So, I spoke too soon. The process continues to stop after 654 records, even though there are over 5000 right now. Any other suggestions? Thanks.

 

[bustersports]

MajP,

I just now saw your response. My confusion is to why it works, even if it is inefficient, on my other form which is doing the exact same thing. The differences between the 2 forms are one is for freight costs, the other is for additional charges added to the invoice, such as waiting time, special delivery instructions, etc. The rates tables are very different which is why I chose to make it two forms. The rates also vary by the carrier and country.

 

[MajP]

My confusion is to why it works, even if it is inefficient

The processing looks like it would be different. So it could be hitting an error not seen on the other form. My point if you clean up the processing logic, you could simplify the code and avoid some problems. Do you get an error at 564 records. Looping records on the interface instead of behind the scenes can be problematic. Things happen when you move through a forms record. There are a lot of events and validation that can interfere. A lot simpler to go directly to the data.

 

[bustersports]

Here is the query (in subfrm2) that is being ran after selecting an invoice in subfrm1.

SELECT tblRatesCombined.RateIDNumber, tblRatesCombined.LoadIDNumber, tblRatesCombined.Carrier, tblRatesCombined.[Amendment#], tblRatesCombined.EffectiveDate, tblRatesCombined.ExpirationDate, tblCarrierQtrlyTool.SCAC, tblRatesCombined.Mode, tblRatesCombined.OriginPort, tblRatesCombined.OriginCountry, tblRatesCombined.OriginGeo, tblRatesCombined.DestinationPort, tblRatesCombined.DestinationCountry, tblRatesCombined.DestinationGeo, tblRatesCombined.MinWeight, tblRatesCombined.MaxWeight, tblRatesCombined.WeightType, tblRatesCombined.DimFactor, tblRatesCombined.ServiceCode, tblRatesCombined.ServiceLevelCode, tblRatesCombined.DeliveryTime, tblRatesCombined.EquipType, tblRatesCombined.DeliveryTime, tblRatesCombined.ServiceType, tblRatesCombined.SupplierCode, tblRatesCombined.IntelCode, tblRatesCombined.TariffName, tblRatesCombined.Rate, tblRatesCombined.MinCharge, tblRatesCombined.MaxCharge, tblRatesCombined.ZoneFlag, tblRatesCombined.TPT, tblRatesCombined.Comment, tblRatesCombined.LastUpdate, tblRatesCombined.CTSIID, tblRatesCombined.AccessorialType, tblRatesCombined.AccessorialCode, tblRatesCombined.Volume, tblRatesCombined.IntelDesc, tblRatesCombined.Archive, tblRatesCombined.RateTable, tblRatesCombined.Accessorial, tblRatesCombined.TariffCodeNumber, Count(tblRatesCombined.ServiceLevelCode) AS CountOfServiceLevelCode
FROM tblCarrierQtrlyTool INNER JOIN tblRatesCombined ON tblCarrierQtrlyTool.RateTableSCAC = tblRatesCombined.Carrier
GROUP BY tblRatesCombined.RateIDNumber, tblRatesCombined.LoadIDNumber, tblRatesCombined.Carrier, tblRatesCombined.[Amendment#], tblRatesCombined.EffectiveDate, tblRatesCombined.ExpirationDate, tblCarrierQtrlyTool.SCAC, tblRatesCombined.Mode, tblRatesCombined.OriginPort, tblRatesCombined.OriginCountry, tblRatesCombined.OriginGeo, tblRatesCombined.DestinationPort, tblRatesCombined.DestinationCountry, tblRatesCombined.DestinationGeo, tblRatesCombined.MinWeight, tblRatesCombined.MaxWeight, tblRatesCombined.WeightType, tblRatesCombined.DimFactor, tblRatesCombined.ServiceCode, tblRatesCombined.ServiceLevelCode, tblRatesCombined.EquipType, tblRatesCombined.DeliveryTime, tblRatesCombined.ServiceType, tblRatesCombined.SupplierCode, tblRatesCombined.IntelCode, tblRatesCombined.TariffName, tblRatesCombined.Rate, tblRatesCombined.MinCharge, tblRatesCombined.MaxCharge, tblRatesCombined.ZoneFlag, tblRatesCombined.TPT, tblRatesCombined.Comment, tblRatesCombined.LastUpdate, tblRatesCombined.CTSIID, tblRatesCombined.AccessorialType, tblRatesCombined.AccessorialCode, tblRatesCombined.Volume, tblRatesCombined.IntelDesc, tblRatesCombined.Archive, tblRatesCombined.RateTable, tblRatesCombined.Accessorial, tblRatesCombined.TariffCodeNumber, tblRatesCombined.DeliveryTime
HAVING (((tblRatesCombined.EffectiveDate)<=[Forms]![frmSelectChargeAccessorials]![subfrmSelectChargeAccessorials2].[Form]![txtShipDate]) AND ((tblRatesCombined.ExpirationDate)>=[Forms]![frmSelectChargeAccessorials]![subfrmSelectChargeAccessorials2].[Form]![txtShipDate]) AND ((tblCarrierQtrlyTool.SCAC)=[Forms]![frmSelectChargeAccessorials]![subfrmSelectChargeAccessorials2].[Form]![txtSCAC]) AND ((tblRatesCombined.OriginPort)=[Forms]![frmSelectChargeAccessorials]![subfrmSelectChargeAccessorials2].[Form]![txtOrigPort] Or (tblRatesCombined.OriginPort)="ZZZ") AND ((tblRatesCombined.OriginCountry)=[Forms]![frmSelectChargeAccessorials]![subfrmSelectChargeAccessorials2].[Form]![txtOrigCntryTwoChar] Or (tblRatesCombined.OriginCountry)="ZZZ") AND ((tblRatesCombined.OriginGeo)=[Forms]![frmSelectChargeAccessorials]![subfrmSelectChargeAccessorials2].[Form]![txtOrigGeo] Or (tblRatesCombined.OriginGeo)="ZZZ" Or (tblRatesCombined.OriginGeo)="ZZ") AND ((tblRatesCombined.DestinationPort)=[Forms]![frmSelectChargeAccessorials]![subfrmSelectChargeAccessorials2].[Form]![txtDestPort] Or (tblRatesCombined.DestinationPort)="ZZZ") AND ((tblRatesCombined.DestinationCountry)=[Forms]![frmSelectChargeAccessorials]![subfrmSelectChargeAccessorials2].[Form]![txtDestCntryTwoChar] Or (tblRatesCombined.DestinationCountry)="ZZZ") AND ((tblRatesCombined.DestinationGeo)=[Forms]![frmSelectChargeAccessorials]![subfrmSelectChargeAccessorials2].[Form]![txtDestGeo] Or (tblRatesCombined.DestinationGeo)="ZZZ" Or (tblRatesCombined.DestinationGeo)="ZZ") AND ((tblRatesCombined.ServiceCode)=[Forms]![frmSelectChargeAccessorials]![subfrmSelectChargeAccessorials2].[Form]![txtServiceTypeCode] Or (tblRatesCombined.ServiceCode)="N/A" Or (tblRatesCombined.ServiceCode) Is Null) AND ((tblRatesCombined.ServiceLevelCode)=[Forms]![frmSelectChargeAccessorials]![txtServiceLevelCode] Or (tblRatesCombined.ServiceLevelCode)="N/A" Or (tblRatesCombined.ServiceLevelCode) Is Null) AND ((tblRatesCombined.EquipType)=[Forms]![frmSelectChargeAccessorials]![txtEquipmentType] Or (tblRatesCombined.EquipType)="N/A" Or (tblRatesCombined.EquipType) Is Null) AND ((tblRatesCombined.AccessorialCode)=[Forms]![frmSelectChargeAccessorials]![subfrmSelectChargeAccessorials2].[Form]![txtFeeCode]) AND (((tblRatesCombined.DeliveryTime)=[Forms]![frmSelectChargeAccessorials]![txtServiceTime] Or (tblRatesCombined.DeliveryTime)="N/A" Or (tblRatesCombined.DeliveryTime) Is Null) And ((tblRatesCombined.DeliveryTime)=[Forms]![frmSelectChargeAccessorials]![txtServiceTime] Or (tblRatesCombined.DeliveryTime)="N/A" Or (tblRatesCombined.DeliveryTime) Is Null)));

 

[bustersports]

Forgot to answer your question about 564 records, yes it happens everytime, a 404 Automation Error

 

[MajP]

I think you mean a 440 error. In the short term you could error trap that error and resume next.

That query is mind boggling. When I look at something that complicated I have to think there is a cleaner way. I could not wrap my head around the logic of that query and that where statement.

What do your function calls do?

 

[SkipVought]

Oops, I forgot an End If

 

[Andrzejek]

When I see SQL like that, first thing I want to do is to introduce aliases for tables
[tt]...
FROM tblCarrierQtrlyTool [blue]As C[/blue] INNER JOIN tblRatesCombined [blue]As R[/blue]
...[/tt]
so anywhere where you have [tt]tblCarrierQtrlyTool.[/tt] you use [tt]C.[/tt]
This will make your SQL a LOT shorter and more readable:

And I am with MajP - there must be a better way to do it. My guess would be - normalize the DB

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[bustersports]

MajP,

The function calls all make a call to a different rate calculation. Depending on the type of rate being billed, it could be simply a weight times rate or it could be qty times rate, etc.

Yes, you are correct, it is a 440 error, not 404.