Problems with DCount

[Denburt]

Did you check the references and of course it should always be a first did you try to reboot? It is strange.

[AccessIdiot]

Yes everything checks out. I opened the db you sent right from the email and I still get the error. :*-(

[MMcCarthy]

Hi Melissa,

RecordCount can sometimes be a problem. Try the following ...

[code=vb]
Private Sub Form_Current()
Dim rs As Object
If Me.Parent.NewRe cord = True Then Exit Sub
If Me.Dirty Then
DoCmd.RunComman d acCmdSelectReco rd
DoCmd.RunComman d acCmdSaveRecord
End If
Set rs = Me.RecordsetClo ne
rs.MoveLast
rs.MoveFirst
If rs.RecordCount > Me.Parent.Speci menCount Then
Me.Parent.Speci menCount = rs.RecordCount
End If
rs.Close
Set rs = Nothing
End Sub
[/code]

Mary

[AccessIdiot]

Hi Mary,

Thanks for jumping in, but I'm still getting the same error message. :(

[MMcCarthy]

Hi Melissa,

RecordCount can sometimes be a problem. Try the following ...

[code=vb]
Private Sub Form_Current()
Dim rs As Object
If Me.Parent.NewRe cord = True Then Exit Sub
If Me.Dirty Then
DoCmd.RunComman d acCmdSelectReco rd
DoCmd.RunComman d acCmdSaveRecord
End If
Set rs = Me.RecordsetClo ne
rs.MoveLast
rs.MoveFirst
If rs.RecordCount > Me.Parent.Speci menCount Then
Me.Parent.Speci menCount = rs.RecordCount
End If
rs.Close
Set rs = Nothing
End Sub
[/code]

Mary

Melissa

Did you put this in the current event of the subform?

Mary

[AccessIdiot]

Yep, sure did! Could the problem be the path through other forms to get to this spot?

[MMcCarthy]

Try this so we can see where the problem is.

[code=vb]
Private Sub Form_Current()
Dim rs As Object

Set rs = Me.RecordsetClo ne
rs.MoveLast
rs.MoveFirst

Msgbox "Subform Record Count is " & rs.RecordCount & _
" and SpecimanCount is " & Me.Parent.Speci menCount

rs.Close
Set rs = Nothing
End Sub
[/code]

Mary

[AccessIdiot]

Okay let's see. When I open frm_Entrainment and hit the "View or Add Specimens" button, which opens the form that contains the subform with the code in question, then I get "No current record" and when I debug it highlights "rs.MoveLas t".

[MMcCarthy]

OK, now try putting this in the current event of the main form and see what happens (don't forget to substiture your subformName)

[code=vb]
Private Sub Form_Current()
Dim rs As Object

Set rs = Me.SubformName. Form.RecordsetC lone
rs.MoveLast
rs.MoveFirst

Msgbox "Subform Record Count is " & rs.RecordCount & _
" and SpecimanCount is " & Me.SpecimenCoun t

rs.Close
Set rs = Nothing
End Sub
[/code]

Mary

[AccessIdiot]

Do you mean the form that contains the subform?

Basically the user opens frm_Entrainment and clicks the button to take them to frm_Specimen_En trainment. On this form is the subform "sbfrm_FishSpec imen_Entrainmen t". It is this subform that I have the above Form_Current code on.

[MMcCarthy]

Do you mean the form that contains the subform?

Basically the user opens frm_Entrainment and clicks the button to take them to frm_Specimen_En trainment. On this form is the subform "sbfrm_FishSpec imen_Entrainmen t". It is this subform that I have the above Form_Current code on.

Yes I mean the form that has the subform

[Denburt]

I am so sorry for such a slow reply been busy...

Okay let's see. When I open frm_Entrainment and hit the "View or Add Specimens" button, which opens the form that contains the subform with the code in question, then I get "No current record" and when I debug it highlights "rs.MoveLas t".

Generally speaking for most recordsets that provide a recordcount you can generally request a recordcount as such. If it is zero then there are no records and a move first or last will cause an error as will most recordset calls.
[CODE=vb]if rs.RecordCount > 0 then
do this
end if[/CODE]

Or another more popular method since it will be recognized by most recordsets

[CODE=vb]If Not rs.EOF And Not rs.BOF Then
do this
end if[/CODE]


Otherwise you will receive the error "No current record"

[Denburt]

Another reason the EOF or BOF method is used is that there are times when a recordset needs to be populated before you can retrieve a recordset which is why Mary sugested the Movefirst moveLast methods, which is also a good practice for coding to prevent issues that may arise as such.

[AccessIdiot]

Right. Now it's hanging on rs.MoveLast with the error message "No current record". The lines of comprehension have started to blur and I am at a loss what to do here?

Denburt should I use something like this

If Not rs.EOF And Not rs.BOF Then

around the rs.MoveLast rs.MoveFirst?

[Denburt]

Private Sub Form_Current()
      Dim rs As DAO.recordset
      Set rs = Me.SubformName.Form.RecordsetClone
  
[B]If Not rs.EOF And Not rs.BOF Then[/B]

    rs.MoveLast
      rs.MoveFirst
      Msgbox "Subform Record Count is " & rs.RecordCount & _
      " and SpecimanCount is " & Me.SpecimenCount

[B]End if[/B]

      rs.Close
      Set rs = Nothing
        End Sub