how to run a select query in VBA (Access) and store values in a variable

hello

i want to run a select query in VBA (Access) and want to store values in a variable like

str = "select CountryCode from Country where CountryName = '" & Combo22.Text & "';"


here combo22.text is the name of combo bo from where the value for where clause will b selected . now after getting this value i want to store it in a variable and then want to use it in insert query.

like

DoCmd.RunSQL "Insert into table (val1,val2) values(txtval.Text,str);"

can anybody tell me how to do it in VBA
how to execute a query in access in VBA code

and second question is that how after execution of a query we can use that reults to poulate in a combo .


waiting for an early reply
regards
Naheed

This should be in the Database section.

You are going to need a recordset object, and that depends on which version of Access you are running, and whether you are using ADO or DAO.

I'd personally stay away from DoCmd.Run... as its naff :p :)

There are loads of tutorials out and about, so have a search first, then post up in the db section when you have a problem with Access.


Vince

So your saying that you want to have the user make a selection
in the combo and click a button to execute the query. Then put
the results into variables and add those values to a new record
in a table.

Use ADO in a module. Will be very very similar to VB. Then you
can execute the query and insert into. No DoCmd or macros.
Have the ADO connectionstring connect to the database its in.
Then open a recordset with the sql statement being passed. Then
execute from the connection object the insert into statement.

Good suggestion is to search the database forum for examples.

HTH