Cycle counting your stock
This sample assumes you have a table of parts that are categorised by warehouse and abc classification (tblCycleCountStockRecords ) . You would have decided how many times you want to count the parts per year (tblCountRotation) and divided that by the number of counts in a year. The code in this function will ensure that for each count you are cycling through your stock records and counting the parts in rotation and getting the number of hits per year per classification.
I also load this cycle count data to a handheld computer and have the operator count the stock by location and upload the results for analysis and live system updating.
A user screen is presented to the user and they can select the warehouse to add to the cycle count file.
Private Sub cmdAddCount_Click()
'On Error GoTo ErrorHandler
Dim rst As Recordset, rstSource As Recordset, rstTarget As Recordset
Dim tmpCount As Long, tmpRotation As Long, tmpCalcRecord, tmpStartingRecord
If MsgBox("Do you want to add the warehouse " & Me.cboWarehouse & " to the count file", vbYesNo) = vbNo Then
Exit Sub
End If
Set rst = CurrentDb.OpenRecordset("Select * from tblCycleSelect where WAREHOUSE=" & Chr(34) & Me.cboWarehouse & Chr(34))
If rst.RecordCount> 0 Then
MsgBox "This warehouse is already in the count file" & vbCrLf & "Please finish the current count before adding this warehouse"
Exit Sub
End If
Set rst = CurrentDb.OpenRecordset("Select * from tblCountRotation where C_WH=" & Chr(34) & Me.cboWarehouse & Chr(34))
If rst.RecordCount = 0 Then
MsgBox "No Details match the selected warehouse "
Exit Sub
End If
rst.MoveFirst
Set rstTarget = CurrentDb.OpenRecordset("select * from tblCycleCountStockRecords ")
tmpRotation = Me.txtRotation + 1
Do While Not rst.EOF
'set the number of lines to count for this abc catgeory
tmpCount = rst!C_LineItemsPerCount
'select the target records
Set rstSource = CurrentDb.OpenRecordset("select * from tblCycleCountStockRecords where WAREHOUSE=" & Chr(34) & rst!C_WH & Chr(34) & _
" and I1ABC=" & Chr(34) & rst!C_ABC & Chr(34) & " Order by Control ASC")
'this calc the number of counts by the count quantity
tmpCalcRecord = (rst!C_LineItemsPerCount * tmpRotation)
If rstSource.RecordCount = 0 Then
GoTo Skip_next
End If
If Int(tmpCalcRecord / rst!C_NoofParts) > 0 Then ' check that the calced position is less than total record
tmpStartingRecord = (tmpCalcRecord - (rst!C_NoofParts * Int(tmpCalcRecord / rst!C_NoofParts))) - rst!C_LineItemsPerCount ' establish starting position
If tmpStartingRecord < 0 Then tmpStartingRecord = rst!C_NoofParts + tmpStartingRecord
Else
tmpStartingRecord = tmpCalcRecord - rst!C_LineItemsPerCount ' less than total record start = calced minus count qty
End If
rstSource.Move tmpStartingRecord
Do While tmpCount >= 0
If rstSource.EOF Then
rstSource.MoveFirst
End If
'add the records to the count file
With rstTarget
Application.Echo True, "Processing Part Number: " &rstSource!StkCode
.AddNew
!StkCode= rstSource!StkCode
!Warehouse = rstSource!Warehouse
!Bin = rstSource! Bin
!Description = rstSource! Description
!UnitofMeasure = rstSource! UnitofMeasure
!ABC = rstSource!ABC
.Update
End With
rstSource.MoveNext
tmpCount = tmpCount - 1
Loop
'update the rotation on the master file
rst.Edit
rst!C_CurrentRotation = tmpRotation
rst.Update
Skip_next:
'next abc
rst.MoveNext
Loop
MsgBox "Finsihed!"
Set rst = Nothing
Set rstSource = Nothing
Set rstTarget = Nothing
Exit Sub
ErrorHandler:
MsgBox "An error occurred when loading the data " & vbCrLf &
" Error Number: " & Err.Number & vbCrLf & " Details: " & Err.Description
DoCmd.Hourglass False
Exit Sub
End Sub
Leave a Reply
Want to join the discussion?Feel free to contribute!