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)
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.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE