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