Recordset returning null fields.

Just recently, my code seems to be acting up.  I run the SELECT query and set it into a recordset.  (DAO)  It cycles through 8-12 times and then the variables become NULL and it says that:  


Here is my code.  It seems to error during the 8th round and there are 32 records returned.  When I run the SQL straight (not through the code) it returns the 32 records with no NULL fields. :

On Error GoTo ERR_Allocate

Dim rs_ALLOC, rs_FCST_BATCH As DAO.Recordset
Dim str_ALLOC, str_FCST_BATCH, str_ADD_ALLOC As String
Dim OF, WF, CS, AC, RF, RS As Double
Dim dbl_ALLOC, dbl_rStock, dbl_rFCST
Dim a, i, TMP_STK_ID, fb As Integer
Dim str_TMP_STOCK, str_ADD_UN As String
Dim str_Update_STOCK, str_REM_REQ, str_Update_Alloc, str_CLOSE As String

lstSplash.AddItem ("MODULE: ALLOCATE")

bln_FULFILL = False
bln_WEND = False

'get records to allocate
str_FCST_BATCH = "SELECT TMP_FCST_BATCH.FCST_ID, TMP_FCST_BATCH.ID, TMP_FCST_BATCH.ABC, " & _
        "TMP_FCST_BATCH.FCST_QTY, TMP_FCST_BATCH.BASIC_MAT " & _
    "FROM TMP_FCST_BATCH " & _
    "WHERE TMP_FCST_BATCH.PASS = True " & _
    "ORDER BY TMP_FCST_BATCH.CALEN_PER, TMP_FCST_BATCH.BASIC_MAT, TMP_FCST_BATCH.ABC DESC , " & _
        "TMP_FCST_BATCH.FCST_QTY "

Set rs_FCST_BATCH = CurrentDb.OpenRecordset(str_FCST_BATCH)

If rs_FCST_BATCH.RecordCount <> 0 Then
    rs_FCST_BATCH.MoveLast
    fb = rs_FCST_BATCH.RecordCount
    'MsgBox fb
    rs_FCST_BATCH.MoveFirst
End If

'MsgBox "records to match locally:" & fb

For a = 1 To fb
    int_ID = rs_FCST_BATCH.Fields(1)
    int_FCST_ID = rs_FCST_BATCH.Fields(0)
    dbl_FCST_QTY = rs_FCST_BATCH.Fields(3)
    str_ABC = rs_FCST_BATCH.Fields(2)
    str_BAS_MAT = rs_FCST_BATCH.Fields(4)
    
    'MsgBox int_ID & "  and " & int_FCST_ID
    str_ALLOC = "SELECT YSOC_CONSOLIDATE.ID, TMP_FCST_BATCH.ID, YSOC_CONSOLIDATE.DIRTY_MAT, " & _
                "YSOC_CONSOLIDATE.PLANT, YAV_SUPPLYLOC.SLOC_PR, YSOC_CONSOLIDATE.ABC_RATE, " & _
                "YSOC_CONSOLIDATE.HARVEST_DATE, IIf(IsNull([REM_QTY]),[QTY],[REM_QTY]) AS AVAIL_QTY " & _
        "FROM TMP_STOCK " & _
            "INNER JOIN (TMP_FCST_BATCH " & _
                "INNER JOIN (YAV_SUPPLYLOC " & _
                    "INNER JOIN YSOC_CONSOLIDATE " & _
                            "ON (YSOC_CONSOLIDATE.MPG = YAV_SUPPLYLOC.MPG) " & _
                            "AND (YAV_SUPPLYLOC.Plant = YSOC_CONSOLIDATE.PLANT)) " & _
                        "ON (TMP_FCST_BATCH.BASIC_MAT = YSOC_CONSOLIDATE.DIRTY_MAT) " & _
                        "AND (TMP_FCST_BATCH.MPG = YAV_SUPPLYLOC.MPG) " & _
                        "AND (TMP_FCST_BATCH.COUNTRY = YAV_SUPPLYLOC.CO)) " & _
                    "ON (TMP_STOCK.BATCH_ID = TMP_FCST_BATCH.ID) " & _
                    "AND (TMP_STOCK.STOCK_ID = YSOC_CONSOLIDATE.ID) " & _
        "WHERE (((YSOC_CONSOLIDATE.bln_IN_STOCK) = True)) " & _
        "GROUP BY YSOC_CONSOLIDATE.ID, TMP_FCST_BATCH.ID, YSOC_CONSOLIDATE.DIRTY_MAT, YSOC_CONSOLIDATE.PLANT, " & _
            "YAV_SUPPLYLOC.SLOC_PR, YSOC_CONSOLIDATE.ABC_RATE, YSOC_CONSOLIDATE.HARVEST_DATE, " & _
            "IIf(IsNull([REM_QTY]),[QTY],[REM_QTY]) " & _
        "HAVING (((TMP_FCST_BATCH.ID) = " & rs_FCST_BATCH.Fields(1) & ") " & _
            "And ((YSOC_CONSOLIDATE.ABC_RATE) = 'A' " & _
                "Or (YSOC_CONSOLIDATE.ABC_RATE) = 'B' " & _
                "Or (YSOC_CONSOLIDATE.ABC_RATE) = 'C') " & _
            "And ((IIf(IsNull([REM_QTY]), [QTY], [REM_QTY])) Is Not Null)) " & _
        "ORDER BY YSOC_CONSOLIDATE.DIRTY_MAT, YAV_SUPPLYLOC.SLOC_PR, YSOC_CONSOLIDATE.ABC_RATE DESC , " & _
            "YSOC_CONSOLIDATE.HARVEST_DATE, IIf(IsNull([REM_QTY]),[QTY],[REM_QTY]) "

    'MsgBox str_ALLOC
    
    Set rs_ALLOC = CurrentDb.OpenRecordset(str_ALLOC)

Select all Open in new window