Problems with DCount

[AccessIdiot]

I'm confused. Does this go on the subform or the form? I'm confused by the reference to Me.SubformName. Form.RecordsetC lone

If this goes on the main form then do I still put something on the subform?

Sorry for the confusion.

[AccessIdiot]

Okay at this point it will probably help if I explain exactly what I have where. :)

On frm_Specimen_En trainment

Private Sub Form_Current()
Dim rs As DAO.Recordset
Set rs = Me.sbfrm_FishSpecimen_Entrainment.Form.RecordsetClone
If Not rs.EOF And Not rs.BOF Then
rs.MoveLast
rs.MoveFirst
MsgBox "Subform Record Count is " & rs.RecordCount & _
" and SpecimenCount is " & Me.SpecimenCount
End If
rs.Close
Set rs = Nothing
End Sub

and on sbfrm_FishSpeci men_Entrainment (a subform of the above)

      Private Sub Form_Current()
      Dim rs As Object
       
          Set rs = Me.RecordsetClone
          rs.MoveLast
          rs.MoveFirst
       
          MsgBox "Subform Record Count is " & rs.RecordCount & _
      " and SpecimanCount is " & Me.Parent.SpecimenCount
          rs.Close
          Set rs = Nothing
      End Sub

Now when I click the button on frm_Entrainment to go to frm_Specimen_En trainment I get:

"No current record" and when I debug it highlights

rs.MoveLast

on the subform (sbfrm_FishSpec imen_Entrainmen t).

Hopefully this makes someone out there go "Ah! Of course!" and there is some kind of simple solution (like toss the form out the window). :D

[Denburt]

I think I found the issue at hand when I opened the Entrainment Specimen form and the number of specimens were less than the number of records in the subform it would try to update the number of specimens before the form was fully loaded. Hopefully this will resolve it for you.

Remove the on Current event from the Entrainment Specimen form and in the subforms VBA this is what I used.

[CODE=vb]Option Compare Database
Option Explicit
Dim IntCurCnt As Integer
Private Sub Form_Current()
Dim rs As Object
IntCurCnt = IntCurCnt + 1
If Me.Parent.NewRe cord = True Or IntCurCnt = 1 Then Exit Sub
If Me.Dirty Then
DoCmd.RunComman d acCmdSelectReco rd
DoCmd.RunComman d acCmdSaveRecord
End If
Set rs = Me.RecordsetClo ne
If Not rs.EOF = True And Not rs.BOF = True Then
rs.MoveFirst
rs.MoveLast
If rs.RecordCount > Me.Parent.Speci menCount Then
Me.Parent.Speci menCount = rs.RecordCount
End If
End If
rs.Close
Set rs = Nothing
End Sub
[/CODE]

[AccessIdiot]

Okay it is no longer throwing an error when the form is opened. However, I'm not sure its working.

When I went in the first time and added records to the subform the SpecimenCount textbox didn't update right away. I had to go to a new record and then come back and when I did I got "no current record" and it highlighted 'rs.MoveLast' on the subform.

The second time I went in, added three records but left my cursor in the third record. The SpecimenCount only recorded two, even when I went to a new record on the form and then came back. I had to go to a new record in the subform for it to update.

The third time I couldn't get the SpecimenCount to update until I had closed the form, gone back to frm_Entrainment , and reopened the form with the button "View or Add Specimens".

So it seems to be working but there is a lot of refreshing going on. Is there a simple way with code that I can refresh everything? Shall I do a requery?

Thanks for your help, its a relief not to constantly be getting an error message!

[Denburt]

It is throwing some interesting results I believe it has to do with picking up the recordset information during the on current event. I cut a lot of the code out and it seems to work great on this end. If the following doesn't give you the proper recordcount then my only other suggestion would be to create a separate query for this and use the criteria to reference the specimen forms ID number. So instead of opening a clone you are opening another separate query.

   Option Compare Database
      Option Explicit
      Dim IntCurCnt As Integer
      Private Sub Form_Current()
      Dim rs As Object
      IntCurCnt = IntCurCnt + 1
      If Me.Parent.NewRecord = True Or IntCurCnt = 1 Then Exit Sub
      If Me.Dirty Then
          DoCmd.RunCommand acCmdSelectRecord
          DoCmd.RunCommand acCmdSaveRecord
      End If
      Set rs = Me.RecordsetClone
          If rs.RecordCount > Me.Parent.SpecimenCount Then
              Me.Parent.SpecimenCount = rs.RecordCount
          End If
      rs.Close
      Set rs = Nothing
      End Sub

[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.