×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Loop is not Funtioning

## Loop is not Funtioning

(OP)
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.

' Open Report
OpenReport "SH_Labels_PartFM.rpt"

' Clear Out Report

' ' 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) = "{@GETBOXQTY}" Then FirstFormula.Text = "'"& BoxQty &"'"

Next

Next

FinishReport

End Function

### RE: Loop is not Funtioning

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

### RE: Loop is not Funtioning

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

### RE: Loop is not Funtioning

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

### RE: Loop is not Funtioning

(OP)
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.

### RE: Loop is not Funtioning

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 &"'"

j = j + 1

Next

### RE: Loop is not Funtioning

(OP)
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) = "{@GETBOXQTY}" Then FirstFormula.Text = "'"& BoxQty &"'"
j = j + 1
Next
next

### RE: Loop is not Funtioning

Increment i as well.

### RE: Loop is not Funtioning

(OP)
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.

### RE: Loop is not Funtioning

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

### RE: Loop is not Funtioning

(OP)
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 sReportToRun
Dim sPart
Dim sPartType

Dim sWhere

Dim oRSFinal

' Open Report
OpenReport "SH_Labels_PartFM.rpt"

' Clear Out Report

' ' 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) = "{@GETBOXQTY}" Then FirstFormula.Text = "'"& BoxQty &"'"

i= i + 1

Next
j = j + 1
next

FinishReport

End Function

### RE: Loop is not Funtioning

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!