Assign value to variable from query

[Roger Oshaug]

How would I assign the MAX(value) in a tablecolumn to an Integer
variable, or put another way, assign any value from a table record to a
variable?

I am trying to achieve something like this:

Dim anInteger As Integer
Dim strSelect As String
strSelect = "SELECT MAX(tblTable.autonumberColumn) From tblTable;"

anInteger = CurrentDb.Execute(strSelect) 'or
anInteger = DoCmd.RunSQL(strSelect)

Neither DoCmd.RunSQL or CurrentDb.Execute lets me do this. I must be
missing something basic and essential since I get an easy understandable
error message stating: "compile error, expected function or variable"
and that I have not found any information where someone is trying to do
something similar.

I am not completely new to programming and databases, and I do belive I
remember doing something very similar with a window-application using
VB.NET and Access, but in Access 2003 VBA I am stuck.

I would be grateful if someone would point me in the right direction
(and sorry if this is a stupid question, I will not be surprised if it is)

[Lyle Fairfield]

Dim AnIntegerMayNotBeBigEnough as Long
' JET's INTEGER = VBA'S Long
AnIntegerMayNotBeBigEnough = CurrentDb.OpenRecordset("SELECT Max(ID)
FROM Customers").Collect(0)