Problems with DCount

[AccessIdiot]

I have a form (frm_Specimen_E ntrainment) that contains a subform (sbfrm_FishSpec imen_Entrainmen t). 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_E ntrainment) that contains a subform (sbfrm_FishSpec imen_Entrainmen t). 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.Speci menCount = 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.Speci menCount = 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 "RecorsetCo unt" 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.

[CODE=VB]
Private Sub Form_Current()
Dim rs As Object
Set rs = Me.RecordsetClo ne
if not rs.eof then
if rs.RecordCount> Me.Parent.MyCou ntBox then
Me.Parent.MyCou ntBox = rs.RecordCount '<--change MyCountBox to your textbox
end if
else
Me.Parent.MyCou ntBox = 0
end if
End Sub[/CODE]

[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.Speci menCount = 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.