Jimbo,
Details matter, of course, but it works somewhat like this:
In a function (I prefer functions over subs, the reason why is off-topic
here), create a recordset object with your SQL as the source. Open the
recordset object, check to see if it returned any rows, (it will, but check
anyway), if it did return at least one row, set the value property of the
control on the form to the field in your SQL statement.
For example.
Dim rs as ADODB.Recordset
Dim SQL as string
SQL = "select max([event id]) as last_event from events;"
With rs
.ActiveConnection = CodeProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.Source = SQL
.Open
If Not .EOF Then
Forms!Login!ActiveEventID = !last_event
End If
.Close
End With
If you made any particular mistake it was failing to name the column in your
SQL statement. Jet will still run it and use a default column name of Expr1
or some such. The problem is knowing what Jet decided to call the column
since you didn't name it yourself. I added "as last_event" so I knew the
column name--last_event.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"Jimbo" <ji********@motorola.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Im trying to run a query in vba and take the result of the query and
set a text box on a form to that value..I try this code: eventholder =
DoCmd.RunSQL("select max([event id]) from events")
Forms![LogIn]![ActiveEventID] = eventholder but it doesnt work..any
ideas?
