How to get a single value from a query without open a recordset?

[Min]

Hi,I have a query named LastInvoiceID: "Select top 1 InvoiceID From Invoice
Order by InvoiceDate Desc" which will return a single value.

Normally, I get this value using recordset. Is it possible to use just
something like DBEngine(0)(0).Execute to return the value,such as:

strInvoiceID = DBEngine(0)(0).Execute "LastInvoiceID"

I just want to make the code easy to read, is there anything like that?

Thanks!

 

[John Vinson]

Hi,I have a query named LastInvoiceID: "Select top 1 InvoiceID From Invoice
Order by InvoiceDate Desc" which will return a single value.

Normally, I get this value using recordset. Is it possible to use just
something like DBEngine(0)(0).Execute to return the value,such as:

strInvoiceID = DBEngine(0)(0).Execute "LastInvoiceID"

I just want to make the code easy to read, is there anything like that?

Thanks!

Execute is for *executing* action queries (append, update, delete
queries etc.)

Try instead

strInvoiceID = DLookUp("[InvoiceID]", "[LastInvoiceID]")

or even avoid the stored query altogether:

strInvoiceID = DLookUp("[InvoiceID]", "[Invoices]", "[InvoiceDate] =
#" & DMax("[InvoiceDate]", "[Invoices]") & "#")

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Renwei]

if you want retrun a query value below is sample code for you reference ,

Private Sub trans_itm_AfterUpdate()
On Error GoTo ErrHandle

Dim rs As Recordset
Dim db As DAO.Database
Dim ItemNo As String
Dim StrSql As String

ItemNo = itm_id.Value
StrSql = "SELECT ITM_PRICE FROM TBL_SERVICE WHERE ITM_ID=""" & ItemNo &
""""

Set db = CurrentDb()
Set rs = db.OpenRecordset(StrSql, dbOpenSnapshot, dbReadOnly)

trans_price.Value = rs!itm_price.Value

rs.Close

ExitErrHandle:
Exit Sub

ErrHandle:
MsgBox Err.Description
Resume ExitErrHandle
End Sub

-------------------------------------
Renwei
Bayamo Corporation
Msn: (e-mail address removed)
Email: (e-mail address removed)
-------------------------------------

Hi,I have a query named LastInvoiceID: "Select top 1 InvoiceID From Invoice
Order by InvoiceDate Desc" which will return a single value.

Normally, I get this value using recordset. Is it possible to use just
something like DBEngine(0)(0).Execute to return the value,such as:

strInvoiceID = DBEngine(0)(0).Execute "LastInvoiceID"

I just want to make the code easy to read, is there anything like that?

Thanks!

Execute is for *executing* action queries (append, update, delete
queries etc.)

Try instead

strInvoiceID = DLookUp("[InvoiceID]", "[LastInvoiceID]")

or even avoid the stored query altogether:

strInvoiceID = DLookUp("[InvoiceID]", "[Invoices]", "[InvoiceDate] =
#" & DMax("[InvoiceDate]", "[Invoices]") & "#")

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps