Setting a variable to a SQL statement result

  • Thread starter Thread starter Karen Hagerman
  • Start date Start date
K

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
 
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
 
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.
 
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
 
Back
Top