SQL Statements in an Event Procedure

  • Thread starter Thread starter David
  • Start date Start date
D

David

I am trying to do an SQL statement in a module. It is
highlighted in red and doesn't like it. Is it even
possible to do this in an event procedure???

Here is what I have.....


temprevnum = SELECT tbl_Revisions.RevNumber
FROM tblRevisions
WHERE me.MachineNum = tblRevisions.MachineNum;
 
David said:
I am trying to do an SQL statement in a module. It is
highlighted in red and doesn't like it. Is it even
possible to do this in an event procedure???

Here is what I have.....


temprevnum = SELECT tbl_Revisions.RevNumber
FROM tblRevisions
WHERE me.MachineNum = tblRevisions.MachineNum;
Click to expand...

You can't actually execute an inline SQL statement in that fashion. You
need to open a recordset on the SQL statement, and get the value from
the Fields collection of the recordset. You can do this using objects
from either the DAO or ADO object library, and I'd be happy to show you
how. However, just to do the simple lookup you describe, it's probably
simplest to use the DLookup function, which handles all that for you:

temprevnum = _
DLookup("RevNumber", "tblRevisions", _
"MachineNum=" & Me.MachineNum)

Note that, if there are more than one record in tblRevisions with a
matching MachineNum, there's no knowing which one you'll get the
RevNumber from.
 
...and just a late chip in, if the text is red in the IDE window (and judging
by your code) you haven't put the text (SQL statement in this case) in
quotes. Just for next time ;o)
 
Back
Top