Full Version: Row count of a subform datasheet linked by Master/Child field
UtterAccess Forums > Microsoft® Access > Access Forms
I have a table tblClients (PK = ClientID) and a table tblClientVisits that tracks what visits (if any) have been made to each client. So in tblClientVisits there is a ClientVisitID (PK) and ClientID (FK) for each row.
I have a form frmClientDetail that includes the subform sfrmClientVisitList (as a datasheet).
The Record Source of sfrmClientVisitList is essentially SELECT * FROM tblClientVisits, but under the Data Properties of sfrmClientVisitList it is linked to frmClientDetail with:
Link Child Fields = ClientID
Link Master Fields = ClientID
So even though the Record Source of sfrmClientVisitList is all of tblClientVisits, only those that match the ClientID of frmClientDetail are listed in the subform.
This works great except I cannot figure out an easy way to determine the number of rows displayed in the subform. I would like to disable certain controls if the number of rows is 0.
I have tried sfrmClientVisitList.Form.RecordsetClone.RecordCount but this seems to always return the total number of rows in tblClientVisits (not just the ones for the selected ClientID). That is, is appears to work with the original Record Source and I need to find the count after the recordset has been winnnowed down by the Master/Child link field.
Is the best way to abandon the Master/Child link convenience and instead set the subform's Record Source programmatically (and include the "WHERE ClientID = ..." clause myself)? I have a number of similar forms/subforms already set up that use the Master/Child link to filter results so am hoping for a solution that doesn't require me to change this. Thanks for any help.
How about
Dcount("ClientID","tblClientVisits","ClientID=" & forms!frmClientDetail!clientID)
I tested the reference you used (SuibformControl.Form.RecordsetClone.RecordCount) and it worked fine.
Is the Main Form bound to the Table tblClients?
Compact & Repair?
Patch your OS and your Access 2002 software?
Van -- I did a compact on the database and the RecordsetClone.RecordCount expression now seems to work. I also realized I did not interpret the original problem correctly (I was seeing a count of ClientVisits for the first Client, not a count of all ClientVisits as I thought) and changed something related to this, so I'm not sure if it was the DB or cockpit error. In any event, thanks for testing and the suggestion.
anny -- Using DCount as you suggest works fine, too. I did not think of this.
Thanks to you both!
you're welcome
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.