Problem with getting Max of table values 1

[jpl458]

Have the following code:

Dim EmKeySql As String
EmKeySql = "SELECT Max(SentEmailTbl.EmKey) AS MaxOfEmKey " & _
"FROM SentEmailTbl"
Dim EMailcon As ADODB.Connection
Set EMailcon = CurrentProject.Connection
Dim EMailRs As New ADODB.Recordset
EMailRs.ActiveConnection = EMailcon
EMailRs.CursorType = adOpenStatic
DoCmd.RunSQL EmKeySql
End Sub

I get an error in DoCmd.RunSQL that states that I must have and SQL Statement. Have tried other variations but with no success.
I just want to get the max value of a 6 digit Primary Key called EmKey. The above SQL was copied from a query in the QBE Grid, and it runs perfect in there.
The query looks fine in the immediate window, except for the error part.

Thanks

jpl

 

[PHV]

Why not simply this ?
Dim myMax
myMax = DMax("EmKey", "SentEmailTbl")


FYI, DoCmd.RunSQL is only suitable with ACTION queries.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

I didn't know that you could get data directly from a table without setting up a recordset (for select queries)
this look like neither a select or action query. But it sure does work.

Thanks for the quick response.

jpl

 

[MarvinM80]

JPL,

Yes, the Domain function/methods are cleverly disguised queries that do all the behind the scenes stuff for you to give you things like Count, Sum, Average (and other) items in a domain. In most cases you can think of a domain as a column in a table. For more info, check the online Help for these terms:

[ul]
[li]DAvg Function[/li]
[li]DCount Method[/li]
[li]DFirst Method[/li]
[li]DLast Method[/li]
[li]DLookup Method[/li]
[li]DMax Method[/li]
[li]DMin Method[/li]
[li]DStDev Method[/li]
[li]DStDevP Method[/li]
[li]DSum Method[/li]
[li]DVar Method[/li]
[li]DVarP Method[/li]
[/ul]

Happy programming,
Marvin M80