You don't say what type of form it is, so I assume it's the row numbers of a continous sub form. These can seem to be a bit tricky, because you cant use VBA code directly on a certain 'cell' in the form. You'll have to do this through the sub form query source. If the sub form don't use a query, then you should create one.
Now, in an empty column in the query - put something like this:
RowCount: RowNumber([YourMainFormPrimaryKeyNameHere]);[YourSubFormPrimaryKeyHere]) Both these fields must be present in the query.
(My norwegian version of Access uses semi colon as a separator for arguments in a query, and I think you must replace the semi with a comma.)
Open a standard module (or create a new one) and copy/paste the following function.....
Function RowNumber(MainFormPrimaryKey As Long, SubformPrimaryKey As Long) As String
Dim A As Integer
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database
Set dbs = CurrentDb
strSQL = "SELECT MainFormPrimaryKey, SubformPrimaryKey " _
& "FROM tblSubForm " _
& "WHERE MainFormPrimaryKey = " & MainFormPrimaryKey _
& " ORDER BY SubFormPrimaryKey;"
Set rst = dbs.OpenRecordset(strSQL)
A = 1
Do While Not (rst.EOF)
If rst!SubformPrimaryKey = SubformPrimaryKey Then Exit Do
A = A + 1
rst.MoveNext
Loop
RowNumber = "This is row" & Str(A)
rst.Close
Set dbs = Nothing
End Function
.....and save it.
This uses DAO Objects 3.6, so you'll need to have this library selected in the Reference section of the VB editor. Since the priority of my DAO library is placed as no. 3 from the top (above ADO), I do not need to use ie. Dim rst As DAO.Recordset. Keep this in mind.
The primary key field used is of data type Numeric (Long), but can easily be changed.
You can now use the 'field' RowCount in your sub form from the field list. You probably want to set the Locked property to True, and TabStop to 'No'
An error handler should also be added.
Post back if you have any questions.
hth
Gerhard
