how to store results of a query in a variable

[Guest]

I have a need to store the result of a sql statement
by RunQuery("select file_name into formvariable from
tablename where ...")

in this case it isn't storing results into formvariable.

How can I put the results of a select statement into
a form variable?

Thank you,
-Me

 

[Guest]

After typing my response, it occured to me that you may also be able to do
what you want to do by using a domain aggregate function directly in your
form control (depending on what your sql is doing). So I have added the
mention of that here. Following is my original response to your question:

I would normally open a recordset based on the sql text and then step
through the record(s).

To open the recordset, just use:

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim strSQL as String

strSQL = "SELECT..." 'Enter your select statement here
set db = CurrentDB
'See help on OpenRecordset for various optional types and options
set rst = db.OpenRecordset(strSQL)

Then, if you know that the variable that you are looking for will be in the
first record, you can just use:

YourVariable = rst![YourFieldName] 'Brackets only needed if field name has
spaces

of course you could also assign the value directly to your form control as
well:

Me.YourControlName = rst![YourFieldName]

If you need to loop through the recordset to find the value that you want to
use:

With rst
Do while not .EOF
'do your evaluation/assignments here
Loop
End With

HTH, Ted Allen

 

[Guest]

Ted,

Thanks for the reply!
While implementing your suggestion, I am getting compile error - "User
defined type not defined" for
Dim db As DAO.Database
Any suggestions? I saw in some document that I should
goto references dialog box and then select appropriate object
library. I don't see any references dialog box.

I may be doing something else wrong.

Need your help again,

Thank you in advance!
-Me

After typing my response, it occured to me that you may also be able to do
what you want to do by using a domain aggregate function directly in your
form control (depending on what your sql is doing). So I have added the
mention of that here. Following is my original response to your question:

I would normally open a recordset based on the sql text and then step
through the record(s).

To open the recordset, just use:

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim strSQL as String

strSQL = "SELECT..." 'Enter your select statement here
set db = CurrentDB
'See help on OpenRecordset for various optional types and options
set rst = db.OpenRecordset(strSQL)

Then, if you know that the variable that you are looking for will be in the
first record, you can just use:

YourVariable = rst![YourFieldName] 'Brackets only needed if field name has
spaces

of course you could also assign the value directly to your form control as
well:

Me.YourControlName = rst![YourFieldName]

If you need to loop through the recordset to find the value that you want to
use:

With rst
Do while not .EOF
'do your evaluation/assignments here
Loop
End With

HTH, Ted Allen

I have a need to store the result of a sql statement
by RunQuery("select file_name into formvariable from
tablename where ...")

in this case it isn't storing results into formvariable.

How can I put the results of a select statement into
a form variable?

Thank you,
-Me

 

[Guest]

You probably don't have a reference to DAO. To add a reference to DAO, go to
Tools|References from the menu in the VBA code window. Then, scroll down and
look for Microsoft DAO 3.6 Library.

HTH, Ted Allen

Ted,

Thanks for the reply!
While implementing your suggestion, I am getting compile error - "User
defined type not defined" for
Dim db As DAO.Database
Any suggestions? I saw in some document that I should
goto references dialog box and then select appropriate object
library. I don't see any references dialog box.

I may be doing something else wrong.

Need your help again,

Thank you in advance!
-Me

After typing my response, it occured to me that you may also be able to do
what you want to do by using a domain aggregate function directly in your
form control (depending on what your sql is doing). So I have added the
mention of that here. Following is my original response to your question:

I would normally open a recordset based on the sql text and then step
through the record(s).

To open the recordset, just use:

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim strSQL as String

strSQL = "SELECT..." 'Enter your select statement here
set db = CurrentDB
'See help on OpenRecordset for various optional types and options
set rst = db.OpenRecordset(strSQL)

Then, if you know that the variable that you are looking for will be in the
first record, you can just use:

YourVariable = rst![YourFieldName] 'Brackets only needed if field name has
spaces

of course you could also assign the value directly to your form control as
well:

Me.YourControlName = rst![YourFieldName]

If you need to loop through the recordset to find the value that you want to
use:

With rst
Do while not .EOF
'do your evaluation/assignments here
Loop
End With

HTH, Ted Allen

I have a need to store the result of a sql statement
by RunQuery("select file_name into formvariable from
tablename where ...")

in this case it isn't storing results into formvariable.

How can I put the results of a select statement into
a form variable?

Thank you,
-Me

 

[Guest]

Thank you Ted,
-Me

You probably don't have a reference to DAO. To add a reference to DAO, go to
Tools|References from the menu in the VBA code window. Then, scroll down and
look for Microsoft DAO 3.6 Library.

HTH, Ted Allen

Ted,

Thanks for the reply!
While implementing your suggestion, I am getting compile error - "User
defined type not defined" for
Dim db As DAO.Database
Any suggestions? I saw in some document that I should
goto references dialog box and then select appropriate object
library. I don't see any references dialog box.

I may be doing something else wrong.

Need your help again,

Thank you in advance!
-Me

After typing my response, it occured to me that you may also be able to do
what you want to do by using a domain aggregate function directly in your
form control (depending on what your sql is doing). So I have added the
mention of that here. Following is my original response to your question:

I would normally open a recordset based on the sql text and then step
through the record(s).

To open the recordset, just use:

Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim strSQL as String

strSQL = "SELECT..." 'Enter your select statement here
set db = CurrentDB
'See help on OpenRecordset for various optional types and options
set rst = db.OpenRecordset(strSQL)

Then, if you know that the variable that you are looking for will be in the
first record, you can just use:

YourVariable = rst![YourFieldName] 'Brackets only needed if field name has
spaces

of course you could also assign the value directly to your form control as
well:

Me.YourControlName = rst![YourFieldName]

If you need to loop through the recordset to find the value that you want to
use:

With rst
Do while not .EOF
'do your evaluation/assignments here
Loop
End With

HTH, Ted Allen

:

I have a need to store the result of a sql statement
by RunQuery("select file_name into formvariable from
tablename where ...")

in this case it isn't storing results into formvariable.

How can I put the results of a select statement into
a form variable?

Thank you,
-Me