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