Append Query with Combo List

[Guest]

I am attempting to create an append query. The area that I am having a problem with is that I want to have the query question the user to specify a value for the 'where' condition. Normally I would use the [enter value here] interface and the user would type the response for the query to run. However, to eliminate any chance of user-input typos, I would rather have the user select from a pick list (combo box) to use in the query. I have a separate table set up that I can use for the combo box values

The object is to have the append query use the combo box to select a <> condition where it would append all records that do not meet the value selected - thereby all other records would be written to another table. Then another query would be used to delete those same records (using the same techinque) from the first table. All this to be combined in a macro.

Sample data structure
table1 - table that I want to move data fro
useri
l_nam
compan
departmen
etc..

table2 - lookup table used for pick list (combo box) value
departmen

table3 - shadow table of table1 - to store the appended data
useri
l_nam
compan
departmen
etc..

Sadly, my knowledge of VBA is non-existant, so if If anyone can shed any light on how to use a combo box with a query I would greatly appreciate it.

 

[Nikos Yannacopoulos]

Rod,

You do not necessarily have to resort to VBA, it can be done without.
First of all, make a simple form to host the bombo box. Then, in your query
design, type in the criterion like:
<> Forms!FormName!cboName
or
= Forms!FormName!cboName
for the delete query, substituting FormName and cboName with the actual
names of the form and the combo box. As long as the form is open when you
run the queries, this will get the criteria values right off the form.

If you want to take it a step further, you can add command button(s) on the
form to run the queries. It could be one button that runs both, or two
separate buttons, one for each. In either case the button runs a macro (the
command button wizard will guide you through the process of assigning a
macro) with an OpenQuery action (or two if both in one). You can even add a
SetWarnings action with arg. False at the beginning and a second one with
arg. True at the end, to skip the confirmation to append / delete records.
If you want to do that, add these actions only when you are happy that the
rest of it works fine, to avoid nasty surprises!

HTH,
Nikos

I am attempting to create an append query. The area that I am having a

problem with is that I want to have the query question the user to specify a
value for the 'where' condition. Normally I would use the [enter value
here] interface and the user would type the response for the query to run.
However, to eliminate any chance of user-input typos, I would rather have
the user select from a pick list (combo box) to use in the query. I have a
separate table set up that I can use for the combo box values.

The object is to have the append query use the combo box to select a <>

condition where it would append all records that do not meet the value
selected - thereby all other records would be written to another table.
Then another query would be used to delete those same records (using the
same techinque) from the first table. All this to be combined in a macro.

Sample data structure:
table1 - table that I want to move data from
userid
l_name
company
department
etc...

table2 - lookup table used for pick list (combo box) values
department

table3 - shadow table of table1 - to store the appended data.
userid
l_name
company
department
etc...

Sadly, my knowledge of VBA is non-existant, so if If anyone can shed any

light on how to use a combo box with a query I would greatly appreciate it.