Avatar of pwdells
pwdellsFlag for United States of America

asked on 

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:  

User generated image
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

Microsoft Access

Avatar of undefined
Last Comment
pwdells
ASKER CERTIFIED SOLUTION
Avatar of pwdells
pwdells
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of pwdells
pwdells
Flag of United States of America image

ASKER

I found the answer before anyone responded.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo