Me.AllowAdditions in a subform based on count in that very subform

[uscctechnician]

I have a form, which has a sub form on it that allows me to enter in records for a table. What I would like to do, is max the amount of entries in this sub form to 10. I have a text box with the data source "=Count(CTN)" and it is accurate.

I added this into the sub form's On Current event:

If Me.Count > 9 Then Me.AllowAdditions = False Else Me.AllowAdditions = True

Thne problem I am having is that when I open my form and go to the tab with the sub form, the code will actually stop at 9. SO, for example, I open my form and go to the page with the sub form. It currently has 9 records on it. If I do NOT have the code in my form, the form will show me my 9 records and a blank line ready to accept the 10th record. When I put the code into the form, when I access the page with the sub form, it shows me my 9 records, no blank line to start a tenth record and no way to input the 10th record.

The Count(CTN) box shows 9 so I must be missing something.

Thank you all in advance!

 

[PHV]

What happens if you replace this:
If Me.Count > 9 Then Me.AllowAdditions = False Else Me.AllowAdditions = True
with this ?
Me.AllowAdditions = (Me.Count <= 10)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[uscctechnician]

PHV,

I saw that in one of the other postings on here when I did a search and tried it as well, and It did the same exact thing. When I accessed the page with the sub form, it wouldnt let me add a record. I even went into my table, deleted 3 records to make my record count 6, and it still wouldnt allow me to add a record.

My count is based off of count(CTN). maybe I should use Count(*) instead?

 

[uscctechnician]

The other thing I noticed is this.

I put in the code above and when I accessed the subform, the sub form will not allow me to add a record. However, the Allow Addition field under the sub form properties still shows Yes.

I now wonder if this whole thing is caused by it counting everything in the table this sub form adds data to and not just counting the records in the table that matches the main form's RecordID, which is how I tell the sub form to filter the data it is showing. But then again, the Me.Count text box which is set to =Count(CTN) works fine and shows the correct # of records for that RecordID.

Im stumped on this one guys.

 

[missinglinq]

"I now wonder if this whole thing is caused by it counting everything in the table this sub form adds data to and not just counting the records in the table that matches the main form's RecordID"

That's what Count() does; there is no provision for a WHERE clause. You need to use something like DCount(), which does have a WHERE clause, and use the RecordID in it:

DCount("*", "SubTableName", "[RecordID]= '" & Me.RecordID & "'")

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[MajP]

Me.AllowAdditions = (Me.recordsetclone.RecordCount <= 10)