Please help

I am not quite sure what is causing my problem, but my "Select" loop is in a contious loop. When it gets to Case 4 the program exits the "select" never even going to Case 5 and 6, I stepped through the program to discover this, and it immediately re-enters the select loop continously. I have pasted a copy of the offending code below. As always any help is greatly appreciated.

Private Sub cmdReport_Click()

Dim sqlStr2 As String
Dim i As Integer
Dim categorycount(6) As Integer


sqlStr2 = "SELECT [Builder Info].Name, [Builder Info].cardellconnectcontract, [Builder Info].cardellconnectcontractend, " & _
"[Builder record Table].Subdivision, [Builder record Table].txtCity, " & _
"[Builder record Table].Address, [Builder record Table].lotblockjobnumber, " & _
"[Builder record Table].cabinetstyle, [Builder record Table].POnumber, " & _
"[Builder record Table].recieveddate, [Builder record Table].category " & _
"FROM [Builder Info] INNER JOIN [Builder record Table] ON " & _
"[Builder Info].Accountnumber = [Builder record Table].Builderaccount;"

rstBuilderInfo.Open sqlStr2

Do While Not (rstBuilderInfo.EOF)
Select Case rstBuilderInfo.Fields("category").Value
Case 1
categorycount(1) = categorycount(1) + 1
Case 2
categorycount(2) = categorycount(2) + 1
Case 3
categorycount(3) = categorycount(3) + 1
Case 4
categorycount(4) = categorycount(4) + 1
Case 5
categorycount(5) = categorycount(5) + 1
Case 6
categorycount(6) = categorycount(6) + 1
End Select
Loop

rstBuilderInfo.Close

For i = 1 To 6
Debug.Print categorycount(i)
Next

You need a .MoveNext

Do While Not (rstBuilderInfo.EOF)
Select Case rstBuilderInfo.Fields("category").Value
Case 1
categorycount(1) = categorycount(1) + 1
Case 2
categorycount(2) = categorycount(2) + 1
Case 3
categorycount(3) = categorycount(3) + 1
Case 4
categorycount(4) = categorycount(4) + 1
Case 5
categorycount(5) = categorycount(5) + 1
Case 6
categorycount(6) = categorycount(6) + 1
End Select

rstBuilderInfo.MoveNext '' <----------- NEED THIS!
Loop

I'd also suggest using a message box (for debugging purposes) showing you what the "category" is before entering the select procedure.

For example:

Select Case ...
MsgBox (rstBuilderInfo("category"))
Case 1
.
.
.
etc.

You'll then be able to make sure the data are being sent to the proper categorycount accumulators (you'll see this when stepping through code in debug mode).

sl8rz

Why use a message box?!?!?! The immediate window servers this purpose.

Incidentally, try not to think of Select as a loop. Its a conditional statement, like If. Its your Do loop that is stuck in an infinite loop. Looking at it this way, you can realize that its because rstBuilderInfo is never reaching EOF, and so you've likely forgotten a method to progress through the recordset.

I'm not trying to be anal about technicalities, I'm just saying that looking at it from the correct perspective makes diagnosing the problem easier.

I've found MessageBoxes can be useful for debugging when you want to pause program execution to give you some information w/o actually breaking the program. Just printing to the Immediate window in a large loop can scroll pretty fast.

Just want to thank every one for there help.