We help IT Professionals succeed at work.
Get Started

Recordset returning null fields.

pwdells
pwdells asked
on
503 Views
Last Modified: 2012-05-11
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:  

Error Screen
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)

Open in new window

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE