Do While / Loop fails to compile

[hceonetman]

I have a procedure which I have checked and rechecked for syntax, but when I try to run it I get a compiler error "Loop without Do". The proc populates a table for generating a report. Sorry for the length, but I didn't want to leave anything out. The code follows:


Sub Updt_Aud_Rept()
Dim Cn As New ADODB.Connection
Dim RecHeader As New ADODB.RecordSet
Dim RecDetail As New ADODB.RecordSet
Dim ReptTable As New ADODB.RecordSet
Dim RecSums As New ADODB.RecordSet
Dim Rec_Id As Long
Dim Cash_Amt As Long
Dim Check_Amt As Long
Dim Wire_Amt As Long
Dim Item_Num As Integer
Dim DetailRecs As Integer
Dim Page_Num As Integer
Dim Tot_Pages As Integer
Dim RecCounter As Integer

Set Cn = CurrentProject.Connection
Cn.CursorLocation = adUseClient

RecHeader.Open "Tbl_Receipts_Header", Cn, adOpenDynamic
RecDetail.Open "Tbl_Receipts_Detail", Cn, adOpenDynamic
ReptTable.Open "Tbl_Aud_Revenue_Rcpt", Cn, adOpenKeyset
RecSums.Open "Qry_Receipts_Sums", Cn, adOpenDynamic

RecHeader.MoveFirst

DetailRecs = 0
Cash_Amt = 0
Check_Amt = 0
Wire_Amt = 0

RecSums.MoveFirst
Do While Not RecSums.EOF
DetailRecs = DetailRecs + RecSums![RecCount]
Select Case RecSums![Pmt_Type]
Case "Cash"
Cash_Amt = Cash_Amt + RecSums![Dollars]
Case "Check"
Check_Amt = Check_Amt + RecSums![Dollars]
Case "Wire"
Wire_Amt = Wire_Amt + RecSums![Dollars]
End Select
RecSums.MoveNext
Loop
Tot_Pages = Int(DetailRecs / 2) + (DetailRecs Mod 2)

' Process two items per page, one page at a time.
RecHeader.Find ("Receipt_ID=" & Rec_Id)
RecDetail.Find ("Receipt_ID=" & Rec_Id)
RecCounter = 0
Do While RecCounter < DetailRecs
RecCounter = RecCounter + 1
Page_Num = Page_Num + 1
If RecCounter = 1 Then
ReptTable.AddNew
ReptTable![Receipt_ID] = RecHeader![Receipt_ID]
ReptTable![RR_Num] = RecHeader![RR_Num]
ReptTable![Pg_num] = Page_Num
ReptTable![Begin_Date] = RecHeader![Begin_Date]
ReptTable![End_Date] = RecHeader![End_Date]
ReptTable![Post_Date] = RecHeader![Posting_Date]
ReptTable![Amount] = RecHeader![Amount]
ReptTable![Written_Amt] = RecHeader![Written_Amount]
ReptTable![Initials] = RecHeader![Initials]
ReptTable![Detail_Lines] = RecHeader![Num_Detail]
End 'If RecCounter = 1
ReptTable![Detail_Num] = Chr(64 + RecCounter) ' A,B,C etc
ReptTable![OCA_1] = RecDetail![OCA_Codes]
ReptTable![OL3_1] = RecDetail![Obj_Level_3]
ReptTable![Amt_1] = RecDetail![Amount]
ReptTable![Descr_1] = RecDetail![Description]
ReptTable![Vend_1] = RecDetail![Vendors]

If RecCounter < DetailRecs Then
RecCounter = RecCounter + 1
RecDetail.MoveNext
'Line item 2 for the current page
If Not RecDetail.EOF Then
ReptTable![Detail_Num] = Chr(64 + RecCounter) ' A,B,C etc
ReptTable![OCA_2] = RecDetail![OCA_Codes]
ReptTable![OL3_2] = RecDetail![Obj_Level_3]
ReptTable![Amt_2] = RecDetail![Amount]
ReptTable![Descr_2] = RecDetail![Description]
ReptTable![Vend_2] = RecDetail![Vendors]
End 'If Not RecDetail.EOF
Else
ReptTable![Detail_Num] = &quot;&quot;
ReptTable![OCA_2] = &quot;&quot;
ReptTable![OL3_2] = &quot;&quot;
ReptTable![Amt_2] = &quot;&quot;
ReptTable![Descr_2] = &quot;&quot;
ReptTable![Vend_2] = &quot;&quot;
End 'RecCounter < DetailRecs
RecDetail.MoveNext
Loop

RecHeader.Close
RecDetail.Close
ReptTable.Close
RecSums.Close
Set RecHeader = Nothing
Set RecDetail = Nothing
Set ReptTable = Nothing
Set RecSums = Nothing
Cn.Close
Set Cn = Nothing
End Sub

The first do/while works, but the compiler highlights the &quot;Loop&quot; at the end, just before the cleanup lines. I've deleted and re-entered the code and have changed it to a 'Do /Until' with no luck. Any Ideas?

Thanks,
HCEONETMAN

&quot;Variables won't, Constants aren't&quot;

 

[vbajock]

End 'If RecCounter = 1
and other occurrances like it - you are commenting out 'If RecCounter = 1. Perhaps you mean else if.

 

[hceonetman]

Vbajock,
I added the comments after the &quot;End&quot; statements for debugging, to help me match the If with it's End. I just tried it without them, with the same result. In the VBA editor, the compiler highlights the &quot;Sub&quot; statement in yellow, and highlights the last &quot;Loop&quot; in blue, if that's any help.

HCEONETMAN

 

[CajunCenturion]

vbajock is correct. The proper statement for closing and If-Then-Else statement is End If, not just End. That situation exists for all three of your If statements.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[hceonetman]

Vbajock & CajunCenturion,
A big &quot;oops&quot; there. I was concentrating my search on the Loops and didn't look closely enough at the Ifs.

Thanks, especially for the rapid response
HCEONETMAN

 

[strongm]

'End' in three cases should be 'End If' (or possibly 'Else If'). 'End' means something very different, but it is the fact that you are not closing block if statements that is causing the problem. Let's rename End as Beep (might as well, for all the good End is doing here), simplify the relevant code section, and reformat it slightly to try and illustrate what is happening; effectively, this is what you've got

Do While 1 < 5

If 1 = 1 Then
Beep

If 1 < 5 Then
If Not True Then
Beep
Else
Beep
Loop

 

[vbajock]

End is a valid command, which is why your editor didn't turn red and beep at you.

 

[hceonetman]

Vbajock, et al,
Having fixed the End statements, the module now runs. However, the addnew statement doesn't seem to work. I've added 'adlockoptimistic' to the open statement for 'ReptTable' with no change. The two lines are:

ReptTable.AddNew
ReptTable![Receipt_ID] = RecHeader![Receipt_ID]

The debugger indicates BOF or EOF is true on the second line, and when I go to the table, no record is added. I've looked through previous posts and the MS website and it looks like the proper syntax.

I've also tried using MS's syntax &quot;Addnew, Array(fieldname), Array(Value)&quot;. This will add a new record if I put in a specific value (&quot;1&quot. If I use

ReptTable.Addnew (&quot;Receipt_ID&quot, &quot;RecHeader![Receipt_ID]&quot

I get &quot;Multiple step operation encountered errors, Check each status value&quot;. Seems it should be easier.

Thanks again,
HCEONETMAN
Lubarsky's Law: &quot;There is always one more bug&quot;

 

[hceonetman]

I'll answer my own question. Just as I was looking at the do loop when the problem was with the Ifs, I just found out that the EOF condition is in the table I am getting the data from, not writing to. I think I can handle it now.
Again, thanks for all the help. My VB coding has come a long way the last couple of months, mostly thanks to TekTips. I'm sure this won't be my last question here.

HCEONETMAN

 

[bboffin]

You never assign a value to Rec_Id so your Find statements will fail.

 

[hceonetman]

Bboffin,
Sharp eye! I had it in but commented it out for another reason. Makes a big difference.

HCEONETMAN