Creating numbering on the fly

[Mo]

In a form and sub-form, where the recordsource of the s-form is based on an
SQL statement (rather that a query), the user would like to see the rows
that are listed in the s-form numbered, so that instead of:

smith, sarah
jones, brian
davies, john
etc...

she sees:

1 smith, sarah
2 jones, brian
3 davies, john
etc...

The table of which the recordsource of the s-form is a subset, already
contains an autonumber. Anyway, I'd not be able to use it since the numbers
would appear in a random fashion.

Is there a way in code to create some sort of 'numbering' of the rows listed
in my subform on-the-fly? Obviously the next time the user opens the form,
it'll need to start numbering from '1' again.

Here is the code so far:

Dim db As DAO.Database
Dim strSQL As String
Dim rst As DAO.Recordset
Set db = CurrentDb()

If IsNull(Me.txt_start_date) Then
MsgBox ("You have not entered a Start Date"), vbOKOnly, "Missing Date"
Me.txt_start_date.SetFocus
Else
If IsNull(Me.txt_end_date) Then
MsgBox ("You have not entered an End Date"), vbOKOnly, "Missing Date"
Me.txt_end_date.SetFocus
Else
strSQL = "SELECT Surname, Forename, DoB, DateOfMerge, FreeT4, TSH,
SampBarCode, TrialGroup "
strSQL = strSQL & "FROM TBL_REGISTRATION0003 "
strSQL = strSQL & "WHERE DateOfMerge >= #" & Month(Me!txt_start_date) & "/"
& Day(Me!txt_start_date) & "/" & Year(Me!txt_start_date) & "#"
strSQL = strSQL & " AND DateOfMerge <= #" & Month(Me!txt_end_date) & "/" &
Day(Me!txt_end_date) & "/" & Year(Me!txt_end_date) & "#"
strSQL = strSQL & " AND TSH >= " & (Me!txt_TSH) & ""
strSQL = strSQL & " AND FreeT4 <= " & (Me!txt_FT4) & ""
strSQL = strSQL & " ORDER BY DateOfMerge, Surname ASC"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.RecordCount <> 0 Then
Me.frm_freeT4sub.Form.RecordSource = strSQL
Forms![frm_positives]![frm_freeT4sub].Requery
Else
Me.frm_freeT4sub.Form.RecordSource = ""
MsgBox ("No matches found!"), vbOKOnly, "No matches"
Me.txt_start_date.SetFocus
End If
End If

rst.Close
Set rst = Nothing
Set db = Nothing

End If

 

[Mo]

Mo

If you look on the Microsoft Support website under Access, there are several articles on using Dsum for creating a count on forms

Brandon

 

[Cheryl Fischer]

You might want to check out MVP Stephen Lebans' techniques for row
numbering:

http://www.lebans.com/rownumber.htm

 

[Van T. Dinh]

See the Microsoft Knowledge Base:

http://support.microsoft.com/?id=120608

and Stephen Lebans' article:

http://www.lebans.com/rownumber.htm

 

[Mo]

Thanks very much to all who responded!

Mo