SQL Query Results in a variable

[svtguy02]

I've got such a simple SQL query, but it won't output the friggin results of the query!

I put the query in a "Query" in access, run it... and it outputs me a integer value. I'm running the following VBA code in "Form Load"

Private Sub Form_Load()

Dim strSQL As String

strSQL = "SELECT Count(*) FROM ContactDetails;"

MsgBox strSQL

What it does is on form load instead of popping up a msgbox saying 41 or whatever the integer value of that count query should be.... it outputs the query itself:

SELECT Count(*) FROM ContactDetails

Any ideas on how I can get the SQL query to run as a query and output the value into the msgbox?

 

[boblarson]

A message box is not built to accept the results of a query. Build your own form, bind the form to the SQL string, and put a single text box on it bound to the single field.

 

[boblarson]

But, you could use a recordset to return the results to a message box IF you are only returning a very short amount of data. If one record and one field then you can use:

Dim rs As DAO.Recordset

Set rs = CurrentDb.Open("SELECT Count(*) FROM ContactDetails")

MsgBox rs(0)

rs.Close
Set rs=nothing

 

[Uncle Gizmo]

You may be able to adapt the code in this thread here:

 

[svtguy02]

well, I'd ultimately like to get the value output in a text box.

I just need to know if there are records in the table for SAID ID number. do a count and output the value to a txt box then use an IF statement from there.

IF me.txtbox > 0 then BLAH BLAH else BLEH BLEH
end if

and I want this SQL Query to populate me.txtbox

 

[boblarson]

If you just want the text box output then use a DCount as the control source:

=DCount("YourKeyIDFieldFromYourTable","ContactDetails")

 

[svtguy02]

the Dcount proved to be the easiest and most successful for what I needed!

dcount doesn't mess around Thanks alot for all the suggestions and help guys!