By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,757 Members | 1,990 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,757 IT Pros & Developers. It's quick & easy.

SQL COUNT in Userform TextBox

P: 31
I am using the below SQL Code to count the TblClaim_Status WHERE Claim_Status = 'QUERY'

How to define the output count value in Text Box
if the total count is 2 it should be shown in Text Box
Expand|Select|Wrap|Line Numbers
  1. .
  2.     Dim db As Database
  3.     Dim qdf As QueryDef
  4.     Dim strSQL As String
  5.     Set db = CurrentDb
  6.     Set qdf = db.QueryDefs("Query_Count")
  7.  
  8.     strSQL = "SELECT COUNT(*)" & _
  9.              "FROM  tblClaim_Status " & _
  10.              "WHERE tblClaim_Status.Claim_Status='QUERY'"
  11.     qdf.SQL = strSQL
  12.     DoCmd.OpenQuery "Query_Count"
  13.  
  14.     """""""strSQL = Me.TotalCount"""""""""
  15.  
  16.     Set qdf = Nothing
  17.     Set db = Nothing
Sep 2 '08 #1
Share this Question
Share on Google+
5 Replies


P: 77
I guess if it is a count(*) statement, you'd get only one record (with one column) as an output, which would be the count of WHERE tblClaim_Status.Claim_Status='QUERY'

I think you should refer directly to the output record and not Me.TotalCount and it should resolve.

Thanks.
Sep 2 '08 #2

Expert Mod 2.5K+
P: 2,545
Hi. There is no way to link a Textbox control with the type of code you have constructed.

If you need a count to be displayed in the textbox you should forget about coding it and use the in-built DCount (domain count) function to do it for you instead.

In the control source property of an otherwise unbound textbox all you need to enter is
Expand|Select|Wrap|Line Numbers
  1. =DCount("*", "[tblClaim_Status]", "[Claim_Status] = 'QUERY'")
Commenting on your (as it turns out somewhat unnecessary) code, OpenQuery simply runs an existing query - it does not return a result back to the calling routine, so the count within it cannot be linked back to the textbox.

The querydef part of your code was not doing anything useful at all - OpenQuery runs the named query 'query_count' listed, which for some reason you were opening as a querydef (query definition - used by Access to define all details of a query, and part of a querydef collection). The qdf was set to the SQL statement immediately preceding it, but this would have nothing at all to do with query_count in your variable qdf unless you were adding query_count as a new query to the querydefs collection.

In your code you could have used DoCmd.RunSQL to run an SQL statement directly (without querydefs) - but it still would not have returned any value back to the calling routine, so providing no link to your textbox.

-Stewart
Sep 2 '08 #3

P: 31
Hi. There is no way to link a Textbox control with the type of code you have constructed.

If you need a count to be displayed in the textbox you should forget about coding it and use the in-built DCount (domain count) function to do it for you instead.

In the control source property of an otherwise unbound textbox all you need to enter is
Expand|Select|Wrap|Line Numbers
  1. =DCount("*", "[tblClaim_Status]", "[Claim_Status] = 'QUERY'")
Commenting on your (as it turns out somewhat unnecessary) code, OpenQuery simply runs an existing query - it does not return a result back to the calling routine, so the count within it cannot be linked back to the textbox.

The querydef part of your code was not doing anything useful at all - OpenQuery runs the named query 'query_count' listed, which for some reason you were opening as a querydef (query definition - used by Access to define all details of a query, and part of a querydef collection). The qdf was set to the SQL statement immediately preceding it, but this would have nothing at all to do with query_count in your variable qdf unless you were adding query_count as a new query to the querydefs collection.

In your code you could have used DoCmd.RunSQL to run an SQL statement directly (without querydefs) - but it still would not have returned any value back to the calling routine, so providing no link to your textbox.

-Stewart
Thank q for your help
ANANTH
Sep 3 '08 #4

P: 31
Thank q for your help
ANANTH
Dear Stewart Ross Inverness

The same is working but is there any SQL Code to define the count in Text Box
Sep 13 '08 #5

Expert Mod 2.5K+
P: 2,545
Hi Tempalli. I'm sorry but I just do not have any idea what you are asking. You will need to tell us enough about your requirements for us to help you - which we can't at present because there is just nothing for us to go on.

-Stewart
Sep 13 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.