vba code to get sql query

[mk2lee1]

Hi, I would like to make a vba code that can query sql and later use that value to do something else.

For example, I would like to find out the number of values of certain table and if its more than let's say 3, I would like to send out the message.

I know how to send out the message but I am not sure how to write VBA code to get the sql query and put the result into a dim variable for later use.

Please give me answer thank you~~

 

[Savil]

Is this what you are looking for?

Dim lngVal As Long

lngVal = DCount("[WhateverField]", "WhateverTableName", "[WhateverField] > 3&quot

 

[JesseH]

I use the code below all the time to do run-time queries. It works well. Hope it helps.
JesseH
hernandj@eim-co.com

Dim qdf As QueryDef, StrSql2 As String
StrSql2 = "SELECT BPCSFV60_RCML01.CCUST, etc""
StrSql2 & "WHERE (((BPCSFV60_RCML01.CCUST)="
StrSql2 = StrSql2 & Me!txtCust & " ));"
Set qdf = dbs.CreateQueryDef("qrySelection", StrSql2)
DoCmd.OpenQuery "qrySelection"

 

[PaulF]

Dim db As DAO.database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("qrySelection&quot
With rst
.MoveFirst
.MoveLast
End With
MsgBox "Record Count is " & rst.RecordCount
Set db = Nothing
Set rst = Nothing

 

[mk2lee1]

i got error..
this is what i wrote
========================================
Private Sub Form_Load()
Dim lngVal As Long
lngVal = DCount("[ParticipateID]", "Participation", "[TestDate]> date()&quot
If lngVal > 0 Then
MsgBox "You have .... ", vbOKOnly, "Message Title"
End If
End Sub
=========================================
does anybody have any idea. Error message is
"exression you entered as a query parameter produced this error:' the object doesn't contain the automation object 'ParticipationID'"

 

[mk2lee1]

Also when I used gdf As QueryDef. I get "user-defined type not defined" error. How can I go about and fix this?

 

[PaulF]

I copied and pasted your code into a form, used EmployeeId from table Employees and <Date().. and it worked fine.... check the spelling of the field name

PaulF

 

[mk2lee1]

i was indeed mispelled something.. Thank you guys. Much needed help on this.