Next without For using IF at EOF or BOF (1 Viewer)

[MackMan]

Below is a (difficult for me) piece of code I'm using to write to a table using DAO, while referencing the main ID's of each record.

All is OK and seems to work fine until I get to the highlighted line, and then when there are no more records, I get an error no more records.

I put an if statement saying that if EOF or BOF then move to the next i.

but it threw out a Next without For error.

Any advice is always appreciated.

Dim Cancel As Integer 
Dim dbsMyAccounts As DAO.Database 
Dim rstTopLines As DAO.Recordset 
Dim rstDetailsSplits As DAO.Recordset 
Dim GETID As Long 
Dim GETSPLitID As Long 
Dim OPENTopLines As String 
Dim OPENdetailSplits As String 
Dim strsql As String 
Dim strSQLSub As String
Dim i As Integer ' to Z  is the number of occurrences
Dim z As Double
Dim ir As Integer ' to X  is the number of splits
Dim x As Double
Dim StartDate As Date 
Dim Intv As String
Dim Every As Double ' the frequency of intervals
 

StartDate = Me.TransDate
z = Me.RecurCount
x = DCount("BilldetailsplitID", "tbl_BillsdetailsSplits", "billdetailentryID = " & Me.BillDetailEntryID)
Intv = Me.PeriodTypeID
Every = Me.PeriodFreq
 
Set dbsMyAccounts = CurrentDb
 
For i = 1 To z
 
strsql = "INSERT INTO [tbl_TopLines] ( BillDetailEntryID, AccountID, EntryType, PayeeID, ChequeNo, BillPaymentMethodID, Transdate, BillPeriodTypeID, CategoryID, SubCategoryID, ExpenseTypeID, Credit, Debit, Amount, BillFixOrEstimatedID, " & _
    "S, Comment, BillTypeID) " & _
    "SELECT BillDetailEntryID, AccountID, EntryType, PayeeID, ChequeNo, PaymentMethodID, " & Format(DateAdd(Intv, (i * Every) - 1, StartDate), "\#mm\/dd\/yyyy\#") & " AS TransDate, PeriodTypeID, CategoryID, " & _
    "SubCategoryID, ExpenseTypeID, Credit, Debit, Amount, BillFixOrEstimatedID, S, Comment, BillTypeID " & _
    "FROM [tbl_BILLsDetailsTopLines] WHERE BillDetailEntryID = " & Me.BillDetailEntryID & ";"
    Debug.Print strsql
    DBEngine(0)(0).Execute strsql, dbFailOnError
    
OPENTopLines = "SELECT * FROM tbl_toplines WHERE BilldetailentryID = " & Me.BillDetailEntryID 
Set rstTopLines = dbsMyAccounts.OpenRecordset(OPENTopLines, dbOpenDynaset)

 rstTopLines.MoveLast
GETID = rstTopLines!EntryID

 OPENdetailSplits = "SELECT * FROM tbl_BILLSDetailsSplits WHERE BilldetailentryID = " & Me.BillDetailEntryID
Set rstDetailsSplits = dbsMyAccounts.OpenRecordset(OPENdetailSplits, dbOpenDynaset)
 
    ' No records, do what is required when none = nothing
     If rstDetailsSplits.EOF And rstDetailsSplits.BOF Then
    
    Else
    'finds the first entry of billentry ID and gets the main entryID number
    rstDetailsSplits.MoveFirst
            
         
    GETSPLitID = rstDetailsSplits!BillDetailSplitID
    End If
 
      For ir = 1 To x ' writes the data to the related table of the above SQL statement
 
       strSQLSub = "INSERT INTO [tbl_Splits] ( BillDetailEntryID, BILLDetailSplitID, EntryID, EntryType, AccountID, PayeeID, ExpenseTypeID, TransDate, ChequeNo, CategoryID, SubCategoryID, Credit, Debit, Amount, Comment) " & _
            "SELECT " & Me.BillDetailEntryID & " AS BillDetailEntryID, " & GETSPLitID & " AS BillDetailSplitID, " & GETID & " AS EntryID, EntryType , AccountID, PayeeID, ExpenseTypeID, " & Format(DateAdd(Intv, (i * Every) - 1, StartDate), "\#dd\/mm\/yyyy\#") & " AS TransDate, ChequeNo, CategoryID, SubCategoryID, Credit, Debit, Amount, Comment " & _
            "FROM [tbl_BillsDetailsSplits] WHERE BillDetailSplitID = " & GETSPLitID & ";"
            'Debug.Print strSQLSub
            DBEngine(0)(0).Execute strSQLSub, dbFailOnError
  
 
[COLOR=red]rstDetailsSplits.MoveNext[/COLOR]
  
 
      GETSPLitID = rstDetailsSplits!BillDetailSplitID
      Next ir
 
Next I
 
 dbsMyAccounts.Close
  
 
If CurrentProject.AllForms("frmBILLS").IsLoaded Then
Forms!frmBILLS!frmBILLSList.Requery
End If
DoCmd.Close acForm, "frmBILLSaddedit", acSaveYes
  
  
 
'CLEARS EVERYTHING:
Cancel = 0
Set dbsMyAccounts = Nothing
Set rstTopLines = Nothing
Set rstDetailsSplits = Nothing
GETID = 0
GETSPLitID = 0
OPENTopLines = vbNullString
OPENdetailSplits = vbNullString
strsql = vbNullString
strSQLSub = vbNullString
i = 0
z = 0
ir = 0
x = 0
StartDate = vbNullString
Intv = vbNullString
Every = 0

Ive had a look at all the other posts in the forum regards to this, and no matter what I've tried, I've not had luck at the highlighted line, saying that if there are no more records move on to the next i statement below it.

I added the following if statement, and it's now thrown me completely.

 [COLOR=darkred]If Not rstDetailsSplits.EOF And Not rstDetailsSplits.BOF Then[/COLOR]
 [COLOR=darkred]rstDetailsSplits.MoveNext
GETSPLitID = rstDetailsSplits!BillDetailSplitID
Next ir
End If[/COLOR]

 

[MarkK]

You are testing for BOTH .eof AND .bof, which are only ever BOTH true if the recordset has no records.

But imagine a recordset with one record, and you are on record one. Then you ".movenext," so now .eof is true, .bof is false, your expression will evaluate to True, and your code will try to move past the end of the recordset. -> error.

You should only do . . .

 If Not rstDetailsSplits.EOF Then
   rstDetailsSplits.MoveNext

 

[arnelgp]

If Not (rstDetailsSplits.EOF And rstDetailsSplits.BOF) Then
rstDetailsSplits.MoveNext
'test it again after moving the record pointer
If Not (rstDetailsSplits.EOF) Then
GETSPLitID = rstDetailsSplits!BillDetailSplitID
Else
'what then do we do if we reach EOF
End if
Next ir
End If

 

[MackMan]

Thanks for the prompt response here. I need to test for any records on this 2nd "For i" statement embedded within the 1st (not sure of that makes a difference or not, but I did read somewhere that there are limitations.) anyway, so I put that if statement in to check for any records, if so move next and get the SplitID number, if not go to the "next i" statement. but it's still throwing out the Compile Error Next without for.

All confusing.. and VBA is still something I'm getting to grips with.. so please.. bare with me.


This is the part it's throwing up a compile error. ..

     If rstDetailsSplits.EOF And rstDetailsSplits.BOF Then
 
     rstDetailsSplits.MoveNext
        If Not rstDetailsSplits.EOF Then
            GETSPLitID = rstDetailsSplits!BillDetailSplitID
        Else
        End If
       Next ir
    End If

    Next i

 

[arnelgp]

        If Not (rstDetailsSplits.EOF And rstDetailsSplits.BOF) Then
     
            rstDetailsSplits.MoveNext
            If Not rstDetailsSplits.EOF Then
                GETSPLitID = rstDetailsSplits!BillDetailSplitID
            Else
            End If
        End If
    Next ir    
Next i

 

[MackMan]

Thank you . Looking at the easiest things can sometimes be the most difficult tasks.