Can't get results from .Execute 2

[randy700]

Can someone tell me where I've gone wrong with this code?

dim dBase as dao.database
dim strSQL as string
dim intNumber as integer
set dBase=CurrentDb
strSQL = "SELECT Max(Table.Field) FROM Table"
intNumber = dbase.Execute(strSQL)
set dBase=nothing

 

[RoyVidar]

Never used the execute method myself, so I don't know much about it. I usually open a recordset:

[tt]dim dBase as dao.database
dim strSQL as string
dim rs as dao.recordset
dim intNumber as integer
set dBase=CurrentDb
strSQL = "SELECT Max(Table.Field) as MyMax FROM Table"
set rs = dbase.Openrecordset(strSQL)
if not rs.bof and not rs.eof then
intNumber = rs!MyMax
end if
rs.close
set rs=nothing
set dBase=nothing[/tt]

 

[mgolla]

Randy8700,
why not just try something like this:

dim dBase as dao.database
[red]dim rec1 as dao.recordset[/red]
dim strSQL as string
dim intNumber as integer
set dBase=CurrentDb
strSQL = "SELECT Max(Table.Field)[red] as myfield[/red] FROM Table"
[red] set rec1 = dbase.openrecordset(strSQL)[/red]
intNumber = [red]rec1.Fields("myfield")
rec1.close[/red]
set dBase=nothing

HTH
Mike

Dooobie...Doobie......Dooo

 

[randy700]

Changed my code to....

dim dBase as dao.database
dim recSet as dao.recordset
dim strSQL as string
dim intNumber as integer
Set dBase = currentdb
strSQL = "SELECT Max(Rem4ID) AS HighID FROM Rem4"
set recSet = dBase.OpenRecordset(strSQL)
intNumber = recset!HighID
recSet.close
set recSet=Nothing
set dBase=Nothing

When I run this code I get an error that says....
Object variable or With block variable not set.

The code is stopping on the set recSet line. Help?

 

[mgolla]

which set recset line?

the first or the second?

if it is the second just comment it out and see if the code works.

HTH
Mike

Dooobie...Doobie......Dooo

 

[randy700]

the first (the important one)

 

[RoyVidar]

If it's the first, try using currentdb directly:

[tt]set recSet = CurrentDB.OpenRecordset(strSQL)[/tt]

Roy-Vidar

 

[mgolla]

make sure you have a dao 3.x reference in your references library

HTH
Mike

Dooobie...Doobie......Dooo

 

[randy700]

That worked. Thanks Roy.