Count of records 1

[jges]

I have a main form (based on a query) with a subform that shows information for a one to many relationship. On the main form I would like to add the number (count) of related records.

I can change the query to show totals (this involves adding a table and using 'count' on the field of interest) but that makes it so that I cannot add new records. I have tried to make a second query and add a text box that points to this query; this gives me a 'Name?' error in the text box. I have also tried various SQL statments in the control source but everything I have tried gives the 'Name?' error (which is understandable since I know very little SQL).

I'm using Access 2000.

 

[DajTwo]

Hi

I am not an expert, far from it, but what I do in a form is create a new field, and as control source = Count ([____]) Count («expr»)). Insert the name of a field that is unique to each record.



If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[ZmrAbdulla]

On current of the mainform can display it

Private Sub Form_Current()
    Me.YourTextBoxName = Me.YourSubFormName.Form.RecordsetClone.RecordCount
End Sub

Zameer Abdulla

 

[jges]

Thank you both for responding.

I got Zameer's method to work but I am still interested in the other proposal. I don't think I'm getting the Count expression correct. Assume I have tblSuppliers -> SupplierID (the one side of the relationship) and tblParts -> PartNo (the many side). I've added an unbound text box to the main form, now what would my expression look like?