CODEWITHSUNDEEP
sqlms-accessvba
user3788491
user3788491

Reputation: 99

MS access, write query in VBA and show result in text box

I have this query which works ok.

I want to write this query in Access VBA and when i execute this query to show me result in text field in my form. On my form i have text box which is name"Result" So i want when execute this query to show on my text box which is name"Result" But i want to write this in VBA. How to do that?

SELECT ORDINACIJA.Exam, Count(*) AS total
FROM ORDINACIJA
WHERE ORDINACIJA.Exam='EHO ABDOMENA' AND ORDINACIJA.month='JANUARY'
GROUP BY ORDINACIJA.Exam;

Upvotes: 1

Views: 3570

Answers (2)

MJH
MJH

Reputation: 2307

You don't need SQL for this. In VBA, you can use the DCount function:

Dim lngCount As Long

lngCount = DCount("Exam", _
                  "ORDINACIJA", _
                  "ORDINACIJA.Exam='EHO ABDOMENA' AND ORDINACIJA.month='JANUARY'")

Result.Text = lngCount


But a better solution is to set the textbox's "Control Source" property to the following:

=DCount("Exam", "ORDINACIJA", "ORDINACIJA.Exam='EHO ABDOMENA' AND ORDINACIJA.month='JANUARY'")

That way, the textbox's value will be correct when the form opens, and additionally, whenever there is a need to update the value (e.g. if the table's data has changed), just execute the following in VBA:

Result.Requery

Upvotes: 2

David Pratt
David Pratt

Reputation: 55

You can assign he Query to a string variable, and then use CurrentDB.OpenRecordset to execute the query, like so:

sub DoMyQuery()
dim S as String
dim R as recordset
  S = "SELECT ORDINACIJA.Exam, Count(*) AS total "& _
      "FROM ORDINACIJA "& _
      "WHERE ORDINACIJA.Exam='EHO ABDOMENA' AND ORDINACIJA.month='JANUARY' "& _
      "GROUP BY ORDINACIJA.Exam;"
  Set R = CurrentDB.OpenRecordset(S)
  ' do what ever needs to be done with data returned here
  R.close
end Sub

Upvotes: 0

Related Questions