Loop is not Funtioning

[rwn]

Overall, this is a basic SQL and updating a Crystal Report. The user just enters free form data (no data is being pulled from JobBOSS) on the Filter Screen(PO number- Pack List – Part Cust Number – Total Qty- MAX Qty – Part Revision, etc).

The key is the Total Qty is divided by the MAX Qty that then equates to the Quantities per Label and the printing the correct number of Labels.

I’m stuck at getting the Loop to function (starts at line 73 on the rpt.htm) and just need a new set of eyes to see my error. I was able to get this formula and loop to function if I was pulling data from JobBOSS and it calculates the qty’s per label and the number of labels perfectly. But with the data now being Free Form, I can’t seem to get it to loop and print the required labels.


Function LoadReport()

' Read Cookies from Filter
NumOfLabels = CInt(GetCookieValue("NumOfLabels"))
LabelsUsed = CInt(GetCookieValue("LabelsUsed"))
TotQty = GetCookieValue("TOTQty")
MAXQty = GetCookieValue("MAXQty")
PO = GetCookieValue("PO")
Pklist = GetCookieValue("PKList")
Cust = GetCookieValue("Cust")
Rev = GetCookieValue("Rev")
Comments = GetCookieValue("Comments")

' Expire Cookies
document.cookie = "NumOfLabels=;" & sExpCookie
document.cookie = "LabelsUsed=;" & sExpCookie
document.cookie = "TOTQty=;" & sExpCookie
document.cookie = "MAXQty=;" & sExpCookie
document.cookie = "PO=;" & sExpCookie
document.cookie = "Pklist=;" & sExpCookie
document.cookie = "Cust=;" & sExpCookie
document.cookie = "Rev=;" & sExpCookie
document.cookie = "Comments=;" & sExpCookie

' Open Report
OpenReport "SH_Labels_PartFM.rpt"

' Clear Out Report
oRpt.DiscardSavedData



' ' Fill Recordset Final
' FillPartBlankRecords
' While math.ceil((TotQty) / (MAXQty)) <>1 ' this is a potential infinite loop? i.e 10/5 <>1 will always be true (go on forever)
For i = 1 To math.ceil((TotQty) / (MAXQty)) ' this is another loop inside the While loop - for i = 1 to 10/5 (i.e. for 1 to 2 e.g. 2 times loop and do something)


' tell report what orientation it is
Set oFormulaFields = oRpt.FormulaFields
For j = 1 To oFormulaFields.Count
Set FirstFormula = oFormulaFields.Item(j)

If UCase(FirstFormula.Name) = "{@GETPO}" Then FirstFormula.Text = "'"& PO &"'"
If UCase(FirstFormula.Name) = "{@GETPACKLIST}" Then FirstFormula.Text = "'"& Pklist &"'"
If UCase(FirstFormula.Name) = "{@GETCUST}" Then FirstFormula.Text = "'"& Cust &"'"
If UCase(FirstFormula.Name) = "{@GETREV}" Then FirstFormula.Text = "'"& Rev &"'"
If UCase(FirstFormula.Name) = "{@GETTOTQTY}" Then FirstFormula.Text = "'"& TOTQTY &"'"
If UCase(FirstFormula.Name) = "{@GETMAXQTY}" Then FirstFormula.Text = "'"& MAXQTY &"'"
If UCase(FirstFormula.Name) = "{@GETCOMMENTS}" Then FirstFormula.Text = "'"& Comments &"'"
If UCase(FirstFormula.Name) = "{@GETBOXQTY}" Then FirstFormula.Text = "'"& BoxQty &"'"

Next

Next

FinishReport

End Function

 

[LaurieHamlin]

Have you tried removing the outer loop. It appears to me that it is not needed.

 

[rwn]

Thanks for the reply, what is an outer loop to remove?

 

[LaurieHamlin]

Take out
While math.ceil((TotQty) / (MAXQty)) <>1 ' this is a potential infinite loop? i.e 10/5 <>1 will always be true (go on forever)

and the last Next

 

[rwn]

there is no While math.ceil((TotQty) / (MAXQty)) <>1. that line is commmented out.
the line is: For i = 1 To math.ceil((TotQty) / (MAXQty)) and it needs to set up to print multi pages based on the calc. so if Tot Qty is 10 and MAX Qty is 3, it would then need to print 4 labels with information on each label.

 

[LaurieHamlin]

I see, apparently I was having a bad day yesterday!
add in here - j = j + 1

If UCase(FirstFormula.Name) = "{@GETBOXQTY}" Then FirstFormula.Text = "'"& BoxQty &"'"

[highlight #729FCF]add here
j = j + 1[/highlight]

Next

 

[rwn]

Ive had those days. I have added as below and same results. meaning only 1 label is printed.
For i = 1 To math.ceil((TotQty) / (MAXQty)) ' this is another loop inside the While loop - for i = 1 to 10/5 (i.e. for 1 to 2 e.g. 2 times loop and do something)


' tell report what orientation it is
Set oFormulaFields = oRpt.FormulaFields
For j = 1 To oFormulaFields.Count
Set FirstFormula = oFormulaFields.Item(j)

If UCase(FirstFormula.Name) = "{@GETPO}" Then FirstFormula.Text = "'"& PO &"'"
If UCase(FirstFormula.Name) = "{@GETPACKLIST}" Then FirstFormula.Text = "'"& Pklist &"'"
If UCase(FirstFormula.Name) = "{@GETCUST}" Then FirstFormula.Text = "'"& Cust &"'"
If UCase(FirstFormula.Name) = "{@GETREV}" Then FirstFormula.Text = "'"& Rev &"'"
If UCase(FirstFormula.Name) = "{@GETTOTQTY}" Then FirstFormula.Text = "'"& TOTQTY &"'"
If UCase(FirstFormula.Name) = "{@GETMAXQTY}" Then FirstFormula.Text = "'"& MAXQTY &"'"
If UCase(FirstFormula.Name) = "{@GETCOMMENTS}" Then FirstFormula.Text = "'"& Comments &"'"
If UCase(FirstFormula.Name) = "{@GETBOXQTY}" Then FirstFormula.Text = "'"& BoxQty &"'"
j = j + 1
Next
next

 

[LaurieHamlin]

Increment i as well.

 

[rwn]

I increment as below:


FirstFormula.Text = "'"& BoxQty &"'"
j = j + 1
i = i + 1
Next
next

And tried

FirstFormula.Text = "'"& BoxQty &"'"
j = j + 1
Next
i= i + 1
next

No success.

 

[LaurieHamlin]

The 2nd one is correct. How are you telling it to print?

 

[rwn]

beleow is the complete code. Lookk for Open Report right after expire cookies.

' Set Defaults
Call SetReportDefaults

Dim NumOfLabels
Dim LabelsUsed

Dim TOTQty
Dim MAXQty
Dim PO
Dim Pklist
Dim Cust
Dim Rev
Dim Comments


Dim sReportToRun
Dim sPart
Dim sPartType

Dim sWhere

Dim oRSFinal
Set oRSFinal = CreateObject("ADODB.Recordset")

Function LoadReport()

' Read Cookies from Filter
NumOfLabels = CInt(GetCookieValue("NumOfLabels"))
LabelsUsed = CInt(GetCookieValue("LabelsUsed"))
TotQty = GetCookieValue("TOTQty")
MAXQty = GetCookieValue("MAXQty")
PO = GetCookieValue("PO")
Pklist = GetCookieValue("PKList")
Cust = GetCookieValue("Cust")
Rev = GetCookieValue("Rev")
Comments = GetCookieValue("Comments")

' Expire Cookies
document.cookie = "NumOfLabels=;" & sExpCookie
document.cookie = "LabelsUsed=;" & sExpCookie
document.cookie = "TOTQty=;" & sExpCookie
document.cookie = "MAXQty=;" & sExpCookie
document.cookie = "PO=;" & sExpCookie
document.cookie = "Pklist=;" & sExpCookie
document.cookie = "Cust=;" & sExpCookie
document.cookie = "Rev=;" & sExpCookie
document.cookie = "Comments=;" & sExpCookie

' Open Report
OpenReport "SH_Labels_PartFM.rpt"

' Clear Out Report
' oRpt.DiscardSavedData

' ' Fill Recordset Final
' FillPartBlankRecords
' While math.ceil((TotQty) / (MAXQty)) <>1 ' this is a potential infinite loop? i.e 10/5 <>1 will always be true (go on forever)

For i = 1 To math.ceil((TotQty) / (MAXQty)) ' this is another loop inside the While loop - for i = 1 to 10/5 (i.e. for 1 to 2 e.g. 2 times loop and do something)


' tell report what orientation it is
Set oFormulaFields = oRpt.FormulaFields
For j = 1 To oFormulaFields.Count
Set FirstFormula = oFormulaFields.Item(j)

If UCase(FirstFormula.Name) = "{@GETPO}" Then FirstFormula.Text = "'"& PO &"'"
If UCase(FirstFormula.Name) = "{@GETPACKLIST}" Then FirstFormula.Text = "'"& Pklist &"'"
If UCase(FirstFormula.Name) = "{@GETCUST}" Then FirstFormula.Text = "'"& Cust &"'"
If UCase(FirstFormula.Name) = "{@GETREV}" Then FirstFormula.Text = "'"& Rev &"'"
If UCase(FirstFormula.Name) = "{@GETTOTQTY}" Then FirstFormula.Text = "'"& TOTQTY &"'"
If UCase(FirstFormula.Name) = "{@GETMAXQTY}" Then FirstFormula.Text = "'"& MAXQTY &"'"
If UCase(FirstFormula.Name) = "{@GETCOMMENTS}" Then FirstFormula.Text = "'"& Comments &"'"
If UCase(FirstFormula.Name) = "{@GETBOXQTY}" Then FirstFormula.Text = "'"& BoxQty &"'"

i= i + 1

Next
j = j + 1
next


FinishReport

End Function

 

[LaurieHamlin]

Sorry, I've been quite busy. Is FinishReport a function you wrote?