DCOUNT: Value In Text Box Not Updating in Real-Time. Can it?

[tatihulot]

Hello, I am using Access 2013 and would like some assistance with the DCOUNT function, please.
I've created a very simple music collection database, with two tables:

Album
Album ID
Album Name
Artist ID

Artist
Artist ID


DVD
Artist Last Name
Artist First Name
Artist Full Name

The relationship is joined via the Artist ID fields.

The Artist table comprises the Main form, and there is a continuous subform created from the Album table.

I have not created any queries for this database…not yet, at least.

I want a text box in the Main form that displays the total number of albums in the collection.

I've created such a text box with the following DCOUNT formula:

=DCount("[Album ID]","Album")

Works properly, except for one thing: It does not update in real time. By that, I mean whenever I enter in a new album in the subform, I would like the DCOUNT text box to update immediately, and show the updated count.

As of this writing, I am not sure if this is possible, so I am requesting help to find out if it can be done.

In order for the DCOUNT value to update, I have to close the form and reopen it.

Have I written the DCOUNT formula incorrectly?

Is it possible to have the DCOUNT text box update in real time? If so, what must I change? Do I need to use a different function other than DCOUNT to get what I want?

Would this work better with a query?
Thank you for your help. TH

[ssanfu]

You can press the F9 key to recalculate calculated controls on a form.

Or you could add this code to the form:

Private Sub Form_AfterUpdate()
   Me.Recalc
End Sub

[tatihulot]

Thanks for replying, again. The F9 key option works....but....I'm just curious as to why it isn't able to update without user input or additional coding.

Where do I place that code snippet? Thank you again.

[ssanfu]

Somehow/something must tell Access to recalculate calculated controls.

Excel is by default set to automatic recalculation; you can set recalc to manual. Then you must press the F9 key the get Excel to recalculate all formulas.



The code example goes in the form module:
Open the form in design view.
Open the form properties dialog box.
Click on the Events Tab.
Find the "After Update" property.
Click on the ellipsis (...)
Enter "Me.Recalc" (no quotes) so it looks like my post.

[tatihulot]

Something isn't right.

I followed your instructions. Assuming I followed them correctly, the DCOUNT field is not updating automatically.

If I add an album--or delete an album--in the subform, will your code automatically update the DCOUNT field without any input on my part?

If so, then something's wrong. It does not display an updated quantity. I have to either press the F9 key, or move back or forth through the records, in order for the update to appear.

Not quite what I'm after. Are you saying the DCOUNT value cannot be updated automatically, and in real time, without any user input?

Is it all right if I attach the database, in .zip format, to this post, so you can see what I've done? Thanks again for your help.

Not having it update immediately is not a deal breaker. I really don't mind all that much if I have to update the DCOUNT value manually, by pressing F9. But if it CAN update in real time automatically, then I'd prefer that.

Music Inventory.zip

[ssanfu]

You added the code correctly....but I told you the wrong place to put the code.

It confused me for a bit when I tried to add a new artist. Turns out you had the (table) calculated field displayed on the form.

I renamed your fields to remove the spaces.
Then I created a new form. I created a new macro (which I never use) - the "Autoexec" will open the form. I use the other method to open a form.
The DCount()_ you used gave a TOTAL album count, so I added a DCount() that provides an album count by artist.

What is the DVD number?

BTW, you have two #15 and have Aerosmith entered twice.

[tatihulot]

Let me take a moment and explain this music database.

I have a huge music collection, which I've converted to MP3 format. The entire collection is stored on an external hard drive. I'm concerned about the hard drive failing at some point. If it does, I lose the entire collection. So, I am backing up the collection onto blank DVD media.

That is what the DVD number is. A typical 4.7GB blank DVD can hold almost 50 albums. A dual layer 8.5GB blank DVD can hold almost 90.
So, one DVD can hold several albums by several artists.

The data you saw in my database is dummy, placeholder data that I just plopped in there at random, just to see if the database worked. When the design is done (it almost is), I will delete that data and put the correct data in.

I do not need, or want, to see the entire contents of a DVD in the form. If I want to see a DVD's entire contents, I can generate a report. Also, if I want to see everything in my collection by one particular artist, I can generate a query, and base a report on that.

Furthermore, I am interested only in the TOTAL count. A running album count by artist is not a concern at this point. I might change my mind about that, however, so it's good to know how to do that. Thank you for that.
The form/subform are primarily for data entry.

The form's interface is exactly how I want it, although it needs some minor tweaking. I did plan on changing the names of the tables, and did plan on eliminating spaces in the field names. Also, I want to add an ADD NEW RECORD button, which should take about ten seconds. I am also considering radio buttons to designate whether the blank media is a CD, a standard DVD, or a Dual Layer DVD. I doubt I'm going to do that, though.

With all this being said, I have one more question: Since you said you gave me the wrong location to place the code, could you please tell me the correct location now?

Thank you for tweaking my database. I appreciate the time and effort you took to do that. With all due respect, I am comfortable with my design. Once the DCOUNT problem is solved, I will consider the essential design complete.

Thanks again.

[tatihulot]

Actually, I have another question.

I found the DCOUNT code in your database, by opening up Visual Basic. Here's what is displayed:

Option Compare Database
Option Explicit

Private Sub Form_AfterDelConfirm(Status As Integer)
Me.Recalc
End Sub

Private Sub Form_AfterUpdate()
Me.Recalc
End Sub


This is different from what you originally sent me. Should I enter all of this, or just what you originally gave me? Where in my database is the proper place to drop this code? Thanks again.

[Nod_Swerdna]

I've had good luck using the full descriptive control name to update values on another 'OPEN' form from the current form that has the User focus.

Private Sub Form_AfterUpdate() ' Place these 3 lines of code in the VBA Code of your subform. It will be called each time you 'update' the subform and should write the new number of albums to txtCount control the mainform.
Forms!Mainform.txtCount.value=DCount("[Album ID]","Album") 'Where txtCount is your text box that displays the total number of albums in the collection and Mainform is the name of your main form.
End Sub

[tatihulot]

I am not confident I am doing this right.
I am not 100% which part of the VB screen to enter the code. There is an option for GENERAL, but I confess I am not sure if I'm entering it in the right place. I definitely modified the code to reflect the name of the main form, but I'm not 100% sure if I need to leave Sub Form alone, or rename that section, as well.

Can we work on this a bit further? I'm a bit of a novice, so sometimes I need my hand held. Thank you.

[Missinglinq]

There is/was a problem with 2010/2013 in that calculated fields, such as

=DCount("[Album ID]","Album")

would only update by either tabbing/clicking into the textbox or by closing/re-opening the form, and this sounds very much like the situation you're describing. There is, I believe, a service pack that addresses this issue, among others, but I don't have the S/P number or download location, as I run 2007 and have no need for it. As odd as it may sound, the immediate fix (and this will also serve to test whether or not this is your problem) is to simply do a cold reboot of your PC! The problem reportedly occurs when the PC has been up and running for an extended period of time.

As to

...Have I written the DCOUNT formula incorrectly...

Normally, when using DCount to count all Records (which is what you're doing, here, assuming that there is a Record for each album) you'd use

=DCount("*","Album")

The problem with using a Field, such as you did with


=DCount("[Album ID]","Album")

is that any Record where [Album ID] is Null will not be included in the count.

Linq ;0)>

[tatihulot]

Thank you for replying. What would cause an album ID to be null? What circumstances would cause that?

My computer is on 24/7. I've never heard of a software application that becomes faulty when the computer has been running a while. What would cause that?

[tatihulot]

I have to redesign the database. I see a design flaw with the input of the artist's name.

[ssanfu]

Let me take a moment and explain this music database.

I have a huge music collection, which I've converted to MP3 format. The entire collection is stored on an external hard drive. I'm concerned about the hard drive failing at some point. If it does, I lose the entire collection. So, I am backing up the collection onto blank DVD media.

That is what the DVD number is. A typical 4.7GB blank DVD can hold almost 50 albums. A dual layer 8.5GB blank DVD can hold almost 90.
So, one DVD can hold several albums by several artists.

You hadn't provided much info about the dB.... I was curious why multiple artists with the same DVD number. Makes great sense.

The data you saw in my database is dummy, placeholder data that I just plopped in there at random, just to see if the database worked. When the design is done (it almost is), I will delete that data and put the correct data in.

The data looked as if it was real data...... couldn't tell if it was dummy data or not.

I do not need, or want, to see the entire contents of a DVD in the form. If I want to see a DVD's entire contents, I can generate a report. Also, if I want to see everything in my collection by one particular artist, I can generate a query, and base a report on that.

Nothing I did would display data by DVD number. The form I created was one way to enter a new artist, then enter songs by that artist. It was also an easy way to find (scroll to) an artist to add additional albums and to see what albums were previously entered.

Furthermore, I am interested only in the TOTAL count. A running album count by artist is not a concern at this point. I might change my mind about that, however, so it's good to know how to do that. Thank you for that.
The form/subform are primarily for data entry.

The total number of albums control does display in the main form, just like your form does.. It was easy to add a control to display the number of albums to be able to know how many albums were entered without having to manually count then. It was meant as a learning example. If you don't like the control, delete it or don't use it.
Furthermore, your form/subform only allowed you to enter only albums, not artists.

The form's interface is exactly how I want it, although it needs some minor tweaking. I did plan on changing the names of the tables, and did plan on eliminating spaces in the field names. Also, I want to add an ADD NEW RECORD button, which should take about ten seconds. I am also considering radio buttons to designate whether the blank media is a CD, a standard DVD, or a Dual Layer DVD. I doubt I'm going to do that, though.

It is your dB. No one is forcing you to use anything you don't want. Your design does have some flaws, such as spaces in object names, calculated fields, etc.
It was easier to fix the naming issues and then demonstrate how to create the "full name" in a query, rather than have a calculated field in a table. Also, (IMO) it is better to use a query or put the SQL in the record source property; using a table as the record source....not as much.

With all this being said, I have one more question: Since you said you gave me the wrong location to place the code, could you please tell me the correct location now?

In the subform form open event. Open the subform in design view and look at the code. See the dB.

Thank you for tweaking my database. I appreciate the time and effort you took to do that. With all due respect, I am comfortable with my design. Once the DCOUNT problem is solved, I will consider the essential design complete.

Since you are comfortable with your design, use it. There are ways to enhance your dB, but......


I also never use macros. But to open the dB to your form (frmArtist), the macro name should be "Autoexec" not "Open".
An alternate method: click on FILE, Options, Current Database, then set the option for "Display Form".


Good luck with your project.
I won't monitor this thread anymore.

[Nod_Swerdna]

I'm sorry I was away for awhile. I can think of a few different places to put the code. I take it you use the subform to add new albums to your collection. If this is the case you can add the code to the subform itself as follows:
-Open the Navigation Pane on the left of the screen when your program is running,
-Right click the subform and choose 'Design View' from the menu that pops up.
-On the property sheet to the right scroll down to the 'After Update' property.
-Click on the elipsys symbol (three dots) at the right end of the line.
-Choose 'Code Builder' from the menu that pops up.
-The VBA window will open and the curser will be between the Sub ... and the End Sub lines.
-This is where you place the three lines of code.
-Click 'File' - 'Save' and then 'File' - 'Close'
-If you have a 'Delete Control' that you use to delete an album from your database you can right click on that control as well. (while the subform is in design view)
-When the property sheet appears, scroll down to the 'After Update' line and click on the elipsys on the right of the line.
-Choose 'Code Builder' and when the VBA code window opens type in the three lines. Save the Form Code as above. (repeat for any other controls that change the number of albums in your database)