Problems with DCount

[AccessIdiot]

I have a form (frm_Specimen_Entrainment) that contains a subform (sbfrm_FishSpecimen_Entrainment). The subform has to be in datasheet view.

On the form I have an unbound textbox where the user can enter the number of specimens. The subform only appears if the specimen is a fish. The subform allows the users to record data about fish only.

The user would like me to add in functionality whereby the computer counts the number of records put into the subform and displays that in the textbox on the form. This would be a nice feature to avoid error actually since if they entered 12 records into the subform but counted 11 they would be making a mistake.

I tried using this code as the default of the textbox:

nz(DCount("Fish_ID","tbl_Specimen_Fish_Entrainment","Specimen_ID = " & [Specimen_ID]),0)

but no go.

I also tried using the code on the afterupdate event of the subform:

Dim CountFish As Integer
Private Sub Form_AfterUpdate()
CountFish = Nz(DCount("Fish_ID", "tbl_Specimen_Fish_Entrainment", "Specimen_ID = " & [Specimen_ID]), 0)
End Sub

again no go. Any ideas?

Oh and I am using Fish_ID in the code but it is not one of the column in my datasheet view of the subform. Does that matter?

One last thing - even though I set a default I still need the count textbox to be editable. In other words, it may count 12 records on the subform but the user may actually have 47 fish total and just not enough time to record all the data on the subform, if that makes sense.

Thanks!

[AccessIdiot]

This seems to be working! Of course, I haven't handed it over to my db users yet. I'm sure they'll find a way to break it. :-)

Thanks for seeing this through Denburt. I'm not sure I understand how it all works but I am glad it is working!

[Denburt]

Glad it is working WHEW (wipes the sweat off the brow). :) Glad I could help, when they break it just let us know.