Create row numbers on a subform in Access 2010

[rd3Po]

I have an Access DB that I'm attempting, for display purposes, to add an unbound text field to a continuous subform that contains a row number for each record. From a really old previous post I can make it work when I open the subform by itself, but it does not work when I open the main form with the subform attached.

The public module code is written:

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([Forms]![myForm])
   
      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

The text field, in the subform, is titled "RowNum" and I have the following as the control source:
=RowNum([Forms]![frmMainForm]![Child3].Form)

I've tried so many different ways and either get #Name or #Error.

Please help!

[mbizup]

Have you tried just passing the path to the subform when calling the function? I don't think you need the ".Form" in control sources or queries.

=RowNum(Forms]![frmMainForm]![Child3])

[zmbd]

Referring to subforms is a real treat sometimes.

Often the errors come across to me, especially at midnight, as:
#Name = I can't find the object you're talking about, are you sure you know what you're doing - go back to bed!
#Error = What are you thinking... I can't read your mind, fix this after you've had a serious nap.
#Type = Really, you're not seriously trying to pass/return the wrong data-type, go count some sheeps, ducks, squirrels!

I have a form and subform where in I use IIF() and UDF to determine if the subform is being opened as standalone or as part of a navigation control form object.... that was a real treat, not.

Anyway,

=RowNum([Forms]![ParentFormName]![SubForm_contain er_Name].[Form])

Say I had:
+ Parent form named [Form1]
+ On [Form1] that form I had the subform container named:
[frmQuery1_conta iner]
+ that held the subform named [frmQuery1]

then the reference for your function would be:
=RowNum([Forms]![Form1]![frmQuery1_conta iner].[Form])
>>> NOTE THE BANGS (!) and DOTS (.) PLACEMENT!!
Often the subform container is named after the subform. I rename these due to the confusion such naming creates by appending "container" to the end of the default name.

[rd3Po]

Thank you mbizup. It didn't work either.

[rd3Po]

Hey zmbd. I'm an experienced Access user but inexperienced Access VBA user. I piece what I can into modules from what I find via Google searches and make them work for my databases. I give you that to say i'm not following your thread. Is your [frmQuery1_conta iner] a sub-subform? In my instance [child3] is the given name of the subform I'm trying to get the row numbers to show up on.

[zmbd]

Ok,
A few images
Parent form:
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 61[/IMGnothumb]

Subform Container:
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 62[/IMGnothumb]

actual subform --- NOTE the barely visible orange square outline just under the circle and around the actual child form:
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 63[/IMGnothumb]

So in this particular arrangement with a parent named [Form3] the reference to the child form named [frmTblPuke2] would be:
[Forms]![Form3]![frmTblPuke2_Con tainer].[Form]

Now the default name for the subform-container when you create it is usually the same name as the form contained therein; however, this tends to cloud what the actual reference is when I am writing references to the child form so I tend to append the "_container " text to the SFC default name.

With a #Name error then I suspect that the name of the object used in the equation =RowNum() in the control's [Control Source] property is either misspelled or not the correct name of the object containing your child form.

With a #Error then There are no records or some other issue with one or more of the other bound controls or one or more of the calculated controls bound to the same underlying record-set is erring.

[zmbd]

... another question comes to mind with the #Name error...
Is the function RowNum() in a standard module or in the form's module?

If in the form's, then move to a standard module.

[rd3Po]

I changed the subform name to child1_containe r, tried again, and... no luck.

Main Form
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 64[/IMGnothumb]

Subform
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 65[/IMGnothumb]

Line No Field
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 66[/IMGnothumb]

All appear to adhere to what has been suggested. Could it be that the module is incorrect? Should RowNum(frm As Form) be written differently?

Thanks!

[rd3Po]

It's in a standard module zmbd
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 67[/IMGnothumb]

[zmbd]

+ Sorry your images in post#8 do not show enough detail... You need to "zoom-in" on the property pane. :)

+ I used the function you published against the form I took screenshots in Post#6 using the reference in Post#6 as given for a control in the subform therein without errors; thus, I think your function is correct; however your reference must be off somehow...

[rd3Po]

Hopefully these show up better :) oh, and, how do you get the pics to show up on the thread (so you don't have to edit the thread again haha)

could the reference be wrong in the module?

[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 68[/IMGnothumb]



[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 70[/IMGnothumb]


[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 69[/IMGnothumb]

[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 71[/IMGnothumb]

[zmbd]

[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 72[/IMGnothumb]
Control Name and Function Name are the same.
I would start with changing the control name to something else - I would suspect that is the source of the #Name errors

[NeoPa]

rD3Po:
oh, and, how do you get the pics to show up on the thread (so you don't have to edit the thread again haha)

Follow these steps in order :

1. Formulate your post originally with all the attachments uploaded then post it.
2. Open a text editor to formulate the extra commands used to show your pictures.
3. For each attachment of the post :
   1. Copy its link (Right-click on the link and select Copy Link Location (or similar)).
   2. Paste the link into the text editor after any previous ones.
   3. Add before the new link - [IMGNOTHUMB]
   4. Add after the new link - [/IMGNOTHUMB]
4. When all attachments have been handled copy the whole lot to your clipboard.
5. Go back to the post you just posted and select edit.
6. Go to the end of your original entry and then paste in your earlier work.
7. Resubmit the post.

[rd3Po]

Wow, could it have been that easy?! Also lesson learned for future projects.

Thanks for your help zmbd!!!!!

[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 73[/IMGnothumb]

[zmbd]

Variable scope can cause interesting things to happen :-)

:)
-z