Record Count problem

[Skip Bisconer]

I have a linked Excel workbook "CurrentStatement" with monthly credit card transactions that get parsed into several different cardholder transaction "CurrentCardholder[name]" Excel spreadsheets that are linked to Access.

Each cardholder must verify the transactions and assign GL codes to each line of data and return the spreadsheet.When each spreadsheet is returned the data is appended to an Accounts Payable table "CompletedRecords" and a "Annual History" Table. Then at the end of the month the the CurrentStatement is purged for a new month.

I need to make sure the record count of CompletedRecords and CurrentStatement are the same prior to purging the old file. I now have two unbound texboxes that provide me with the information but it's on the form's onload event so if the form is already opened and other cardholders infomation has been appended I can't get a count of CompletedRecords until I close and reopen the form.

I have tried it in every form event that made sense but it doesn't do anything. I also tried to requery the form but the textboxes are unbound so that didn't work either. So I assume I need that record count to happen when the cardholder data is updated but I don't know how to state it in VBA. Below is the record update code. I would appreciate any advice given on this. Copy of form is attached.

Private Sub Bisconer_Click()
'On Error GoTo ErrorHandler
'Post to AnnualTransantionTable and Completed Transaction Table
DoCmd.SetWarnings False
DoCmd.OpenQuery "PostToCompletedMonthlyTransaction11152", acViewNormal
DoCmd.OpenQuery "PostToHistoryCurrentCharges11152", acViewNormal
DoCmd.SetWarnings True
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = False
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesBisconer.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
xlWB.Worksheets("CurrentCharges").Unprotect Password:=("1234")
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11152", , dbOpenDynamic)
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i + 1).Value = ""
  .Range("B" & i + 1).Value = ""
  .Range("C" & i + 1).Value = ""
  .Range("D" & i + 1).Value = ""
  .Range("E" & i + 1).Value = ""
  .Range("F" & i + 1).Value = ""
  .Range("G" & i + 1).Value = ""
  .Range("H" & i + 1).Value = ""
  .Range("I" & i + 1).Value = ""
  .Range("J" & i + 1).Value = ""
  .Range("K" & i + 1).Value = ""
  .Range("L" & i + 1).Value = ""
  .Range("M" & i + 1).Value = ""
  .Range("N" & i + 1).Value = ""
 
  End With
i = i + 1
rs.MoveNext
Loop
 
xlWB.Worksheets("CurrentCharges").Protect Password:=("1234")
objXL.ActiveWorkbook.Save
objXL.Application.Quit
rs.Close
db.Close
'DoCmd.Requery "Post and Close Individual Statements"
 
MsgBox "Purge Completed"
 Exit Sub
ErrorHandler:
    ' Display error information.
    MsgBox "Error number " & Err.Number & ": " & Err.Description
    ' Resume with statement following occurrence of error.
    Resume Next
End Sub