SQL Query in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Im trying to run a query in vba and take the result of the query and set a
text box on a form to that value..I try this code:

eventholder = DoCmd.RunSQL("select max([event id]) from events")
Forms![LogIn]![ActiveEventID] = eventholder

but it doesnt work..any ideas?
 
Hi Jim,

As far as I know, you cannot assign the results of queries directly to
variables in VBA, even in cases such as yours where the query is an aggregate
that would return only a single value. Also, the Docmd.RunSQL command is for
running action queries, not opening SELECT queries.

You could use the DMax() function directly in the form control to provide
the value. Or, you could open a recordset based on your query to provide the
value, such as:

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim lngEventHolder as Long
Dim strSQL as String

strSQL = "SELECT Max([event id] FROM events;"

set db = currentdb
set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If not rst.EOF Then
lngEventHolder = rst![event id]
endif

rst.Close
set rst = nothing
set db = nothing

The DMax() function would be easier. I'd probably only explore using the
recordset if your form slows using DMax(), unless you want to try it just to
see how it works.

HTH, Ted Allen
 
This seems good..I tried using this and I get an error:
Runtime error 3265
Item not found in this collection and focuses on this line of the code:
lngEventHolder = rst![Event ID]

thanks for your help
 
YOU ARE THE MAN!!!!!

I figured out the bug...I changed th sql query from
SELECT Max([event id] FROM events
to
SELECT Max([event id] as [EventID] FROM events
and the line:
lngEventHolder = rst![event id]
to:
lngEventHolder = rst![EventId]

thank you so much for you help...
 
Ah, good catch. I didn't think about it renaming the field because of being
an aggregate. glad you got it to work.

-Ted
 
Something like this air code:

Dim rsEventHolder As DAO.RecordSet

Set rsEventHolder = CurrentDB.OpenRecordset( _
"SELECT BLAH BLAH", dbOpenSnapshot)
Forms!LogIn!ActiveEventID = rsEventHolder.Fields(0).Value
rsEventHolder.Close



Im trying to run a query in vba and take the result of the query and set a
text box on a form to that value..I try this code:

eventholder = DoCmd.RunSQL("select max([event id]) from events")
Forms![LogIn]![ActiveEventID] = eventholder

but it doesnt work..any ideas?
Click to expand...
 
eventholder = DoCmd.RunSQL("select max([event id]) from events")
Forms![LogIn]![ActiveEventID] = eventholder
Click to expand...

Forms!Login!ActiveEventID = DLookup("Max([Event ID])", "Events", "")


You could also use

DMax("[Event ID]", "Events")

Hope that helps


Tim F
 
Back
Top