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!

[JConsulting]

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!

How about in your subform's on_current event

Private Sub Form_Current()
Dim rs As Object
Set rs = Me.RecordsetClone
Me.Parent.MyCountBox = rs.RecordCount  '<--change MyCountBox to your textbox
End Sub
 

J

[AccessIdiot]

Finally getting back to working on this after a diversion of other projects . . .

I get an error message with the code you suggested (oh and it's RecordCount not RecordsetCount by the way), probably because the subform isn't used everytime. The error I am getting is "You can't assign a value to this object" and it is highlighting Me.Parent.SpecimenCount = rs.RecordCount

thanks for any help

[Denburt]

      Private Sub Form_Current()
      Dim rs As Object
      Set rs = Me.RecordsetClone
if not rs.eof then
      Me.Parent.MyCountBox = rs.RecordCount  '<--change MyCountBox to your textbox
else
Me.Parent.MyCountBox = 0
end if
      End Sub

This should take care of the error.

[JConsulting]

Finally getting back to working on this after a diversion of other projects . . .

I get an error message with the code you suggested (oh and it's RecordCount not RecordsetCount by the way), probably because the subform isn't used everytime. The error I am getting is "You can't assign a value to this object" and it is highlighting Me.Parent.SpecimenCount = rs.RecordCount

thanks for any help

??oh and it's RecordCount not RecordsetCount by the way??
What are you referring to?

[Denburt]

Oh and BTW although probably a tiny bit slower, Dcount should have worked unless the Specimen_ID is numeric if it is then you need to wrap it in quotes. Fish_ID field doesn't need to be in your current form or datasheet since it is directly accessing the table name you provided in the domain area.

For this and an interesting tip about DCount I refer you to the help file:

You can use the DCount function to count the number of records containing a particular field that isn't in the record source on which your form or report is based. For example, you could display the number of orders in the Orders table in a calculated control on a form based on the Products table.

The DCount function doesn't count records that contain Null values in the field referenced by expr unless expr is the asterisk (*) wildcard character. If you use an asterisk, the DCount function calculates the total number of records, including those that contain Null fields. The following example calculates the number of records in an Orders table.

intX = DCount("*", "Orders")

So unless you don't want to count Null values I would use the wild card.

CountFish = Nz(DCount("*", "tbl_Specimen_Fish_Entrainment", "Specimen_ID = " & [Specimen_ID]), 0)

[AccessIdiot]

??oh and it's RecordCount not RecordsetCount by the way??
What are you referring to?

Sorry, I could have sworn the code you suggested said "RecorsetCount" but that's probably because I'm working on 5 different projects at the moment, lol.

Denburt, I would love to use DCount because I'm more familiar with it (although its lovely having options). The Fish_ID is an autonumber and the Specimen_ID is a number field. I will try using a wildcard instead.

One concern I have is updating the SpecimenCount field when new records are added to the subform. In otherwords, anytime I add a record using the subform the SpecimenCount should up one.

So where would I put the code you suggested? In the After_Update or Current or . . .?

[Denburt]

I would use the on current event since if a user is browsing records and not changing them then it will still work.

Also on the DCount issue I just realized that the field [Specimen_ID] was not declared as being on any particular form such as Me![Specimen_ID] keep this in mind since even though a method might work if it isn't proper then it could cause you grief later on. Send you chasing your own tail so to speak.

[AccessIdiot]

Hmmm, still not working.

But you know what? I'm beginning to think maybe its not such a good idea anyway. It's messed up - see, the user wants to save a step of having to count how many records he's entered into the subform. He wants to enter the info and have the computer count the number of records for him. However, sometimes he won't enter any records into the subform, but he'll still want to enter a count onto the form. That is, they caught 10 fish (recorded on the form) but didn't have time to measure them (recorded on the subform).

So that rules out putting something in the on current event. That's why I thought I'd put it as a default value at first, so when the form is opened it counts any records in the subform. But then that is hard to update if the info is entered on a new form - that is the default would be zero and then records get added and then how do you update the count?

So you see my conundrum here. :) Maybe its best just to go back to the user and say "sorry, you're just going to have to count your records."

[Denburt]

the user wants to save a step of having to count how many records he's entered into the subform. He wants to enter the info and have the computer count the number of records for him. However, sometimes he won't enter any records into the subform, but he'll still want to enter a count onto the form. That is, they caught 10 fish (recorded on the form) but didn't have time to measure them (recorded on the subform).

So that rules out putting something in the on current event. That's why I thought I'd put it as a default value at first, so when the form is opened it counts any records in the subform. But then that is hard to update if the info is entered on a new form - that is the default would be zero and then records get added and then how do you update the count?

So you see my conundrum here. :) Maybe its best just to go back to the user and say "sorry, you're just going to have to count your records."

No and no! LOL No is not an option ;)
Seriously though, if you use the current event of the subform set it up so that it is only updated when the count is higher than the number currently in the text box. If they don't enter any records or if they enter a number manually but enter more records than the number that is on the form... You can catch them coming and going preventing any sort of issue.

 
      Private Sub Form_Current()
            Dim rs As Object
            Set rs = Me.RecordsetClone
      if not rs.eof then
          if rs.RecordCount>Me.Parent.MyCountBox then
            Me.Parent.MyCountBox = rs.RecordCount  '<--change MyCountBox to your textbox
           end if
      else
      Me.Parent.MyCountBox = 0
      end if
            End Sub

[JConsulting]

Finally getting back to working on this after a diversion of other projects . . .

I get an error message with the code you suggested (oh and it's RecordCount not RecordsetCount by the way), probably because the subform isn't used everytime. The error I am getting is "You can't assign a value to this object" and it is highlighting Me.Parent.SpecimenCount = rs.RecordCount

thanks for any help

I've been lax on this one I'm afeared.

problem is one of focus. I was under the impression that you had one textbox that your users enter a number into...and another that displays the number of records in the subform.

using this method in the main form's on_current event should work.

Private Sub Form_Current()
Dim rs As Object
Set rs = Me.SubformOne.Form.RecordsetClone
Me.MySubCount = rs.RecordCount
End Sub
 

In that scenario, this works.
J

[Denburt]

I've been lax on this one I'm afeared.

problem is one of focus. I was under the impression that you had one textbox that your users enter a number into...and another that displays the number of records in the subform.

using this method in the main form's on_current event should work.

Private Sub Form_Current()
Dim rs As Object
Set rs = Me.SubformOne.Form.RecordsetClone
Me.MySubCount = rs.RecordCount
End Sub
 

In that scenario, this works.
J

LOL yes you have evidently. Let me catch you up real quick and give you a rundown.

She had an error because there was no check in place for EOF.

She feels really comfortable and likes using DCount but isn't having any success.

I added an if statement in my last post to provide for a check to determine and prevent updating if the user manually entered a number greater than what is listed in the record count.

Still with us? j/k LOL

[AccessIdiot]

I'm still here! lol, I was handed a project georeferencing about 10 images so had to take a break from the db. :(

[JConsulting]

LOL yes you have evidently. Let me catch you up real quick and give you a rundown.

She had an error because there was no check in place for EOF.

She feels really comfortable and likes using DCount but isn't having any success.

I added an if statement in my last post to provide for a check to determine and prevent updating if the user manually entered a number greater than what is listed in the record count.

Still with us? j/k LOL

Sounds good.
J

[Denburt]

I'm still here! lol, I was handed a project georeferencing about 10 images so had to take a break from the db. :(

Cool, that is one thing I love about my job one day I could be working on one thing the next it's almost like I am on another planet working on something else.

[AccessIdiot]

Sorry edited your post instead of a reply.

[Denburt]

Where is the line of code in question? It would appear to be located in the subform yet I am asking about the event that runs it.

[AccessIdiot]

lol was my post that bad? ;)

I used your code above on the current event of the subform:

Private Sub Form_Current()
Dim rs As Object
Set rs = Me.RecordsetClone
If Not rs.EOF Then
    If rs.RecordCount > Me.Parent.SpecimenCount Then
        Me.Parent.SpecimenCount = rs.RecordCount
    End If
Else
    Me.Parent.SpecimenCount = 0
End If
End Sub

And the line the debugger highlights is the Else statement Me.Parent.SpecimenCount = 0
Says it can't assign the value to the object.

[Denburt]

LOL no it wasn't your post it was me...

This should resolve that issue.

Private Sub Form_Current()
Dim rs As Object
If Me.Parent.NewRecord = True Then Exit Sub
 
Set rs = Me.RecordsetClone
If Not rs.EOF Then
    If rs.RecordCount > Me.Parent.SpecimenCount Then
        Me.Parent.SpecimenCount = rs.RecordCount
    End If
Else
    Me.Parent.SpecimenCount = 0
End If
End Sub

[AccessIdiot]

Now I am getting the same error on this line:

  Me.Parent.SpecimenCount = rs.RecordCount

Maybe the else statement needs to go with the 2nd if and not the first if? Or maybe the 2nd if statement needs its own else statement?

[Denburt]

I cleaned it up there were a few things needed touching up but shouldn't have any effect towards that error.

Make sure you have the SpecimenCount fields default value set to 0 that was the only issue I had when I tested it.

Private Sub Form_Current()
Dim rs As Recordset
If Me.Parent.NewRecord = True Then Exit Sub
Set rs = Me.RecordsetClone
If Not rs.EOF Then
    If rs.RecordCount > Me.Parent!SpecimenCount Then
        Me.Parent!SpecimenCount = rs.RecordCount
    End If
Else
    Me.Parent!SpecimenCount = 0
End If
rs.Close
Set rs = Nothing
End Sub

[Rabbit]

Check the SpecimenCount object and its control source. If its control source is the result of a calculation, then it's not updateable. If it's an autonumber then it's not updateable.

[AccessIdiot]

Um, don't kill me but . . .
Same error on line:

Me.Parent!SpecimenCount = rs.RecordCount

I tried substituting ! with . and same thing. :(

SpecimenCount is just a blank textbox.

[Denburt]

Um, don't kill me but . . .
Same error on line:

Me.Parent!SpecimenCount = rs.RecordCount

I tried substituting ! with . and same thing. :(

SpecimenCount is just a blank textbox.

Any better now? :) 2. 3. 10

[AccessIdiot]

Any better now? :) 2. 3. 10

:( Believe it or not I'm still getting the same error message on the same line. I'm going from frm_Entrainment to frm_Specimen_Entrainment by clicking the "View or Add Specimens" button and thats when the error pops up.

By the way, were smilies disabled?

[Denburt]

I did get a yellow break in the VBA code as if I didn't clear a line break but I simply hit F5 and it resolved that issue. I did run into another issue so i am resending it this one should be O.K.

[AccessIdiot]

Didn't know about F5! I'll try that out next time. Thanks for resending, I'll check it out in a minute when I can get all the little tasks out the door.

[AccessIdiot]

Same problem. I'm beginning to think this just isn't worth the (your) effort. :-D

[Denburt]

Strange are you sure your using the latest or most recent copy i sent?

Try to compile it on your machine and check for any missing references. I have no idea what the problem might be, it is working fine here and I have tested about every scenario I could think of. I just opened it reran it compiled it checked for missing references... I really don't know what the issue could be.

[AccessIdiot]

This is the code you wrote right? On the current event of sbfrm_FishSpecimen_Entrainment?

Private Sub Form_Current()
Dim rs As Object
If Me.Parent.NewRecord = True 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

When I run the compiler it seems fine. When I open frm_Entrainment to the first record and click the button "View or Add Specimens" I get the error. The count is recording 0 though there are 4 records in the Fish Specimen Details section (the subform).

Weird no?

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

Private Sub Form_Current()
Dim rs As Object
If Me.Parent.NewRecord = True Then Exit Sub
If Me.Dirty Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
End If
Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
If rs.RecordCount > Me.Parent.SpecimenCount Then
Me.Parent.SpecimenCount = rs.RecordCount
End If
rs.Close
Set rs = Nothing
End Sub
 

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

Private Sub Form_Current()
Dim rs As Object
If Me.Parent.NewRecord = True Then Exit Sub
If Me.Dirty Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
End If
Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
If rs.RecordCount > Me.Parent.SpecimenCount Then
Me.Parent.SpecimenCount = rs.RecordCount
End If
rs.Close
Set rs = Nothing
End Sub
 

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.

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
 

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.MoveLast".

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

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

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_Entrainment. On this form is the subform "sbfrm_FishSpecimen_Entrainment". 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_Entrainment. On this form is the subform "sbfrm_FishSpecimen_Entrainment". 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.MoveLast".

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.

if rs.RecordCount > 0 then
do this
end if

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

If Not rs.EOF And Not rs.BOF Then
do this
end if

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
 
 If Not rs.EOF And Not rs.BOF Then
 
    rs.MoveLast
      rs.MoveFirst
      Msgbox "Subform Record Count is " & rs.RecordCount & _
      " and SpecimanCount is " & Me.SpecimenCount
 
 End if
 
      rs.Close
      Set rs = Nothing
        End Sub

[AccessIdiot]

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

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_Entrainment

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_FishSpecimen_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_Entrainment I get:

"No current record" and when I debug it highlights

rs.MoveLast

on the subform (sbfrm_FishSpecimen_Entrainment).

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.

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 Not rs.EOF = True And Not rs.BOF = True Then
rs.MoveFirst
rs.MoveLast
    If rs.RecordCount > Me.Parent.SpecimenCount Then
        Me.Parent.SpecimenCount = rs.RecordCount
    End If
End If
rs.Close
Set rs = Nothing
End Sub
 

[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