Dcount not working in unbound textbox

[skydivetom]

Happy Sunday:



- I'd like to set a **subform's** control "Navigation Buttons" = "No".
- At the same time, I still would like to see the number of records (from that subform) in an unbound textbox on the **mainform**.
- For testing purposes only, I added the following on the current event in the main form.

Private Sub Form_Current()    
    MsgBox DCount("ID", "Query1", "ID=" & Me.ID), vbInformation, "Count of Records in Subform"
End Sub

- The pop-up message boxes display the correct number of records from the subform when navigating through the 9 main records.
- However, I would like to remove the msgbox and replace the record with the code listed below.

Private Sub Form_Current()
    Dim CountID As Integer
    Me.CountID = DCount("ID", "Query1", "ID=" & Me.ID)
End Sub

- Unfortunately, when replacing the msgbox with the 2 lines above, I get a run-time error "You can't assign value to this object."

What is missing/incorrect in the VBA so that I can display the subform's record counts of {8, 7, 5, 4, 7, 3, 5, 8, 10} on the mainform's **green** unbound text box [CountID] when navigating through the 9 records.?

Thanks in advance.

[June7]

You aren't counting records on form, this is counting records in table. If a filter is applied to subform, count will not reflect subform dataset.

VBA should not be needed. Just use DCount() expression in textbox ControlSource. Remove Me. from textbox expression.

Declared variables are not prefixed with Me. And don't need to declare a variable to simply set a textbox value. However, since the textbox has a calculated ControlSource, VBA cannot set its value. Me. tells VBA to address the textbox, not the declared variable, which, BTW should not be same name as textbox.

[skydivetom]

Works great in "Dummy" version... thank you.

[skydivetom]

June7:

I have a quick follow-up question. I am 99% confident the answer is staring right in my face, but I am unable to see the difference between my "dummy solution" (where Dcount is working) and my "actual solution (where Dcount is not working).

Attached are 2 DBs:
1. Dummy version
2. Actual version

Info on Dummy version:
1. Upon opening form "F01_MainMenu", the green textbox shows the Dcount values.
2. Navigating through all shows the correct Dcount for each of the 9 records (these numbers match the record counts in the subform's navigation button).
3. Lastly, for testing only, I also created query "qry99_Counts" which, again, show the correct number of records when grouping/counting on field [ID].

... so, everything works great in the dummy version.

Info on Actual version:
- I copied the Dcount control (green textbox) from the dummy version into my actual DB.
- Also copied query "qry99_Counts" into this version.

... and this is where I'm stumbled.

Issue:
- The counts in "qry99_Counts" (Acutal) are different than the counts in "qry99_Counts" (Dummy).
- Thus, the same applies for the =DCount("ID","qry02_Approach#1_Results","ID=" & [ID]) expression... I get the incorrect number of values in my Dcount control on the mainform.

Validation:
- In my actual query "qry02_Approach#1_Results", if I filter on [ID] = 1, it returns eight (8) records... which is the correct answer.

My question:
Why does "qry99_Counts" (Actual) return, e.g., [ID] = 1 with only four (4) records when the answer should be eight (8)? Again, a manual filtering on "qry02_Approach#1_Results" returns the correct #.

[Gicu]

Hi Tom,

Here is an updated file with a slightly different approach - it uses the subform's recordsetclone.recordcount property to accurately display the number of records in the subform even when it is filetered.

Cheers,

[skydivetom]

Vlad:

This is an excellent way of handling it... sooo much smoother. I'm excited that the subform count is finally showing correctly.

I will hide the control on the mainform.

Now, given the count is working, I thought it might be easy for the user to include them in the header of the subform.

So, currently I have a label called "MATCHES". I'm thinking of changing the label to a 2nd unbound box and including the count in parenthesis.

In addition to "Me.Parent.Form.Controls("CountID") = Me.Form.RecordsetClone.Recordcount", I added the following line:
Me.SubformCount.Value = Me.Form.RecordsetClone.Recordcount

It display the correct records (e.g., "8" for the 1st record). Great!

Once I added a string prefix & suffix (before/after), I end up with an error. The concatenated string is shown below:
Me.SubformCount.Value = "MATCHES (" & Me.Form.RecordsetClone.Recordcount & ")"

What would be the correct syntax so that I can add text before & after? For example, output for 1st record should read: "MATCHES (8)"

Cheers,
Tom

[June7]

If you want to count filtered subform records, again, no VBA is needed.

Give subform container a name like ctrDetails. In subform header or footer, textbox named tbxTotal with expression: =Count(*). Can set this section or textbox as not visible. Then textbox on main form: =[ctrDetails].Form.tbxTotal or ="Matches(" & [ctrDetails].[Form].[tbxTotal] & ")". Should see the calcs perform faster than DCount - DCount has a noticeable delay.

The posted Dummy is not working - it still has Me. in textbox expression.
The posted Actual is not using DCount - it is Vlad's version using RecordsetClone.

Strongly advise not to use punctuation/special characters (underscore is only exception) in naming convention.

All code modules should have Option Explicit in header. Set this to happen automatically when new module created.
From the VBA edidtor: Tools > Options > Editor > check Require Variable Declaration.

[skydivetom]

June7 -- thank you for the additional info.

1. Added "Option Explicit"
2. Remove the punctuation from the "MATCHES"... I still have them as separate controls but that ok now.

Attached is the latest working version. Again, thank you and Vlad for the additional assistance... very much appreciated.

[June7]

The punctuation I meant was # and other characters in query and other object names, not characters in "MATCHES" expression.