Using MakeTable and UpDate Queries

  • Thread starter Thread starter Sandra Grawunder
  • Start date Start date
S

Sandra Grawunder

1. qryML1 makes a tblMLTemp to concatenate certain fields.
2. qryML2 makes a tblMLTemp2 to concatenate certain
fields using different criteria for the fields.
3. qryMLUpdate updates the certain fields in tblMLTemp
based on the values of the fields in MLTemp2.

These all work as expected manually and give the correct
results. Now, how do I automate this proc so that first
you run qryML1 to get tblMLTemp records Correct, then run
qryML2 to obtain the other changes in the table, then run
the qryUpdate to modify the records in tblTemp with the
records in tblTemp2.

Thanks in advance!
Sandra G
 
I may be missing something here, but I think there is a
very simple solution:

you can create a macro to run each query in order:
* Open a new macro in macro design view
* Select 'OpenQuery' from the 'Action' column
* Select the 1st query name from the 'Query Name'
Argument (bottom of screen)
* repeat (in order) for the other two queries

I believe this will work

If you don't want to be asked before running each query,
use the 'SetWarnings' action to turn them off before the
queries run - but DONT forget to turn them back on
afterwards (in the last line) or you'll accidentally save
design changes and all sorts of problems

Kind Regards,

Wes
 
All this rigamarole is to construct a table to be used in
a combobox to select a donor record, and also for fields
in mailings. The master donor table has a linked
relations table i.e. individuals related to the master
donor record. In some cases the relations are Household,
in some cases the relations are ContactP. If Household, I
run a query to concatenate the first names of the
individuals thus John and Mary Doe (Doe being the NameID
in the master record). If Contact then I want to combine
the first and last name thus Harry Smith (who is a
contact at ABC Bank, the NameID). The temp tables both
have a common field of CombNames to contain the
concatenated data from both queries. So eventually the
data can be displayed as:

John and Mary Doe
123 High Street
-or-
ABC Bank
Attn: Harry Smith
123 High Street

So far, so good. But now I need a way to automate this
whole process for use in the combo box. In other words,
when the form is loaded this procedure should run so that
the temp table is current and available for selection.
Will a macro work for this? How will it handle the usual
prompts about making a table, updating a table, etc.
Presumably, this should be seamless and invisible to the
user. I will also, at some point, need to filter the
results so that the combobox only shows a list of Smiths
rather than a huge list of names.

Thanks for your help,

Sandra G
 
All this rigamarole is to construct a table to be used in
a combobox to select a donor record
Click to expand...

Ummm... why construct the table? Lots of hassle and overhead!

Just base the Combo on the Select query rather than changing it into a
MakeTable query.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
OK, now it gets more technical (not too bad if you know
about forms design though)

Here we go:
1. to make macro run when the form opens:
* Open the form in design view
* Press ALT+Enter to view the properties window
(the properties dialog should say 'Form' in the
title bar, if not select it by clicking the little square
box at the top left of the form design window)
* In the properties dialog, choose the 'Events' Tab
and find the 'On Open' entry
* Now simply select the macro you created yesterday
from the dropdown list

The macro will now run EVERYTIME the form is opened.

2. to prevent the prompts from appearing when the macro
runs
* Open the macro in design view
* select the first line in the macro and choose 'rows'
from the 'insert' menu
* select 'SetWarnings' from the Action column in the
new row
* Choose 'False' in the Arguments section
* Now go to the last (blank) line in the macro
* select 'SetWarnings' again and enter 'True' in the
Arguments section

This turns off all confirmation prompts temporarily,
accepting 'Yes' or 'OK' as the user response, and then re-
activates them after the macro has run. Note: this also
turns off the prompts for saving design changes in forms
and queries so BE CAREFUL. It is important to turn the
warnings back on to save yourself the embarrasment of
accidentaly saving design changes after you have been
fiddling around.

3. To Filter the list of values

* I can't really help on this one just yet because I
need to know how you plan to get the criteria for the
filter (eg Input box from user, another combobox or
textbox, values from a table, etc)

See how you go on 1. & 2. first

Regards,

Wes Baker
 
Back
Top