what causes records to repeat endlessly?

humvie · Aug 25, 2002

I've written some code which basically takes information from one table and then compares it against two other tables and then CREATES a new table from the result.

I used the INNER JOIN .... ON ....
Then to compare I used INSERT ......

The codes works fine until I add the WHERE .....

Now without copying the code (it is fairly long-winded) would anyone know why records would be repeated endlessly to the point ACCESS errors out by maxing out at .99MB ?

Has anyone run into this problem before and what was done to rectify the problem?

thanks

Guest_imported · Aug 25, 2002

What is the SQL statement you are using?

Guest_imported · Aug 25, 2002

What is the SQL statement you are using?

Steve101 · Aug 25, 2002

I would suggest that your code is obviously not breaking out of a processing loop. Without seeing the code, its impossible to help further. Why not post the code into this thread, so someone can check it out.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

humvie · Aug 25, 2002

OK, here you go. What I'm trying to achieve is to give the user the obtion to select the carrier (cboTruck). Having a difficult time placing it in the code. Disregard some of the comments - I use them to keep track of my progress.
See text in red. Maybe it doesnt even belong there !!!

strSQL1 = "SELECT tblRatesHeader.PrimaryCarrier, tblRatesHeader.CarrierCode, " & _
"tblRatesHeader.CarrierName, tblRatesHeader.Item, tblRatesHeader.Quote, " & _
"tblRatesHeader.Origin, tblRatesHeader.Destination, tblRatesHeader.ServType, " & _
"tblRatesHeader.ServLevel, tblRateDetail.Currency, tblRateDetail.Rate, tblRateDetail.RateType, " & _
"tblRateDetail.HidWgt, tblRateDetail.Weight, tblRateDetail.WgtBrk, " & _
"tblRatesHeader.Factor, tblRateDetail.Qty, " & _
"IIf([QTY]='MC' Or [RateType] = 'Flat' Or CINT([Qty]) Between 1 AND 35, " & _
"[RATE], IIf(" & Weight_in_lbs & "<=[Weight],[Weight]/100*[Rate]," & Weight_in_lbs & "/100*[Rate])) AS Net " & _
"FROM tblRatesHeader INNER JOIN tblRateDetail " & _
"ON tblRatesHeader.Quote = tblRateDetail.Quote WHERE tblRatesHeader.CarrierName = '" & Me!cboTruck & "'"

' adding the AND tblRatesHeader.CarrierName = 'DAILY MOTOR FREIGHT' causes the system to create duplicates
' until Access reaches .99MB or full capacity

strSQL2 = "SELECT Freight.Carrier, Freight.[Province Destination], " & _
"Freight.[Origin Zip Code/PC], Freight.[Weight in lbs], " & _
"Freight.Total, Freight.TTLApproved, Freight.Currency, " & _
"Freight.BegDate, Freight.Skids, Freight.[Invoice Number], " & _
"Freight.FSC, Freight.OtherCharges, Freight.Mode, True AS Sel, Freight.TTLApproved " & _
"FROM Freight WHERE 1 = 1"

If Not (Me!cboCarrierHIST.Value = vbNullString) Then
strSQL2a = strSQL2a & " AND Carrier = '" & Me!cboCarrierHIST.Value & "'"
End If

If Not (Me!cboDestHIST.Value = vbNullString) Then
strSQL2a = strSQL2a & " AND [Province Destination] = '" & Me!cboDestHIST.Value & "'"
End If

If Not (Me!cboOriginHIST.Value = vbNullString) Then
strSQL2a = strSQL2a & " AND [Origin Zip Code/PC] = '" & Me!cboOriginHIST.Value & "'"
End If

If Not (Me!cboTerrHIST.Value = vbNullString) Then
strSQL2a = strSQL2a & " AND Territory = '" & Me!cboTerrHIST.Value & "'"
End If

If (Not (IsNull(Me!txtWgtHIST))) And (Not (IsNull(Me!txtWgtHIST2))) Then
strSQL2a = strSQL2a & " AND [Weight in lbs] BETWEEN " & Me!txtWgtHIST & " AND " & Me!txtWgtHIST2 & ""
End If

If (Not IsNull(Me!Text133.Value)) And (Not IsNull(Me!Text135.Value)) Then
strSQL2a = strSQL2a & " AND BegDate BETWEEN #" & Me!Text133.Value & "# AND #" & Me!Text135.Value & "#"
End If

strSQL2 = strSQL2 & strSQL2a

strSQL3 = "DELETE * FROM FreightCost_Temp" ' Used for clearing the temp table

strSQL4 = "INSERT INTO FreightCost_Temp " & _
"( Carrier, [Province Destination], [Origin Zip Code/PC], " & _
" [Weight in lbs], Total, TTLApproved, [Currency], BegDate, " & _
" Skids, [Invoice Number], FSC, OtherCharges, Mode, Selected, Net) " & _
"SELECT Freight.Carrier, Freight.[Province Destination], " & _
"Freight.[Origin Zip Code/PC], Freight.[Weight in lbs], " & _
"Freight.Total, Freight.TTLApproved, Freight.Currency, " & _
"Freight.BegDate, Freight.Skids, Freight.[Invoice Number], " & _
"Freight.FSC, Freight.OtherCharges, Freight.Mode, True AS Sel, Freight.TTLApproved " & _
"FROM Freight WHERE 1 = 1"

strSQL4 = strSQL4 & strSQL2a

' Used for populating the temp table and anything changed here must also be
'be changed in strSQL2
' Clear the temp table

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL3
DoCmd.SetWarnings True

' Get the existing data and push it into the temp table
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL4
DoCmd.SetWarnings True

' Process the comparison data
' We need to do this one record at a time.
Set db = CurrentDb()
Set rs2 = db.OpenRecordset(strSQL2)

With rs2 ' The Freight Table
While Not .EOF ' Cycle through all the records
' Build the filter
strFilter = "("
' Origin
If Not IsNull(![Origin Zip Code/PC]) Then
strFilter = strFilter & " Origin = '" & ![Origin Zip Code/PC] & "' and"

End If

' Destination
If Not IsNull(![Province Destination]) Then
strFilter = strFilter & " Destination = '" & ![Province Destination] & "' and"

End If

' Qty
If Not IsNull(!Skids) Then
strFilter = strFilter & " Qty = '" & !Skids & "') or ("

End If

'Origin
If Not IsNull(![Origin Zip Code/PC]) Then
strFilter = strFilter & " Origin = '" & ![Origin Zip Code/PC] & "' and"

End If

' Destination
If Not IsNull(![Province Destination]) Then
strFilter = strFilter & " Destination = '" & ![Province Destination] & "' and"

End If

On Error Resume Next
' Weight
If Not IsNull(![Weight in lbs]) Then
strFilter = strFilter & " HidWgt <= " & ![Weight in lbs] & " and WgtBrk >= " & ![Weight in lbs] & &quot

"
'MsgBox ![Weight In Lbs]
Weight_in_lbs = ![Weight in lbs]

Else
Weight_in_lbs = 0

End If

' Trim the string
If Right(strFilter, 3) = "and" Then
strFilter = Trim(Left(strFilter, Len(strFilter) - 3))

End If

' Set the filter
If Len(strFilter) > 0 Then
strFilter = " WHERE " & strFilter
strSQL5 = strSQL1 & strFilter
End If

' Run the query

Set rs1 = db.OpenRecordset(strSQL5)

While Not rs1.EOF

Option1 = False

If rs1!Qty = "MC" Or rs1!RateType = "FLAT" Or rs1!RateType = "SKID" Or rs1!Qty = "1" Or rs1!Qty = "2" Or rs1!Qty = "3" Or rs1!Qty = "4" Or rs1!Qty = "5" Or rs1!Qty = "6" Or rs1!Qty = "7" Or rs1!Qty = "8" Or rs1!Qty = "9" Or rs1!Qty = "10" Or rs1!Qty = "11" Or rs1!Qty = "12" Or rs1!Qty = "13" Or rs1!Qty = "14" Or rs1!Qty = "15" Or rs1!Qty = "16" Or rs1!Qty = "17" Or rs1!Qty = "18" Or rs1!Qty = "19" Or rs1!Qty = "20" Or rs1!Qty = "21" Or rs1!Qty = "22" Or rs1!Qty = "23" Or rs1!Qty = "24" Or rs1!Qty = "25" Or rs1!Qty = "26" Or rs1!Qty = "27" Or rs1!Qty = "28" Or rs1!Qty = "29" Or rs1!Qty = "30" Or rs1!Qty = "31" Or rs1!Qty = "32" Or rs1!Qty = "33" Or rs1!Qty = "34" Or rs1!Qty = "35" Then Option1 = True
If Option1 Then
Net = rs1!Rate + rs2!FSC + rs2!OtherCharges
Total = Net - rs2!FSC - rs2!OtherCharges
End If

If rs1!RateType = "CWT" Then
If [Weight_in_lbs] <= rs1!Weight Then
Net = (rs1!Weight / 100 * rs1!Rate) + rs2!FSC + rs2!OtherCharges
Total = Net - rs2!FSC - rs2!OtherCharges
Else

Net = ([Weight_in_lbs] / 100 * rs1!Rate) + rs2!FSC + rs2!OtherCharges
Total = Net - rs2!FSC - rs2!OtherCharges
End If

End If
'stop

strSQL4 = "INSERT INTO FreightCost_Temp " & _
"( [Invoice Number], Carrier, [Province Destination], [Origin Zip Code/PC], " & _
" [Weight in lbs], [Currency], Skids, Net, Total, OtherCharges, FSC) " & _
"VALUES ( '" & rs2![Invoice Number] & "', ' " & rs1!CarrierName & "', '" & rs1!Destination & "', '" & _
"" & rs1!Origin & "', " & rs1!Weight & ", '" & _
"" & rs1!Currency & "', '" & rs2!Skids & "', '" & Net & "','" & Total & "','" & rs2!OtherCharges & "','" & rs2!FSC & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL4
DoCmd.SetWarnings True

rs1.MoveNext

Wend
strSQL5 = ""
strFilter = ""
.MoveNext

Wend

End With

Steve101 · Aug 25, 2002

Humvie,

I've had a quick look at the code, and nothing obvious leaps out that would cause an infinate loop. Possibly whats happening is:

(a) the looping is generating more data than anticipated, probably because of underconstrained queries.

(b) There's no evidence of error handling in the code snippet you provided, but is it possible that you ignore errors, which causes the program to resume on the next line, and as a consequence, never complete - unlikely, but I need to ask this.

At this point, what I would do in your position, is start making extensive use of the debugger, stopping the code at various breakpoints (I note that you do have a commented out Stop statement in your code), and displaying key variable values. One of my favourite debugging aids, is to display an sql based variable in the immediate window, then cut it and past it into an SQL query definition. Then run the query, and make sure that it delivers the rows that you expect.

Before doing the extensive debugging, you should be able to view the contents of the freightcost_temp table to see what data is being generated into it. Preferably, try to view this data in the sequence it is generated. Duplicate (unwanted) entries should then expose themselves, providing you clues as to where to look for the reason in the code.

I'm afraid that from the look of things, you have a little detective work in front of you.

I assume also that the code that you submitted is behind a form, as opposed to being in a module. If its in a module, then the me! prefix will not be recognised, resulting in an error situation, or worse, if error handling is suppressed.

One other point; your one if statement has code to the effect of rs1!Qty = '1' or rs1!Qty = 2 or ... or rs1!Qty = '35'. This for example could be better coded as:

Val(rs!Qty) > =1 and Val(rs!Qty) <= 35

I know I havnt solved your problem, but hopefully I've provided a bit of a strategy for you to follow to solve it,

Good luck,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

danvlas · Aug 26, 2002

To test:
First I'd get rid of 'On Error Resume Next'
I'd run the code step by step and check each variable, especially until the line:

strSQL5 = strSQL1 & strFilter

Then, before
While Not rs1.EOF

I'd check the recordcount:

rs1.movelast
rs1.movefirst
Debug.print rs1.RecordCount 'Or MsgBox rs1.RecordCount & " records"

To simplify the code:
I'd change:

If rs1!Qty = "MC" Or rs1!RateType = "FLAT" Or rs1!RateType = "SKID" Or rs1!Qty = "1" Or rs1!Qty = "2" Or rs1!Qty = "3" Or rs1!Qty = "4" Or rs1!Qty = "5" Or rs1!Qty = "6" Or rs1!Qty = "7" Or rs1!Qty = "8" Or rs1!Qty = "9" Or rs1!Qty = "10" Or rs1!Qty = "11" Or rs1!Qty = "12" Or rs1!Qty = "13" Or rs1!Qty = "14" Or rs1!Qty = "15" Or rs1!Qty = "16" Or rs1!Qty = "17" Or rs1!Qty = "18" Or rs1!Qty = "19" Or rs1!Qty = "20" Or rs1!Qty = "21" Or rs1!Qty = "22" Or rs1!Qty = "23" Or rs1!Qty = "24" Or rs1!Qty = "25" Or rs1!Qty = "26" Or rs1!Qty = "27" Or rs1!Qty = "28" Or rs1!Qty = "29" Or rs1!Qty = "30" Or rs1!Qty = "31" Or rs1!Qty = "32" Or rs1!Qty = "33" Or rs1!Qty = "34" Or rs1!Qty = "35" Then Option1 = True

to:
Select Case rs1!qty
Case "MC", "1", "2".... and so on
Select Case rs1!RateType
Case "FLAT", "SKID"
Option1 = True
Case Else
MsgBox "RateType = " & rs1!RateType 'an unhandled rate type
End Select
Case Else
MsgBox "qty = " & rs1!qty 'unhandled qty
End select

I'd delete "Where 1 = 1" in the SQL statements, as it is obviously always true and does nothing but unnecessarily complicates things. (I would understand "Where 1=0" as it's great when an empty recordset is needed, but it's not the case here).

And in the end I'd close the recordsets and set them to Nothing...

Just a few thoughts...

Dan
[pipe]

humvie · Aug 26, 2002

thanks guys, but I warned you it was long winded. I appreciate your input.
Thanks
Ray

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

what causes records to repeat endlessly?

humvie

Technical User

Guest_imported

New member

Guest_imported

New member

Steve101

Programmer

humvie

Technical User

Steve101

Programmer

danvlas

Programmer

humvie

Technical User

Similar threads

Part and Inventory Search

Sponsor