Setting a variable to a SQL statement result

[Karen Hagerman]

Why doesn't this work?

1.
Dim intTopOne As Integer
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim strSQL As String

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn

cmd.CommandType = adCmdText
cmd.CommandText = "SELECT TOP 1 tblRent.RentID FROM tblRent"

intTopOne = cmd.Execute

or this

2.
Setting the control source for a text box to a query with the above SQL statement

There must be a way to set a variable to the result of a SQL statement but I'm just stymied.

Karen

 

[Karen Hagerman]

Dan,

Thanks. Let's imagine however that I'm still learning, any suggestions as
to the correct terminology I could use with DLookup to find the LARGEST (Top
1)

DLookup("RentID", "tblRent",???????)

or

when you say open the recordset and assign the value to the text box, that's
the normal way?

Karen

 

[Dan Artuso]

Hi Karen,
Okay, we can imagine that

Since you want to find the Max RentId, we can use DMax instead of
DLookup.

Dim intRentId As Integer
intRentId = DMax("[RentId]","tblRent")

Let me know if this works for you.

 

[Karen Hagerman]

Dan,

Thank you, thank you, thank you. I have been chasing so many possible
solutions for longer than I'd want to share. This is EXACTLY what I needed.

Karen