Executing queries in procedures

[manish.amina]

How do I execute SQL queries inside an EVENT procedure? I tried looking
for the command which does so, but can't seem to find it. ( EXEC SQL or
runsql )

Can someone post a simple procedure which executes an sql statement and
uses the value returned in some calculation within the procedure?

Manish

 

[Sandra Daigle]

Queries don't return values. However, you can open a recordset on a query to
obtain whatever values you need. If you are just trying to get a single
value from a specific field using specific criteria you can use the Dlookup
function. If you'll provide more information about what you are trying to do
I'll be glad to try to provide more detail.

Regarding a recordset, here is some simple code for opening a recordset and
cycling through it's records:

Public Sub WalkRecordset()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
'Open the recordset (once)
'the name of a saved query could be used in place
'of the SQL Select statement
Set rst = db.OpenRecordset("Select * from Customers")
With rst
' Test for records (EOF and BOF are both true when empty),
If Not (.EOF And .BOF) Then
' Loop through stopping when the EOF condition is reached.
Do Until .EOF
' Inside the loop we do something with each record,
Debug.Print "Customerid:" & .Fields("Customerid")
' Move to the next record
.MoveNext
Loop
End If
' Close the recordset
.Close
End With
'Destroy the object variables.
Set rst = Nothing
Set db = Nothing
End Sub

 

[manish.amina]

This is what I was trying to do earlier. I was trying to count the
number of records entered in the detail subform of a master detail
form. I was using the following procedure

Private Sub Form_BeforeInsert(Cancel As Integer)
If DCount("*", "[ANALYST-PRACTICE MATCH]", "[FIRST NAME] = " & [analyst
one-on-one sessions]![FIRSTNAME] & " AND [LAST NAME] = " & [analyst
invite list]![Last Name] & " AND [RESEARCH FIRM]= " & [analyst invite
list]![Research Firm]) >= 6 Then
Cancel = True
MsgBox "Sorry, only six items allowed", vbOKOnly
End If

End Sub

"analyst invite list" is the name of the subform (detail records).
But when I execute it, I get the following message

"Run-time error 2465:
Microsoft office access can't find the field "I"referred to in your
expression."

I cant seem to fix the error.

BTW, what does me![field name] mean. Does it store the current value of
the [field name] in the form?

Manish

 

[Sandra Daigle]

When making comparisons against text fields you need to quote wrap the text
values so that JET (the database engine) sees them as strings. Each of the
inside pairs of quotes (") will be
converted to a single quote character that will be kept in the criteria
string. The outsidemost quotes are string delimiters which are not embedded
in the string. Here is how it should look assuming that all of your test
fields are text. I'm also adding line continuation characters to break the
statement into multiple lines:


If DCount("*", "[ANALYST-PRACTICE MATCH]", _
"[FIRST NAME] = """ & [analyst one-on-one sessions]![FIRSTNAME] & """"
_
& " AND [LAST NAME] = """ & [analyst invite list]![Last Name] & """" _
& " AND [RESEARCH FIRM]= """ & [analyst invite list]![Research Firm] &
"""") >= 6 Then
Cancel = True
MsgBox "Sorry, only six items allowed", vbOKOnly
End If