By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

SQL in VBA

P: n/a
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?

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"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?


If it's that simple, just set the ControlSource of the textbox to:
=DMAX("([event id]","events")
If you wanted to get the result from a query, it is possible - but a lot
more code is needed and doesn't offer much of an advantage.
Nov 13 '05 #2

P: n/a
Yeah I wish it were that simple..I need it for that point in time

Nov 13 '05 #3

P: n/a

"Jimbo" <ji********@motorola.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Yeah I wish it were that simple..I need it for that point in time


Did you try my suggestion. I suspect it does what you need.
Nov 13 '05 #4

P: n/a
Yeah I know that will work...but I cant have it default to that
value...I need it dynamic to be the value of the query at that point in
time...thanks anyway

Nov 13 '05 #5

P: n/a
So you're trying to number the subevents to an Event record? You *can*
set the default value of the field based on what Justin suggested...
Just put your function into the Default Value property of the
appropriate field on your form. (can't be done at table level.) That
will give you the "next" value in the sequence.

Nov 13 '05 #6

P: n/a
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?

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.