Count number in subform

[triplee23]

Hi,

I am quite new to Access and have a problem I hope someone can help me with.

I have a main form called frm_SAMPLE and in this form I have added a subform called frm_JM_subform. The subform is a continuous form linked through a relationship sample_id <> sample_id. It is a one-to-many relationship, eg. the subform frm_JM_subform contains many records for every record in frm_SAMPLE. The subform frm_JM_subform is based on table tbl_JM and the main form based on a table called tbl_SAMPLES.

I wish to count the number of records in the subform and through a calculation add an incremental index number to every record in the subform, something like the example below:

Nr. Field 1: Field 2:
1 Value 1 Value 2
2 Value 1 Value 2
etc.

The incremental number in the first row is of course not the key as the relationship will decide which records show depending on which record is active in the main form. It is this number I am trying to calculate. Is is this incremental number I wish to show in the subform.

As an example I have watched a youtube instruction video almost solving my problem.

My calculation looks like this:

=DCount("*";"tbl_JM";"JM_id <=" & [JM_id])

The calculation works but the values it returns are based on the whole table (tbl_JM) and not the relevant records in the subform given by the relationship through the main form. How do I get this calculation to only calculate on the records in the subform? I clearly have to learn syntax for calculations. I don't know how to specifically point to the subform.

I know my expression "tbl_JM" is the problem, I just don't know how to (syntax) point to the specific subform I am trying to count the records in.

The value itself is represented in a text box within the subform and is visible for every record in the subform.

Thanks for all possible help, even a good link to a tutorial on expression builder syntax would be nice.

 

[highandwild]

Hi

I had to investigate this one but this seems to work.

Add this function to the code on the form

Public Function ShowRecordNumber()

Me.RecordsetClone.Bookmark = Me.Bookmark

ShowRecordNumber = Me.RecordsetClone.AbsolutePosition + 1

End Function

and call it fom a text box on the subform like this.

=ShowRecordNumber()

HighAndWild

 

[triplee23]

Thanks, the function work perfectly. Could you explain how it works? As I mentioned, I am an Access beginner and would like to understand.

What does the Bookmark do and what does the RecordsetClone represent?

Triplee23

 

[highandwild]

There are a number of things here that you need to know about.

RecordsetClone
The RecordsetClone property setting is a copy of the underlying query or table specified by the form's RecordSource property. If a form is based on a query, for example, referring to the RecordsetClone property is the equivalent of cloning a Recordset object by using the same query. If you then apply a filter to the form, the Recordset object reflects the filtering.

Bookmark
You can use the Bookmark property with forms to set a bookmark that uniquely identifies a particular record in the form's underlying table, query, or SQL.

Absoluteposition
Returns a long integer from 0 to one less than the number of records in the Recordset object. It corresponds to the ordinal position of the current record in the Recordset object specified by the object.

Most of the above is taken from the help. Just highlight something in your code and press F1.

So….

Me.RecordsetClone.Bookmark = Me.Bookmark : A virtual copy is created of the set of records upon which the form / subform is based. The bookmark in this copy is set to be the bookmark of the current record, the one which called the function .

Me.RecordsetClone.AbsolutePosition + 1: The record number of this virtual record is ascertained and returned to the object that called the function. Note that because the records numbers start from 0 you have to add one.

I hope that this helps.

HighAndWild