Loop program to populate a field 1

[thebluffer]

Novice user trying to write somewhat simple code but I can not get the program to run. I want to group records by a common "Control Number" then populate a running total for each Control Number in a field called RecordCount. Some "Control Numbers" may have 18 records and others more or less. Thanks for any help.


Private Sub FinancialRecordCount()
Dim PropertyNumber As Integer
Dim PropertyFinancialRecordCount As Integer
Dim dbs As Database, rst As Recordset

Set dbs = OpenDatabase("RECM.mdb&quot
Set rst = dbs.OpenRecordset("SELECT ControlNumber, RecordNumber FROM [Financial Data]&quot
rst.MoveFirst
PropertyNumber = ControlNumber
PropertyFinancialRecordCount = 1
Do While Not EOF()
If PropertyNumber = ControlNumber Then
RecordNumber = PropertyFinancialRecordCount
PropertyFinancialRecordCount = PropertyFinancialRecordCount + 1
rst.MoveNext
Else
PropertyNumber = ControlNumber
PropertyFinancialRecordCount = 1
End If
Loop
dbs.Close
End Sub

 

[Aivars]

You took more bloomers in your codes. Besides if RECM.mdb is external DB better is to create links to this DB's tables. Than it would be worked with this tables like current DB tables.
Absolutely needless is saving data of table record count because these data can take by using simple query.

Example 1. Try this:
call MsgRecordCounts

Sub MsgRecordCounts()
Dim strMsg As String
Dim strList2Col As String

Set rst = CurrentDb.OpenRecordset("SELECT ControlNumber, Count(ControlNumber) AS CountOfControlNumber FROM [Financial Data] GROUP BY ControlNumber;&quot
Do While Not rst.EOF
If strMsg <> &quot;&quot; Then
strMsg = strMsg & vbLf
End If
strMsg = strMsg & rst(0) & vbTab & rst(1)
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
MsgBox &quot;Record count of each 'Control number':&quot; & vbLf & vbLf & strMsg
End Sub

Example 2
Create listbox with two columns on form and set its rowsource=

SELECT ControlNumber, Count(ControlNumber) AS CountOfControlNumber FROM [Financial Data] GROUP BY ControlNumber;

Simple copy SQL string into Row Source box on listbox properties window.

Aivars