-1

I have a form of view type "continuous forms" that is returning records based on a query, and I want the records to have a number label like 1, 2, 3.. in the order listed:

form

Is there any way to generate a label or textbox automatically there?

Any help is appreciated! Thanks

EDIT: Heres the query code:

    Dim qd As DAO.QueryDef
    Set qd = CurrentDb.CreateQueryDef("pairsOrdered", "select * from allPairs order by Count desc")

    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("pairsOrdered")
    If Not rs.EOF Then
        Me.Label22.Visible = 0
    End If

    If Me.OpenArgs = "25" Then
        RecordSource = "select top 25 * from pairsOrdered"
    End If

    If Me.OpenArgs = "50" Then
        RecordSource = "select top 50 * from pairsOrdered"
    End If

    If Me.OpenArgs = "all" Then
        RecordSource = "select * from pairsOrdered"
    End If
Shubham
  • 949
  • 6
  • 21
  • 29
  • "Access VBA: How to display record count in a continuous form" that's is another identical question of yours isn't it ? – iDevlop Aug 04 '11 at 20:53
  • yea...i thougth i might get answers if i asked in a different way... – Shubham Aug 04 '11 at 21:28
  • The correct tag is `ms-access`, I've edited it in. – Lance Roberts Aug 04 '11 at 21:39
  • 1
    Did you try Google? I just did, and I found a few links which might help you: - [Line numbers on a continuous form](http://www.utteraccess.com/forum/Line-numbers-continuous-t1726357.html) - [Row numbers and alternate colors to a Continuous Form](http://bytes.com/topic/net/insights/594455-row-numbers-alternate-colors-continuous-form) – Christian Specht Aug 04 '11 at 22:18
  • 1
    If your really need such a useless number, why not teaching your users to watch the bottom of the form, where that number is already displayed for the current record ? – iDevlop Aug 05 '11 at 06:20
  • Why do you think you need this? I can't think of any but a handful of circumstances where you'd want a line number in any case (like invoice line items, where the invoice may be long enough that the users benefit from having the line item number exposed), but dynamic line numbers suggests to me that you have user interface design problems. What good whould this do the users? It might be that you don't want dynamic numbers at all, but should be storing a number in the actual record, but overall, it's a pretty silly question. – David-W-Fenton Aug 05 '11 at 21:32
  • I have simply a continuous form which displays a record set containing two columns: SKU and count. The records are displayed based on a query that omits some records and sorts them by count. I'm pretty sure a primary key wont help here, as it will be out of order. So I just want a numbering on the select record set. – Shubham Aug 05 '11 at 21:39
  • @iDevlop, the goal of this form is to be user friendly and pleasing to the eye, i know youre going to say this is the wrong software for that but this is what I have to work. As for the bar at the bottom that shows record counts, that is what I'm trying to replace with the numbering – Shubham Aug 05 '11 at 21:42
  • I still don't see what that number is going to be used for. Does the user need to know that row 4 has certain data? Are they going to do something with a particular row and need an ordinal number in order to know what to do with it? If not, then I just don't see any utility at all added by the row number. – David-W-Fenton Aug 07 '11 at 20:36

1 Answers1

2

If your table includes a numeric primary key, you can use DCount() in your query to generate a sequence number. In this example, id is a number and the primary key.

SELECT DCount("*","MyTable","id<=" & [m].[id]) AS sequence_num, m.id, m.some_text
FROM MyTable AS m
ORDER BY DCount("*","MyTable","id<=" & [m].[id]);

You can change the ORDER BY to refer to the field expression's ordinal value in the field list, rather than the full field expression, if you prefer.

ORDER BY 1;

Either way, the query should produce an editable record source for your form, though obviously you won't be able to edit the sequence_num directly ... so set the properties of the control you bind to it as Enable=No and/or Locked=Yes.

You could use a subquery instead of the DCount expression for sequence_num, but then you will get a read-only recordset.

This approach does not require a numeric primary key, but you do need a unique field or combination of fields which is unique. You can also do it with text or date fields.

Edit: If you delete rows in the form, do Me.Requery in the form's After Update Confirm event. If you add a row which you want displayed before the end of the recordset, do Me.Requery then, too. Same if you edit values in the unique field(s) for existing rows.

HansUp
  • 95,961
  • 11
  • 77
  • 135