By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,022 Members | 856 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,022 IT Pros & Developers. It's quick & easy.

Problems with DCount

AccessIdiot
100+
P: 493
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim CountFish As Integer
  2. Private Sub Form_AfterUpdate()
  3. CountFish = Nz(DCount("Fish_ID", "tbl_Specimen_Fish_Entrainment", "Specimen_ID = " & [Specimen_ID]), 0)
  4. 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!
May 11 '07 #1
Share this Question
Share on Google+
51 Replies


JConsulting
Expert 100+
P: 603
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim CountFish As Integer
  2. Private Sub Form_AfterUpdate()
  3. CountFish = Nz(DCount("Fish_ID", "tbl_Specimen_Fish_Entrainment", "Specimen_ID = " & [Specimen_ID]), 0)
  4. 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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As Object
  3. Set rs = Me.RecordsetClone
  4. Me.Parent.MyCountBox = rs.RecordCount  '<--change MyCountBox to your textbox
  5. End Sub
  6.  
J
May 12 '07 #2

AccessIdiot
100+
P: 493
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
May 15 '07 #3

Denburt
Expert 100+
P: 1,356
Expand|Select|Wrap|Line Numbers
  1.       Private Sub Form_Current()
  2.       Dim rs As Object
  3.       Set rs = Me.RecordsetClone
  4. if not rs.eof then
  5.       Me.Parent.MyCountBox = rs.RecordCount  '<--change MyCountBox to your textbox
  6. else
  7. Me.Parent.MyCountBox = 0
  8. end if
  9.       End Sub
This should take care of the error.
May 15 '07 #4

JConsulting
Expert 100+
P: 603
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?
May 15 '07 #5

Denburt
Expert 100+
P: 1,356
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.
Expand|Select|Wrap|Line Numbers
  1. CountFish = Nz(DCount("*", "tbl_Specimen_Fish_Entrainment", "Specimen_ID = " & [Specimen_ID]), 0)
May 15 '07 #6

AccessIdiot
100+
P: 493
??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 . . .?
May 15 '07 #7

Denburt
Expert 100+
P: 1,356
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.
May 15 '07 #8

AccessIdiot
100+
P: 493
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."
May 15 '07 #9

Denburt
Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.       Private Sub Form_Current()
  3.             Dim rs As Object
  4.             Set rs = Me.RecordsetClone
  5.       if not rs.eof then
  6.           if rs.RecordCount>Me.Parent.MyCountBox then
  7.             Me.Parent.MyCountBox = rs.RecordCount  '<--change MyCountBox to your textbox
  8.            end if
  9.       else
  10.       Me.Parent.MyCountBox = 0
  11.       end if
  12.             End Sub
May 15 '07 #10

JConsulting
Expert 100+
P: 603
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As Object
  3. Set rs = Me.SubformOne.Form.RecordsetClone
  4. Me.MySubCount = rs.RecordCount
  5. End Sub
  6.  
In that scenario, this works.
J
May 15 '07 #11

Denburt
Expert 100+
P: 1,356
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As Object
  3. Set rs = Me.SubformOne.Form.RecordsetClone
  4. Me.MySubCount = rs.RecordCount
  5. End Sub
  6.  
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
May 15 '07 #12

AccessIdiot
100+
P: 493
I'm still here! lol, I was handed a project georeferencing about 10 images so had to take a break from the db. :(
May 15 '07 #13

JConsulting
Expert 100+
P: 603
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
May 15 '07 #14

Denburt
Expert 100+
P: 1,356
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.
May 16 '07 #15

AccessIdiot
100+
P: 493
Sorry edited your post instead of a reply.
May 25 '07 #16

Denburt
Expert 100+
P: 1,356
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.
May 25 '07 #17

AccessIdiot
100+
P: 493
lol was my post that bad? ;)

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As Object
  3. Set rs = Me.RecordsetClone
  4. If Not rs.EOF Then
  5.     If rs.RecordCount > Me.Parent.SpecimenCount Then
  6.         Me.Parent.SpecimenCount = rs.RecordCount
  7.     End If
  8. Else
  9.     Me.Parent.SpecimenCount = 0
  10. End If
  11. 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.
May 25 '07 #18

Denburt
Expert 100+
P: 1,356
LOL no it wasn't your post it was me...

This should resolve that issue.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As Object
  3. If Me.Parent.NewRecord = True Then Exit Sub
  4.  
  5. Set rs = Me.RecordsetClone
  6. If Not rs.EOF Then
  7.     If rs.RecordCount > Me.Parent.SpecimenCount Then
  8.         Me.Parent.SpecimenCount = rs.RecordCount
  9.     End If
  10. Else
  11.     Me.Parent.SpecimenCount = 0
  12. End If
  13. End Sub
May 25 '07 #19

AccessIdiot
100+
P: 493
Now I am getting the same error on this line:

Expand|Select|Wrap|Line Numbers
  1.   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?
May 25 '07 #20

Denburt
Expert 100+
P: 1,356
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As Recordset
  3. If Me.Parent.NewRecord = True Then Exit Sub
  4. Set rs = Me.RecordsetClone
  5. If Not rs.EOF Then
  6.     If rs.RecordCount > Me.Parent!SpecimenCount Then
  7.         Me.Parent!SpecimenCount = rs.RecordCount
  8.     End If
  9. Else
  10.     Me.Parent!SpecimenCount = 0
  11. End If
  12. rs.Close
  13. Set rs = Nothing
  14. End Sub
May 25 '07 #21

Rabbit
Expert Mod 10K+
P: 12,359
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.
May 25 '07 #22

AccessIdiot
100+
P: 493
Um, don't kill me but . . .
Same error on line:
Expand|Select|Wrap|Line Numbers
  1. Me.Parent!SpecimenCount = rs.RecordCount
I tried substituting ! with . and same thing. :(

SpecimenCount is just a blank textbox.
May 25 '07 #23

Denburt
Expert 100+
P: 1,356
Um, don't kill me but . . .
Same error on line:
Expand|Select|Wrap|Line Numbers
  1. Me.Parent!SpecimenCount = rs.RecordCount
I tried substituting ! with . and same thing. :(

SpecimenCount is just a blank textbox.
Any better now? :) 2. 3. 10
May 31 '07 #24

AccessIdiot
100+
P: 493
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?
May 31 '07 #25

Denburt
Expert 100+
P: 1,356
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.
May 31 '07 #26

AccessIdiot
100+
P: 493
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.
May 31 '07 #27

AccessIdiot
100+
P: 493
Same problem. I'm beginning to think this just isn't worth the (your) effort. :-D
May 31 '07 #28

Denburt
Expert 100+
P: 1,356
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.
May 31 '07 #29

AccessIdiot
100+
P: 493
This is the code you wrote right? On the current event of sbfrm_FishSpecimen_Entrainment?
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As Object
  3. If Me.Parent.NewRecord = True Then Exit Sub
  4. If Me.Dirty Then
  5.     DoCmd.RunCommand acCmdSelectRecord
  6.     DoCmd.RunCommand acCmdSaveRecord
  7. End If
  8. Set rs = Me.RecordsetClone
  9.     If rs.RecordCount > Me.Parent.SpecimenCount Then
  10.         Me.Parent.SpecimenCount = rs.RecordCount
  11.     End If
  12. rs.Close
  13. Set rs = Nothing
  14. 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?
May 31 '07 #30

Denburt
Expert 100+
P: 1,356
Did you check the references and of course it should always be a first did you try to reboot? It is strange.
May 31 '07 #31

AccessIdiot
100+
P: 493
Yes everything checks out. I opened the db you sent right from the email and I still get the error. :*-(
May 31 '07 #32

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Melissa,

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As Object
  3. If Me.Parent.NewRecord = True Then Exit Sub
  4. If Me.Dirty Then
  5. DoCmd.RunCommand acCmdSelectRecord
  6. DoCmd.RunCommand acCmdSaveRecord
  7. End If
  8. Set rs = Me.RecordsetClone
  9. rs.MoveLast
  10. rs.MoveFirst
  11. If rs.RecordCount > Me.Parent.SpecimenCount Then
  12. Me.Parent.SpecimenCount = rs.RecordCount
  13. End If
  14. rs.Close
  15. Set rs = Nothing
  16. End Sub
  17.  
Mary
Jun 1 '07 #33

AccessIdiot
100+
P: 493
Hi Mary,

Thanks for jumping in, but I'm still getting the same error message. :(
Jun 4 '07 #34

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Melissa,

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As Object
  3. If Me.Parent.NewRecord = True Then Exit Sub
  4. If Me.Dirty Then
  5. DoCmd.RunCommand acCmdSelectRecord
  6. DoCmd.RunCommand acCmdSaveRecord
  7. End If
  8. Set rs = Me.RecordsetClone
  9. rs.MoveLast
  10. rs.MoveFirst
  11. If rs.RecordCount > Me.Parent.SpecimenCount Then
  12. Me.Parent.SpecimenCount = rs.RecordCount
  13. End If
  14. rs.Close
  15. Set rs = Nothing
  16. End Sub
  17.  
Mary
Melissa

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

Mary
Jun 4 '07 #35

AccessIdiot
100+
P: 493
Yep, sure did! Could the problem be the path through other forms to get to this spot?
Jun 4 '07 #36

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this so we can see where the problem is.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As Object
  3.  
  4.     Set rs = Me.RecordsetClone
  5.     rs.MoveLast
  6.     rs.MoveFirst
  7.  
  8.     Msgbox "Subform Record Count is " & rs.RecordCount & _
  9. " and SpecimanCount is " & Me.Parent.SpecimenCount 
  10.  
  11.     rs.Close
  12.     Set rs = Nothing
  13. End Sub
  14.  
Mary
Jun 4 '07 #37

AccessIdiot
100+
P: 493
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".
Jun 4 '07 #38

MMcCarthy
Expert Mod 10K+
P: 14,534
OK, now try putting this in the current event of the main form and see what happens (don't forget to substiture your subformName)

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As Object
  3.  
  4. Set rs = Me.SubformName.Form.RecordsetClone
  5. rs.MoveLast
  6. rs.MoveFirst
  7.  
  8. Msgbox "Subform Record Count is " & rs.RecordCount & _
  9. " and SpecimanCount is " & Me.SpecimenCount 
  10.  
  11. rs.Close
  12. Set rs = Nothing
  13. End Sub
  14.  
Mary
Jun 4 '07 #39

AccessIdiot
100+
P: 493
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.
Jun 4 '07 #40

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jun 4 '07 #41

Denburt
Expert 100+
P: 1,356
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.
Expand|Select|Wrap|Line Numbers
  1. if rs.RecordCount > 0 then
  2. do this
  3. end if
Or another more popular method since it will be recognized by most recordsets

Expand|Select|Wrap|Line Numbers
  1. If Not rs.EOF And Not rs.BOF Then
  2. do this
  3. end if

Otherwise you will receive the error "No current record"
Jun 5 '07 #42

Denburt
Expert 100+
P: 1,356
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.
Jun 5 '07 #43

AccessIdiot
100+
P: 493
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
Expand|Select|Wrap|Line Numbers
  1. If Not rs.EOF And Not rs.BOF Then
around the rs.MoveLast rs.MoveFirst?
Jun 5 '07 #44

Denburt
Expert 100+
P: 1,356
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.       Dim rs As DAO.recordset
  3.       Set rs = Me.SubformName.Form.RecordsetClone
  4.  
  5. If Not rs.EOF And Not rs.BOF Then
  6.  
  7.     rs.MoveLast
  8.       rs.MoveFirst
  9.       Msgbox "Subform Record Count is " & rs.RecordCount & _
  10.       " and SpecimanCount is " & Me.SpecimenCount
  11.  
  12. End if
  13.  
  14.       rs.Close
  15.       Set rs = Nothing
  16.         End Sub
Jun 5 '07 #45

AccessIdiot
100+
P: 493
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.
Jun 5 '07 #46

AccessIdiot
100+
P: 493
Okay at this point it will probably help if I explain exactly what I have where. :)

On frm_Specimen_Entrainment
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim rs As DAO.Recordset
  3. Set rs = Me.sbfrm_FishSpecimen_Entrainment.Form.RecordsetClone
  4. If Not rs.EOF And Not rs.BOF Then
  5. rs.MoveLast
  6. rs.MoveFirst
  7. MsgBox "Subform Record Count is " & rs.RecordCount & _
  8. " and SpecimenCount is " & Me.SpecimenCount
  9. End If
  10. rs.Close
  11. Set rs = Nothing
  12. End Sub
and on sbfrm_FishSpecimen_Entrainment (a subform of the above)
Expand|Select|Wrap|Line Numbers
  1.       Private Sub Form_Current()
  2.       Dim rs As Object
  3.  
  4.           Set rs = Me.RecordsetClone
  5.           rs.MoveLast
  6.           rs.MoveFirst
  7.  
  8.           MsgBox "Subform Record Count is " & rs.RecordCount & _
  9.       " and SpecimanCount is " & Me.Parent.SpecimenCount
  10.           rs.Close
  11.           Set rs = Nothing
  12.       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
Expand|Select|Wrap|Line Numbers
  1. 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
Jun 5 '07 #47

Denburt
Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim IntCurCnt As Integer
  4. Private Sub Form_Current()
  5. Dim rs As Object
  6. IntCurCnt = IntCurCnt + 1
  7. If Me.Parent.NewRecord = True Or IntCurCnt = 1 Then Exit Sub
  8. If Me.Dirty Then
  9.     DoCmd.RunCommand acCmdSelectRecord
  10.     DoCmd.RunCommand acCmdSaveRecord
  11. End If
  12. Set rs = Me.RecordsetClone
  13. If Not rs.EOF = True And Not rs.BOF = True Then
  14. rs.MoveFirst
  15. rs.MoveLast
  16.     If rs.RecordCount > Me.Parent.SpecimenCount Then
  17.         Me.Parent.SpecimenCount = rs.RecordCount
  18.     End If
  19. End If
  20. rs.Close
  21. Set rs = Nothing
  22. End Sub
  23.  
Jun 5 '07 #48

AccessIdiot
100+
P: 493
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!
Jun 5 '07 #49

Denburt
Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1.    Option Compare Database
  2.       Option Explicit
  3.       Dim IntCurCnt As Integer
  4.       Private Sub Form_Current()
  5.       Dim rs As Object
  6.       IntCurCnt = IntCurCnt + 1
  7.       If Me.Parent.NewRecord = True Or IntCurCnt = 1 Then Exit Sub
  8.       If Me.Dirty Then
  9.           DoCmd.RunCommand acCmdSelectRecord
  10.           DoCmd.RunCommand acCmdSaveRecord
  11.       End If
  12.       Set rs = Me.RecordsetClone
  13.           If rs.RecordCount > Me.Parent.SpecimenCount Then
  14.               Me.Parent.SpecimenCount = rs.RecordCount
  15.           End If
  16.       rs.Close
  17.       Set rs = Nothing
  18.       End Sub
  19.  
Jun 5 '07 #50

51 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.