how to execute an SQL SELECT statement in VBA?

[kat17]

I want to execute an SQL SELECT statement in the "after update" event of a field on my form.

If the query returns NULL, I want nothing to happen.
If the query returns a result, I need to display a MsgBox.

I am also hoping that I can insert the query result into the MsgBox.

Can anyone give a few pointers??

 

[ProgramError]

Hi kat17

To run a query in vba you need docmd.runquery "name of query"

I would suggest you don't show the result in a message box window, keep these for messages. What I think you mean is you want to display a message if no records are found and if so the you need to put the following code in the 'no data' event.

Private Sub Report_NoData(cancel As Integer)
cancel = True
MsgBox "No records found", vbOKOnly
End Sub

Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[PHV]

Have a look at the IsNull and DLookUp functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

There are lot of pointers around here. The Domain Aggregates, as suggested by PHV is one way, another is using a recordset (which is often faster when working with linked tables)

Some air code ...

[tt]dim rs as dao.recordset
set rs = currentdb.openrecordset("select ...")
if not rs.bof and not rs.eof then
msgbox "blah blah " & rs.fields(0).value
'msgbox "blah blah " & rs.fields("fieldname").value
else
msgbox "sorry, no records"
end if
rs.close
set rs=nothing[/tt]

- you'll need a reference to Microsoft DAO 3.# object library for this to work (in VBE - Tools | References)

Roy-Vidar