Update text boxes from CountIf() from a subform table in real time. (1 Viewer)

KeemoSabby

New member
Local time
Today, 02:11
Joined
Sep 9, 2021
Messages
4
I have a form with a sub form table whose values I need to track in text boxes in real-time
basically, I want to set the value of the text boxes with the value from CountIf( fieldbool = true ) from one field, and series of boxes with Countif(fieldx= constant) with a different text box per constant. This needs to be done after each insert in the subform
here is the core query I need to be tracked (the others are all similar, string comparisons from a dropdown box):
SELECT Count(Songs.Cancon) AS CountOfCancon
FROM Logger INNER JOIN Songs ON Logger.Logger_ID = Songs.Logger_ID
HAVING (((Count(Songs.Cancon))=True));

Thanks for any advice
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 09:11
Joined
Feb 19, 2013
Messages
16,553
not quite sure where you are going with this. Your query will return one record (a count) which will by default be true.

Provide some example data and the result required from that data.

best guess is try

Code:
SELECT Count(Songs.Cancon) AS CountOfCancon
FROM Logger INNER JOIN Songs ON Logger.Logger_ID = Songs.Logger_ID
WHERE Cancon=True
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:11
Joined
Feb 19, 2002
Messages
42,981
What is the point of the join?
CountIF() is an Excel function and doesn't work in Access

How about:

SELECT Abs(Sum(Cancon)) AS CountOfCancon
FROM Songs

True = -1 so summing the value and converting it to its absolute value should give you the count. This query isn't going to use an index so there is no need for criteria.

If Logger is actually a table you are using to filter Songs, then add the join back in to the query.
 

KeemoSabby

New member
Local time
Today, 02:11
Joined
Sep 9, 2021
Messages
4
not quite sure where you are going with this. Your query will return one record (a count) which will by default be true.

Provide some example data and the result required from that data.

best guess is try

Code:
SELECT Count(Songs.Cancon) AS CountOfCancon
FROM Logger INNER JOIN Songs ON Logger.Logger_ID = Songs.Logger_ID
WHERE Cancon=True
the query works, but it does not update the text box that needs to display it, and it needs to be updated as records are added.
Once I get the first update from a Count() function to work, the other 6 will be easy
I need to count in one field, those are true, and, 6 different queries one 1 field, 1 for each of the allowable values in that field. The counts do not need to be stored in a table, just be read when the form user is done and printed on a report, but the user needs to know the counts as they are entered. I want it to trigger as a new record is added, I know where, but all my attempts just return errors
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:11
Joined
May 7, 2009
Messages
19,170
can you count the cancon on the Current event of the subform.
see this demo, see the code behind the current event of the subform.
 

Attachments

  • LoggerSong.accdb
    640 KB · Views: 286

CJ_London

Super Moderator
Staff member
Local time
Today, 09:11
Joined
Feb 19, 2013
Messages
16,553
no example data as requested, so still guessing but sounds like your data isn't normalised and maybe this is a case for using the dcount function rather than sql

have a query, called say qryCounter

SELECT *
FROM Logger INNER JOIN Songs ON Logger.Logger_ID = Songs.Logger_ID

in your control

=dcount("*","qryCounter")
 

KeemoSabby

New member
Local time
Today, 02:11
Joined
Sep 9, 2021
Messages
4
no example data as requested, so still guessing but sounds like your data isn't normalised and maybe this is a case for using the dcount function rather than sql

have a query, called say qryCounter

SELECT *
FROM Logger INNER JOIN Songs ON Logger.Logger_ID = Songs.Logger_ID

in your control

no example data as requested, so still guessing but sounds like your data isn't normalised and maybe this is a case for using the dcount function rather than sql

have a query, called say qryCounter

SELECT *
FROM Logger INNER JOIN Songs ON Logger.Logger_ID = Songs.Logger_ID

in your control

=dcount("*","qryCounter")
I will upload it later, an attempt to applly another solution broke to form's data, I have to fix that first, and before that, produce this week's show. The data is normalised, the COUNT() query works just fine, but refuses to update the read only text box that should display it
 

Users who are viewing this thread

Top Bottom