Record number in subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Newbie using 2003. I have 2 tables, just to make it easy on me,"One" and
"Many". In a subform where anywhere from 5 to 10 records of "many" are
displayed I want to use a text box to show the record number next to the
"many" type records, ie. the number between the navigation buttons (which I
will remove). The input is numerical and will not be sorted. I do not need
the cumulative record number.
Thanks for all your help, Bill
 
See:
http://www.lebans.com/rownumber.htm
Rownumber.zip is a database containing functions for the automatic row
numbering of Forms, SubForms and Queries.

Updated Oct. 13 by Allen Browne. Includes error handling and cleaned
code.



Here's an update to the Serialize function by Peter Schroeder:

Good to hear. FWIW, here's the version I came up with today, based off
of your code and Ken's(Getz) suggestion, with a few changes:


Function Serialize(qryname As String, keyname As String, keyvalue) As
Long

Dim rs As Recordset


On Error GoTo Err_Serialize

Set rs = CurrentDb.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)

rs.FindFirst Application.BuildCriteria(keyname,
rs.Fields(keyname).Type, keyvalue)

Serialize = Nz(rs.AbsolutePosition, -1) + 1


Err_Serialize:

rs.Close

Set rs = Nothing

End Function


Peter Schroeder


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
BillS3 said:
Newbie using 2003. I have 2 tables, just to make it easy on me,"One"
and "Many". In a subform where anywhere from 5 to 10 records of
"many" are displayed I want to use a text box to show the record
number next to the "many" type records, ie. the number between the
navigation buttons (which I will remove). The input is numerical and
will not be sorted. I do not need the cumulative record number.
Thanks for all your help, Bill
Click to expand...

You have to understand first that the "record number" is essentially
meaningless and circumstantial, since it is nothing inherent in the
record, but instead is entirely dependent on the order in which the
records happen to be sorted at the moment.

I've never had any occasion to implement row numbers myself, but in
answer to a similar question recently I threw together the following
function, which seems to work. It doesn't require that the form's
recordsource have a primary key, nor that you impose any particular sort
order. I'm not sure whether it will work in an ADP or not, as I haven't
tried it.

'----- start of function code -----
Function FormRecNo(frm As Form) As Variant

' Return the "record number" for each record on a
' continuous form. <frm> is a reference to the form
' itself.

Const conNO_CURRENT_RECORD = 3021

On Error GoTo Err_Handler

Dim rs As Object
Set rs = frm.RecordsetClone

With rs
.Bookmark = frm.Bookmark
FormRecNo = .AbsolutePosition + 1
End With

Exit_Point:
Set rs = Nothing
Exit Function

Err_Handler:
If Err.Number = conNO_CURRENT_RECORD Then
On Error Resume Next
rs.MoveLast
FormRecNo = rs.RecordCount + 1
Else
FormRecNo = CVErr(Err.Number)
End If
Resume Exit_Point

End Function
'----- end of function code -----

Then you can put a calculated control on the form that calls the
function, like this:

=FormRecNo([Form])

I haven't tested the above function thoroughly, but it seems to work in
preliminary tests. If all you want is record numbers to display on a
form, it seems to me to be a bit more flexible than approaches that
involve modifying the form's recordsource query, and it responds
automatically to any sorting or filtering you do to the form.
 
Stephen .. Your code worked perfectly. Being so new I used the simple posting
by Brian in the 10/14/2004 of your code. Thanks, Thanks. One problem down,
many more to follow!
 
Thanx Dirk for your suggestion. I have tried something like that and it works
fine for forms. But I need it to work in a report. Can you suggest a way out?
--
Glint


Dirk Goldgar said:
BillS3 said:
Newbie using 2003. I have 2 tables, just to make it easy on me,"One"
and "Many". In a subform where anywhere from 5 to 10 records of
"many" are displayed I want to use a text box to show the record
number next to the "many" type records, ie. the number between the
navigation buttons (which I will remove). The input is numerical and
will not be sorted. I do not need the cumulative record number.
Thanks for all your help, Bill
Click to expand...

You have to understand first that the "record number" is essentially
meaningless and circumstantial, since it is nothing inherent in the
record, but instead is entirely dependent on the order in which the
records happen to be sorted at the moment.

I've never had any occasion to implement row numbers myself, but in
answer to a similar question recently I threw together the following
function, which seems to work. It doesn't require that the form's
recordsource have a primary key, nor that you impose any particular sort
order. I'm not sure whether it will work in an ADP or not, as I haven't
tried it.

'----- start of function code -----
Function FormRecNo(frm As Form) As Variant

' Return the "record number" for each record on a
' continuous form. <frm> is a reference to the form
' itself.

Const conNO_CURRENT_RECORD = 3021

On Error GoTo Err_Handler

Dim rs As Object
Set rs = frm.RecordsetClone

With rs
.Bookmark = frm.Bookmark
FormRecNo = .AbsolutePosition + 1
End With

Exit_Point:
Set rs = Nothing
Exit Function

Err_Handler:
If Err.Number = conNO_CURRENT_RECORD Then
On Error Resume Next
rs.MoveLast
FormRecNo = rs.RecordCount + 1
Else
FormRecNo = CVErr(Err.Number)
End If
Resume Exit_Point

End Function
'----- end of function code -----

Then you can put a calculated control on the form that calls the
function, like this:

=FormRecNo([Form])

I haven't tested the above function thoroughly, but it seems to work in
preliminary tests. If all you want is record numbers to display on a
form, it seems to me to be a bit more flexible than approaches that
involve modifying the form's recordsource query, and it responds
automatically to any sorting or filtering you do to the form.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top