How to run a query and put the result into a variable

Question

I am running a query from the VBA editor of Access:

select max(somerow) from sometable

I want to put the result of this query into a VBA variable. How do i do it?

Answer 1

Look at Access Help for DMax function.

Dim varSomething As Variant
varSomething = DMax("somerow", "sometable")

Edit: I realize that suggestion is not what you were looking for. But it seems to me you may be taking the long way round to achieve something that is simple with the DMax domain function.

Answer 2

If you just want the Max value, you should consider using HansUps Solution.

Here is a solution using DAO:

Dim rs As DAO.Recordset
Dim sqlMax As String
Dim result As Integer

sqlMax = "select max(somerow) from sometable"
Set rs = CurrentDb.OpenRecordset(sqlMax)

If rs.Fields.Count = 1 Then
    result = rs.Fields(0)
End If

Set rs = Nothing

You will need to add a Reference to the Microsoft DAO Object library through Tools->References in the VBA Editor