Records numbering on Forms & Reports

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

Guest

Is there any easier way to show the consequent number of each record that
appears on the form or report except writing some scripts which I am not good
at?

I have a form "Order" with a subform "Order Details". Subform shows a number
of Products that are relevant to each Order. I have managed to show "Total of
Products" for each Order (by putting "=Count([Products])" into the control
source). But I also want numbering from 1 to .... for each Product in the
"Order Details" subform.

And I want the same to appear on my report.

Please HELP!
Thank you.
Lana
 
Lana said:
Is there any easier way to show the consequent number of each record that
appears on the form or report except writing some scripts which I am not good
at?

I have a form "Order" with a subform "Order Details". Subform shows a number
of Products that are relevant to each Order. I have managed to show "Total of
Products" for each Order (by putting "=Count([Products])" into the control
source). But I also want numbering from 1 to .... for each Product in the
"Order Details" subform.

And I want the same to appear on my report.

Please HELP!
Thank you.
Lana
Click to expand...

You can't actually do it in a form without writing some code. If you can
tell me the name of the field which uniquely identifies each product record
within an order, and it's data type, then I can give you the code. It's
only about 6 lines.
 
Lana,
You should consider adding a LineItemNumber field to you order details table.

Barry Gilbert
 
Brian said:
You can't actually do it in a form without writing some code. If you can
tell me the name of the field which uniquely identifies each product record
within an order, and it's data type, then I can give you the code. It's
only about 6 lines.
Click to expand...

Hi, Brian,

The scrolling at the bottom of my subform shows number of records and
automatically numerates it accordingly (1 to ...). I just want the same
number to appear in front of every Product I have in the subform. I thought
there should be a way to do that.

Anyway, I have a special field called [No], where I manually input numbers
1,2,3,...., but it's annoying and I really dont need this to be stored in my
table (except if for use on my report later).

The unique field for every product is [S_T_Detailed Description].[ID] which
is AutoNumber field.

Table [Orders] has field [PREQ No] which has no duplicates.
Table [S_T_Detailed Description] also has a field [PREQ No] but has
duplicates.
I dont know if its the right way to do, but the two tables are connected
through this field, and I have a subdatasheet in table view so I dont enter
[PREQ No] more than once. Same in the form and subform - I only input that
number once and it stores itself in both tables [Orders] and [S_T_Detailed
Description].

[PREQ No] is a text field (with values like "324-04", "325-04"), but may be
I will somehow change it to number, as I also want it to generate the next
number automatically.

And I use Access 2002.

I would very much appreciate if you could help me.

Lana
 
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.


Lana said:
Brian said:
You can't actually do it in a form without writing some code. If you can
tell me the name of the field which uniquely identifies each product record
within an order, and it's data type, then I can give you the code. It's
only about 6 lines.
Click to expand...

Hi, Brian,

The scrolling at the bottom of my subform shows number of records and
automatically numerates it accordingly (1 to ...). I just want the same
number to appear in front of every Product I have in the subform. I thought
there should be a way to do that.

Anyway, I have a special field called [No], where I manually input numbers
1,2,3,...., but it's annoying and I really dont need this to be stored in my
table (except if for use on my report later).

The unique field for every product is [S_T_Detailed Description].[ID] which
is AutoNumber field.

Table [Orders] has field [PREQ No] which has no duplicates.
Table [S_T_Detailed Description] also has a field [PREQ No] but has
duplicates.
I dont know if its the right way to do, but the two tables are connected
through this field, and I have a subdatasheet in table view so I dont enter
[PREQ No] more than once. Same in the form and subform - I only input that
number once and it stores itself in both tables [Orders] and [S_T_Detailed
Description].

[PREQ No] is a text field (with values like "324-04", "325-04"), but may be
I will somehow change it to number, as I also want it to generate the next
number automatically.

And I use Access 2002.

I would very much appreciate if you could help me.

Lana
Click to expand...
 
Dear Stephen,

I have seen your web-page before I posted my question. This code seems too
complicated for me - I am stupid. That is why I asked if there is some easier
way. May be I will have to manually 1-2-3 my records untill microsoft comes
up with something....

Lana
 
Hi, Brian,

The scrolling at the bottom of my subform shows number of records and
automatically numerates it accordingly (1 to ...). I just want the same
number to appear in front of every Product I have in the subform. I thought
there should be a way to do that.

Anyway, I have a special field called [No], where I manually input numbers
1,2,3,...., but it's annoying and I really dont need this to be stored in my
table (except if for use on my report later).

The unique field for every product is [S_T_Detailed Description].[ID] which
is AutoNumber field.

Table [Orders] has field [PREQ No] which has no duplicates.
Table [S_T_Detailed Description] also has a field [PREQ No] but has
duplicates.
I dont know if its the right way to do, but the two tables are connected
through this field, and I have a subdatasheet in table view so I dont enter
[PREQ No] more than once. Same in the form and subform - I only input that
number once and it stores itself in both tables [Orders] and [S_T_Detailed
Description].

[PREQ No] is a text field (with values like "324-04", "325-04"), but may be
I will somehow change it to number, as I also want it to generate the next
number automatically.

And I use Access 2002.

I would very much appreciate if you could help me.

Lana
Click to expand...

Hi Lana,

I've had a look at Stephen Lebans' solution, and it's similar to what I had
in mind, but even simpler. I suspect that your trepidation is due to the
fact that he has several solutions in his database, and you probably can't
see the wood for the trees.

With due credit to Stephen (I'm sure he will not mind me posting his code
here) I've extracted the relevant function and posted it below. All you
need to do is:

1. Click on the Modules button at the left of your database window.
2. Click on the New button at the top.
3. Paste Stephen's exact code below into the code window that opens
(watch out for and repair any line wraps caused by your newsreader)
4. Go to the design window for your subform. For the text box where you
want the numbers to be displayed, change it's ControlSource property to:

=RowNum([Form])


'======Code begins here=============
'Copyright Stephen Lebans 1999
'May not be resold
'Please include my 1 line Copyright notice
'in your code if you use these functions

'I left a bunch of development code in here in case anyone decides to go
'down the same paths I did.
'Created by Stephen Lebans with help from Chris Bergmans
' Updated by Allen Browne Oct/2002
'Production version of GetLineNumberForm
'Works in Form or SubForm mode
'Set controlsource of unbound Text box to
'= RowNum([Form])
'Type exactly as above

Public Function RowNum(frm As Form) As Variant
On Error GoTo Err_RowNum
'Purpose: Numbering the rows on a form.
'Usage: Text box with ControlSource of: =RowNum([Form])

With frm.RecordsetClone
.Bookmark = frm.Bookmark
RowNum = .AbsolutePosition + 1
End With

Exit_RowNum:
Exit Function

Err_RowNum:
If Err.Number <> 3021& Then 'Ignore "No bookmark" at new row.
Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
End If
RowNum = Null
Resume Exit_RowNum
End Function
'======Code ends here=============
 
Lana said:
Is there any easier way to show the consequent number of each record that
appears on the form or report except writing some scripts which I am not good
at?

I have a form "Order" with a subform "Order Details". Subform shows a number
of Products that are relevant to each Order. I have managed to show "Total of
Products" for each Order (by putting "=Count([Products])" into the control
source). But I also want numbering from 1 to .... for each Product in the
"Order Details" subform.

And I want the same to appear on my report.

Please HELP!
Thank you.
Lana
Click to expand...

Hello again Lana. We are all overlooking the fact that you want to do this
in Reports as well as Forms. The good news is that for Reports, no code is
needed! If you look up "RunningSum" in Access help, it tells you exactly
how to do it.
 
Thank you Brian!
You are so nice to me!
I did exactly as you said and it WORKS!! Both the Form and the Report!
You are the Best Man in the Net!
 
Back
Top