Setting global variable with result from SQL statement

[jon]

I am trying to obtain the status of a sales order, and store that status in
a global variable.

There is only ever 1 record that is returned from my SQL statement which
obtains the status, but how do I put the result into the global variable ?

I can amend the SQL to put the result in a table, but that just pretty much
puts me in the same position.

The only thing I can think of, is to open a form with the results of my SQL,
and set the variable to the value of the linked text box on the form, then
close the form, but this would be very messy, and I'm sure its not the
correct way to do it.

Heres a rough example of what I want to do

Global ORDER_STATUS as string

Public Function Get_sales_order_status() as string

ORDER_STATUS = Docmd.RunSql "SELECT ORDER_HEADER.STATUS FROM CS3TEST_OPHEADM
WHERE (((CS3TEST_OPHEADM.ORDER_NO)=SEL_ORDER_NO()));"

End Function

I know this is obviously wrong, but I thought it may help with my
explination of the problem.

thanks for your help.

Jon

 

[Brendan Reynolds]

I am trying to obtain the status of a sales order, and store that status
in a global variable.

There is only ever 1 record that is returned from my SQL statement which
obtains the status, but how do I put the result into the global variable ?

<snip>

You can do this by using the Access DLookup function or by opening a
recordset. Opening a recordset is the more powerful and flexible solution,
but using DLookup might be easier to begin with.

Here's a link to the online help topic on the DLookup function ...

http://office.microsoft.com/client/...rt=2&ns=MSACCESS&lcid=2057&pid=CH100728911033

.... and here's a link to the topic on the OpenRecordset method ...

http://office.microsoft.com/client/...&ns=MSACCESS.DEV&lcid=2057&pid=CH101096361033

 

[jon]

Fantastic.
I owe you a beer, not sure how I'm going to get it to you though ;-)

Have a good weekend

Jon