total number of records in subform shown on Main form

[barra47]

I have a Main form(Table1) with a subform (Table2)
Table1 has a field named Counter(number)
The subform has a hyperlink field (Link) that store's the path to files on the server
if i have 3 hyperlink record in the subform
I want to have the total of 3 showing in the Mainform's Counter field

Can you please show me code that will acheive this
and would this code be best placed on the afterupdate of the subform

Thanks in Advance
Much appreciated.

 

[TheAceMan1]

How are ya barra47 . . .

Have a look at DCount ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

barra47 . . .

In reference to your Counter field in Table1, be aware that calculations are typically not stored in a table. Rather since they should always work out, calculations are displayed. There are rare instances where calculations are stored ... such as in a banking statement having a running balance. Either way I'll give you both methods:
[ol][li]To display the recordcount ... in the Control Source of a textbox on Table1 copy/paste the following (you substitute proper names in red):

=DCount("[[COLOR=#CC0000][b]LinkFieldName[/b][/color]]","[COLOR=#CC0000][b]Table2Name[/b][/color]","[[COLOR=#CC0000][b]LinkFieldName[/b][/color]] Is Not Null")

Note: I used DCount with criteria so the count only includes those records which have values in your Link field.[/li]
[li]To store the recordcount ... we wrap our DCount in a subroutine and make our first call from Table1 On Load event. Copy/paste the following function to the code module of Table1:

Private Sub GetSubCnt()
   Me.Counter = DCount("[CoID]", "tblEmployee", "[empLN] is not null")
End Sub

And the code for Table1 On Load event:

   Call GetSubCnt

After the form opens you call GetSubCnt as you like.[/li][/ol]
Your Thoughts? . . .

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]