currentDB() Value

  • Thread starter Thread starter Steven Scaife
  • Start date Start date
S

Steven Scaife

I am using currentDB() to run some SQL code in my form.

I need to get a value entered via SQL code that is to be run before this
line. When i have this value i want to do an insert statement. The insert
isnt a problem. Getting the value is.

the select statement is "SELECT TOP 1 Payment_ID FROM Payment ORDER BY
Payment_ID DESC;"

i thought i could do:

strVariable = db.Execute "SELECT TOP 1 Payment_ID FROM Payment ORDER BY
Payment_ID DESC;"

But it doesnt like this, is there another way of doing this

Thanks in advance
 
Steven Scaife said:
I am using currentDB() to run some SQL code in my form.

I need to get a value entered via SQL code that is to be run before this
line. When i have this value i want to do an insert statement. The insert
isnt a problem. Getting the value is.

the select statement is "SELECT TOP 1 Payment_ID FROM Payment ORDER BY
Payment_ID DESC;"

i thought i could do:

strVariable = db.Execute "SELECT TOP 1 Payment_ID FROM Payment ORDER BY
Payment_ID DESC;"
Click to expand...

You can't execute a SELECT SQL statement in code. You either need to use
DLookup() or open a RecordSet.

strSQL = "SELECT ..."
strVariable = DLookup("PaymentID", strSQL)

or

strSQL = "SELECT..."
set rs = db.OpenRecordSet(strSQL)
strVariable = rs!Payment_ID

(the above obviously does not include proper dimming, error handling, and
clean up)
 
Rick Brandt said:
You can't execute a SELECT SQL statement in code. You either need to
use DLookup() or open a RecordSet.

strSQL = "SELECT ..."
strVariable = DLookup("PaymentID", strSQL)
Click to expand...

AFAIK, you can't use a SQL string as the domain argument for any of the
domain aggregate functions. It must be a table or stored query. So Mr.
Scaife would do better to use your recordset example.
 
Dirk Goldgar said:
AFAIK, you can't use a SQL string as the domain argument for any of the
domain aggregate functions. It must be a table or stored query. So Mr.
Scaife would do better to use your recordset example.
Click to expand...

Yeah, you're correct. I had never done that myself but could have sworn
that I had seen past posts where people used select statments as the Domain
argument.
 
Back
Top