Rowcount on a subform? (1 Viewer)

[jegenes]

Is there way to find the row count (number of entries) on a subform? Mine has 4 columns per line, which get put into its own table. I need it to figure the number of entries I've made so I can have it automatically enter that number into a textbox on the main form.

I've tried to dim an integer, then do a For/Next loop. Something like:

For index = 0 To Subform.Count
index = index +1
Next index

I do get a count, but it's the wrong number. Think of a shopping list, where you select entries, and each entry is on its own line. I want to count up the number of entries on the subform. Thanks a lot.

John

 

[jwindon]

Put this in the control source for that text field on your main form.

=DCount("*","tblSubFormTableSourceName","[MainFormFieldLinkName] = '" & [MainFormFieldLinkName] & "'")

 

[Pat Hartman]

Add a footer to the subform. In the footer, add a control with the following controlsource.

=Count(*)

 

[jegenes]

Pat,
I added a textbox with the control source as you suggested. ( =Count(*) )

As I add records to the subform, it stays at zero. But, if I select one of the records and delete it, the box updates to the correct number of records. So, I assume it needs to do some kind of requery in order to update as I add each record.

Also, if I save the entire record (form), it does not update, but stays at zero. The only way I can find to make it update is to delete one of the records I've added in the subform.

Thanks so much for everyone's help.

John

 

[Pat Hartman]

Try requerying it in the AfterUpdate event of the subform. I think that event runs for add and delete also. If not, you'll need the requery in three places.