Can't get DCount statement to work
Windows 7 32bit 
Access 2013
ORIGINAL POST:
"Hi There:
I'm trying to teach myself Access VBA and am a relative newbie.
I'm trying to get a message box to pop up when a form loads. The code is supposed to look at all the calculated values in a specific query field, and pop up if it finds a value above a certain test value. The Query is called "Alkaline Cleaner Graph Query", the calculated field is "%BV". I keep getting a "Run time error 2465, can't find the field referred to in your expression". I must be doing something very basically wrong, but I can't figure it out. Any help would be greatly appreciated.
Private Sub Form_Load()
If (DCount("[%BV]", "[Alkaline Cleaner Graph Query]", [%BV] > 6)) > 0 Then
MsgBox ("Got it!")
End If
End Sub"
-----------------------
UPDATE:
First off, thanks VERY MUCH to everyone who responded to my post above, and helped me get it working.
After getting it to work, I realized it is just what I asked for, but not what I want! Awk! I realized I can't search for any %BV field greater than 6, I have to look at only THE LAST ENTRY and see if it is greater than 6 (or whatever). And I need to do this at form load, and also just when the user has typed in a new value. The problem is that I have chemists doing titrations and entering the ml of titrant into a field on a form every few hours. From that entry, a %BV field is calculated from the ml of titrant field and displayed. If the value in the %BV field is too high, I need to give the chemist a message to add more chemistry to the tank. Ideally, the chemist would add more chemical right away and then do another titration. Hopefully the new titration would be within range and everything would be good. But sometimes the chemist will forget to add the chemical and needs to be reminded when they come back and reload the form later on. So my thought is, warn them both when they enter the most recent value, and also warn them at form load if the most recent record is above the limit.
So anyway, following a suggestion on a MS web page, I created a totals query sorted in natural order and returning just the last record of the chemical analysis table. Then I attempted to use this query to pop up the message box, unfortunately without success.
Private Sub Form_Load()
If Queries.LastBVEntered.LastOfBV > 6 Then
MsgBox ("Add more chemical!")
End If
I haven't attempted yet to pop up the message when the user has just typed in a new value.
This query method seems awfully round-o-bout. Is there a function that just returns the value of a selected field in the last record of a table? That might make things much easier.
Anyway, any more advice for this brain-drained newbie would be greatly appreciated.
-------------
UPDATE #2:
Solved it myself! The DLast function seems to do what I want. I don't know why the MS Dev website says "You can use the DLast function to return a random record from a particular field in a table or query when you simply need any value from that field." That definition confused the heck out of me. Isn't it the *Last* record from a particular field?
Anyway, thanks again
Reply With Quote
Originally Posted by RuralGuy
