DCount Control Update or Focus

[boomslanger]

I have this straightforward DCount control in Control Source that shows the number of Assets.

=DCount("Asset","AssetListFilter")

AssetListFilter is a Query.

Problem I'm having on the form is the count is not shown until I click on the control, then when I move to to then next record it goes blank again until I click on it, otherwise the count works as it should.

This has to be basic, but I cannot find out how to have the control automatically update and show the count whenever I move to another record.

I thought it would be an Event function?

Thanks in advance.

 

[jjturner]

In Form_Current event try:

Me!myControl.Recalc

 

[boomslanger]

Sorry for my ignorance.

I cannot find where to do that.

The name of the form is frm_AssetsUnderMan
The name of the control (unbound text box) is cmd_TotalAsset

Where do I put in the Me!myControl.Recalc?

 

[jjturner]

No worries.

My suggestion may be overkill for the issue you're having, but I don't have a version of Access handy at the moment.

In Design View if you select cmd_TotalAsset, you should see the properties menu. The middle tab on the menu should have a list of events. Go to the OnCurrent event and use the drop down to pick [Event Procedure], then click the ellipsis button to the right (...)

This will take you to the VBA editor where you'll notice the Sub Form_Current.

In between that line and the "End Sub" line insert the Me!cmd_TotalAsset.Recalc

 

[boomslanger]

Access 2007

OK but there is no OnCurrent event under that tab for the control though there is an On Current under the event tab for the Form property sheet. I tried it there and got an error.

 

[jjturner]

my bad - must be getting a bit too late for me

You're right - the On Current is for the Form property

Keep the code there, but change it to Me!cmd_TotalAsset.Requery

 

[boomslanger]

Ok no errors but the same problem. Unless that box has the focus then the total is not displayed when you move onto the next record.

 

[jjturner]

Hmmm - that's certainly unusual

Without Access at hand (I'm on a Mac right now), I'm at a loss as to what the hang-up is. Hopefully someone will jump in here, but in any case I can test it out tomorrow (later today) and get back to you

Cheers,
John

 

[boomslanger]

Really appreciate your help so far, and I continue to experiment.

Ta John.

 

[jjturner]

boomslanger,

just to update you, I had a go at setting a DCount Control Source on a text box and I was unable to reproduce the behavior you're experiencing. While the value temporarily disappears between record moves, it otherwise persistently displays without any manual refocusing or cursor resetting via the mouse.

May I ask what the SQL is for the query "AssetListFilter"?

 

[boomslanger]

Thanks for persisting with this jj.

The SQL is:

SELECT *
FROM tbl_Assets
WHERE ((([Forms]![frm_AssetsUnderMan]![Asset])=[Asset]))
ORDER BY tbl_Assets.Asset DESC;

I do notice the control box briefly blink every now and again as though an update is taking place, but unless the focus is on the control box nothing appears in it.

 

[jjturner]

what happens when you change the WHERE clause to:
WHERE [Asset] = [Forms]![frm_AssetsUnderMan]![Asset]

 

[boomslanger]

Not making any difference, except the WHERE clause gets changed to:

WHERE [Asset]=Forms!frm_AssetsUnderMan!Asset

The square brackets are removed for some reason.

 

[jjturner]

Hmmm... this one's a stumper!

Well, as an alternative, you could move the text box (cmd_TotalAsset) to the Header or Footer section - then change Control Source to = Count([Asset]) and see if that gets you what you need...

 

[jjturner]

I just had a thought -

The behavior is symptomatic of circular reference behavior. And if we consider that the RecordSource for your form may be based on the query "AssetListFilter", then we do indeed have a circular reference.

In other words, we can't be loading the form's RecordSource and at the same time using a value in that RecordSource as a parameter in the query.

If my hunch is correct, then I'd say a rethink of your recordset implementation strategy would be in order...

 

[boomslanger]

Thanks John, I'm on the case I think.

Not that clued-up on Access and was hoping it wouldn't become as complicated as it has. Also haven't got that much more time to spend on this as management are already making noises about the time I'm spending on it whilst other things are pending.

I'll look into in my spare time and your direction has given me insight I didn't have. I'm grateful for that.

Thanks

 

[jjturner]

NP - presuming I've hit on the issue, just keep in mind that the parameters used to feed your query will need to come from an external source or value list - such as a selection from an unbound combo box or list box.

Hopefully if you get your db producing some useful results, "Big Brother" will give you some more 'breathing space'

Best,
John

 

[Core]

Hello, I am having the same problem.

Please see my thread for the specifics: http://www.access-programmers.co.uk/forums/showthread.php?t=178917

Now when I use the exact same database at home I do not have the issue. I feel it must be an issue with the version of Access I have at work.

As a matter of interest what version of Access are you using?

1. Click the Microsoft Office Button
   
   , and then click Access Options.
2. Click Resources, and then click About.
3. To see information about your computer, click System Info.

Access 2007 (12.0.4518.1014) MSO (12.0.4518.1014) <- that's my version of access at work.

Also, if you want (and your database does not contain confidential information) you can send it me and I can test it at home to see if it is said issue.

 

[boomslanger]

Access 2007 (12.0.6423.1000) MSO (12.0.6425.1000)

Can anybody help with what must be a simple thing to implement and maybe in common use?

I have a database that tracks a whole bunch of different company assets under management. What I need in the form is the total for any one asset type when the user is displaying a record for that asset. For example:

Widget A = 47 items
Widget B = 195 items
etc.

These items come in batches from overseas suppliers and after going through out quality control are distributed to customers around the country.

What I need to show on the main display and data entry form is the total amount of any widget on our books and for that to automatically update when the user navigates to another type of widget.

I managed to do this using a query and DCount as I have explained earlier in this thread, but this can't be the right way to go about it as the control does not automatically update and display the count until it has focus or if focus remains with it when you navigate through the records, otherwise it is blank. You can see it flash as though it is updating but nothing appears.

Any help on this would be appreciated, and it seems not just by me.

 

[Core]

Check the post out on the thread I made:

Instead of writing code in control source of text box

use some vba event to populate text box (such as form open event) like

me.textbox1=DCount("[UID]","[qTesting]","[Test Subject]='Numeracy' And [Test Level]='Entry Level' and [Passed]=Yes")

me.textbox2= .......

and so on

Use VBA to do that and then a me.requery to update it with the on current event. (or when ever you want it to update).

Don't use the control source of the text box for dcount.