how to put line numbers on subform?

[lorirobn]

Hi,

I have a subform (continuous) where records from a table are displayed, row by row (recordsource is a query). I would like to number these lines, displaying the number on the subform. I don't need to keep this number on the table, but numbering the lines would make it easier to distinguish.

I copied 'GetLineNumber' code from a thread, but it is not working.

Thanks in advance,
Lori

 

[Remou]

Have you looked at Running Sum? I think it will suit. You can create a textbox with a Control Source of 1 and set the Running Sum property to Over All.

 

[lorirobn]

Thanks, Remou.
According to VB Help, Running Sum can only be used on text boxes on reports. Mine is a form. Oh well. I will have to remember that for reports.
Any other suggestions?

 

[Melagan]

the Running Sum property is availabe in forms? Where? I thought it was available only in reports. In reports, the property is found under the Data tab; nowhere to be found when the control is on a form though.

~Melagan
______
"It's never too late to become what you might have been.

 

[lorirobn]

oops, spoke too soon... it seems to have taken it. But where's the correct place to put the RunningSum code in my VBA (it's not on the property list for the text box)?
I set the control source to: "=1", and in my Form_Open, put the code: Me.txtLineNumber.RunningSum = 2.
The field is displaying as '1' on every line.
I will play around with it...

 

[Remou]

I am sorry, I though I was in the reports forum.

 

[MajP]

This is how I did it. My primary key on the form is a field called "autoPersonnelID"

Public Function lineNumber(intPK As Long) As Long
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
  rs.FindFirst ("autoPersonnelID = " & intPK)
  lineNumber = rs.AbsolutePosition + 1
End Function

from the textbox I call this function

=lineNumber([autoPersonnelID])

There is probably a simpler way.

 

[lorirobn]

Thanks, MajP - works great, and very simple!
But - what do I do on the empty "add new" line. I am getting an error on that line. How do I test for 'addnew' line?

Thanks!
Lori

 

[MajP]

Public Function lineNumber(intPK As Variant) As Variant
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If intPK = "" Then
lineNumber = ""
Else
rs.FindFirst ("autoPersonnelID = " & intPK)
lineNumber = rs.AbsolutePosition + 1
End If
End Function

=lineNumber(nz([autoPersonnelID],""))

The error comes from trying to pass a null value to the function. So I use a NZ function to convert the null to "". Then I print out "". Notice the paramater and function are switched to variant which allows you to pass in and out an empty space.

 

[TheAceMan1]

Howe are ya lorirobn . . .
Just another version from my library. I'm a little late but couldn't resist . . .

[blue]Public Function LinNum() As Long
   Dim rst As DAO.Recordset, ctl As Control
   
   Set ctl = Me![purple][b]YourPrimaryKeyName[/b][/purple]
   
   If Trim(ctl & "") <> "" Then
      Set rst = Me.RecordsetClone
      
      If Not rst.BOF Then
         rst.FindFirst "[![purple][b]YourPrimaryKeyName[/b][/purple]] = " & ctl
         LinNum = rst.AbsolutePosition + 1
      End If
      
      Set rst = Nothing
      Set ctl = Nothing
   End If
      
End Function[/blue]

See Ya! . . . . . .

 

[lorirobn]

MajP: thanks for the modification, and also the explanation about variant. I am getting an error on the last line, "=linenumber" (Error msg = "expected line number or statement or end of statement").
My code is as follows:

Public Function CalcLineNumber(intPK As Long) As Long
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
  
If intPK = "" Then
    CalcLineNumber = ""
Else
    rs.FindFirst ("ActivitySeqNo = " & intPK)
    CalcLineNumber = rs.AbsolutePosition + 1
End If
 
 =CalcLineNumber(Nz([activityseqno], ""))
 
End Function

AceMan: thanks for your suggestion, too. When I get this working, I will check out yours. Always helpful to learn some more...

Thanks all.

 

[MajP]

Sorry
=lineNumber(nz([autoPersonnelID],""))

is how you call it from the unbound text box. Not part of the code. AceMan's approach is a little simpler in that you do not have to pass any parameters.

 

[lorirobn]

MajP - I still get an error on the new line. Does processing for this line even go to the code in the function? I am not sure how to fix the "#Error".

AceMan: I tried your code, too, and am getting an error (#Error) on every line. I think it is happening around the line:
Set ctl = Me!YourPrimaryKeyName

because I set a breakpoint there but it never gets past that line.

MajP, my code is as follows:

Public Function CalcLineNumber(intPK As Long) As Long
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
  
If intPK = 0 Or IsNull(intPK) Then
    CalcLineNumber = 0
Else
    rs.FindFirst ("ActivitySeqNo = " & intPK)
    CalcLineNumber = rs.AbsolutePosition + 1
End If

End Sub

Note: I replaced "" with 0 in the IF stmt because I got errors with "". Actually, it never even goes to the first part of the if statement, which is why I ask if processing goes here for a new line.

I will keep trying with both your ways, but would appreciate any suggestions!

Thanks,
Lori

 

[MajP]

Since your parameter is a long (i.e. intPK as Long) you can not pass in a null value. This function would throw a type mismatch error, thus #error.

So this is meaningless
If intPK = 0 Or IsNull(intPK) Then

because if it is null, then you will throw an error before ever getting there.

I assume you call the function like this.
=CalcLineNumber(nz([ActivitySeqNo])

When it gets to the new record, the activity seq no is null. The NZ function converts the nuul to zero. It does not bypass the function but sends it a 0 instead of the null. The is just an example of a nested function. A function inside of a function.

On AceMan's code are you by chance literally using
"Set ctl = Me!YourPrimaryKeyName"

That is just an example.

 

[lorirobn]

OHHHH, I get it. Thanks for the explanation, MajP.

I call the function with this:
=CalcLineNumber(Nz([activityseqno]))

I have txtLineNumber on my form with format General Number.

Now I understand that a null would cause an error before even getting there.

But: at what exact point does the NZ function get performed? Does it go to the CalcLineNumber function, THEN do the NZ?
OR - does it perform the NZ and THEN start the CalcLineNumber function?

Since it's a function within a function, I guess I need to know which is the top-most function that would get performed first, right?

With AceMan's tip, I didn't literally use 'YourPrimaryKeyName' <gr> - I almost mentioned that above, but I thought 'nah, that's too obvious'. Guess it wouldn't have hurt. I put my primary key name in.

Is there a way to simply keep a COUNTER of line numbers, without even doing a FindFirst?

Thanks once again - thx for sticking this out with me,
Lori

 

[lorirobn]

MajP: Actually, I just got rid of the error on the new line number, with your explanation above. I had the NZ function set up wrong, with
=CalcLineNumber(Nz([activityseqno],""))
I looked up NZ function, and realized I didn't need the "".
Now no error. (I meant to tell you above, but thought I was still getting errors).

Now new line number displays a zero, which looks funny. What is the best way to not display a zero? Should I use 'visible = false', or is there a way to set the format?

Thanks so much for all your help (and AceMan, too - sorry I couldn't get yours to work).
I'd still be curious if there is an easy way to just keep a line counter.

Lori