query Access 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to run a query based on the result of a combo box.

For example, in a form I have a combo box to select a road name. When I
select a road name I want a query to run in the afterupdate. I don't know the
codes for this.
I'd appreciate any help. Thanks.

Chai
 
To get the query to filter on the combo, you put its fully qualified name in
the Criteria row of the field column in the query builder for the field you
want to filter on. [Forms]![MyForm]![MyControlname]

How you run the query depends on what kind of query it is. If it is an
Action query (Update, Append, Delete, MakeTable) you can use either
Docmd.RunSQL "MyQueryName"
or
Currentdb.Execute "MyQueryName", dbFailOnError

(The second option is much better - Faster, and is not affected by
SetWarnings)

If it is a select query you will need to use the OpenQuery method. You can
get more detail on this method in VBA Help.
 
I have a make-table query base on the result of a combo box. Now, how do I
disable the messages that says "You are about to run a make-table query that
will modify data in your table" and "The exisiting table will be deleted
before you run the query". Thanks!

Klatuu said:
To get the query to filter on the combo, you put its fully qualified name in
the Criteria row of the field column in the query builder for the field you
want to filter on. [Forms]![MyForm]![MyControlname]

How you run the query depends on what kind of query it is. If it is an
Action query (Update, Append, Delete, MakeTable) you can use either
Docmd.RunSQL "MyQueryName"
or
Currentdb.Execute "MyQueryName", dbFailOnError

(The second option is much better - Faster, and is not affected by
SetWarnings)

If it is a select query you will need to use the OpenQuery method. You can
get more detail on this method in VBA Help.

Chai said:
I would like to run a query based on the result of a combo box.

For example, in a form I have a combo box to select a road name. When I
select a road name I want a query to run in the afterupdate. I don't know the
codes for this.
I'd appreciate any help. Thanks.

Chai
Click to expand...
Click to expand...
 
There better way is to use the Currentdb.Execute method. It is unaffected by
the warning messages. It also runs much faster than the docmd.RunSQL method
which is affected. If you use the RunSQL method, you must control the state
of the warning messages.
Before the Query
Docmd.SetWarnings False 'Turn off Warnings
After the Query
Docmd.SetWarnings True 'Turn on Warnings

One other note about the Make Table query. I never use them. The table
created by a make table query is very inefficient. It uses the Access
default size for text boxes which is 255 characters unless you make it less
in the database options and Long Interger for numeric fields which can also
be changed in the options. If it finds decimals in the numbers, it changes
them to Doulbe.

My recommendation is to create your table and set the field types and sizes
to what they should be. Then use an append query to put the data into the
table. You can use a simple SQL delete query to clear the old data out first.
This also reduces database bloat.

Currentdb.Execute "DELETE * FROM MyTable;", dbFailOnError
Currentdb.Execute "qappMyAppendQuery", dbFailOnError


Chai said:
I have a make-table query base on the result of a combo box. Now, how do I
disable the messages that says "You are about to run a make-table query that
will modify data in your table" and "The exisiting table will be deleted
before you run the query". Thanks!

Klatuu said:
To get the query to filter on the combo, you put its fully qualified name in
the Criteria row of the field column in the query builder for the field you
want to filter on. [Forms]![MyForm]![MyControlname]

How you run the query depends on what kind of query it is. If it is an
Action query (Update, Append, Delete, MakeTable) you can use either
Docmd.RunSQL "MyQueryName"
or
Currentdb.Execute "MyQueryName", dbFailOnError

(The second option is much better - Faster, and is not affected by
SetWarnings)

If it is a select query you will need to use the OpenQuery method. You can
get more detail on this method in VBA Help.

Chai said:
I would like to run a query based on the result of a combo box.

For example, in a form I have a combo box to select a road name. When I
select a road name I want a query to run in the afterupdate. I don't know the
codes for this.
I'd appreciate any help. Thanks.

Chai
Click to expand...
Click to expand...
Click to expand...
 
Back
Top