select into

  • Thread starter Thread starter hoke
  • Start date Start date
H

hoke

Can anybody tell me how a can get an value from an access
table and store it in a VBA variable.

This is the query
select max(id)
from members

I already tried the help on RunSQL. But the link to
the "select into" topic leads me to a unavailable
helptopic.

thanx in advance
 
Take a look at the DMax domain aggregate function.

You can't just use a Query or an SQL statement directly to set a variable or
a specific control on a form or report. That's what the domain aggregate
functions are for.

If none of the domain aggregate functions, DLookup, DMin, DMax, etc. do what
you want. You'll have to open a Recordset on the Query and set the variable
from the content in the Recordset.

Larry Linson
Microsoft Access MVP
 
if you are using dao:

dim qdf as dao.querydef
dim sqlstring as string
dim rs as dao.recordset
dim lMax as long

set qdf=currentdb.createquerydef("")
sqlstring ="select max(id) from members"
qdf.sql=sqlstring
qdf.returnsrecords=true
set rs=qdf.openrecordset
if rs.recordcount>0 then
if isnull(rs.fields(0))=false then
lmax=rs.fields(0).value
end if
end if
'closing recordset
rs.close
 
Why create a querydef? Not picking, just asking if there is a reason why you
would do it. This is much simpler:

Dim rs As DAO.Recordset
Dim lngID As Long

Set rs = currentdb.OpenRecordset("SELECT MAX(id) AS BigID FROM members")

If Not (rs.EOF and rs.BOF) Then
lngID=rs("BigID")
end if

rs.Close
 
Back
Top