seeing the append query before updating records

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

Guest

I want to use an append query as a "hot button" as follows:
Users need to add a variety of information for each contract. I have
created a select query that uses a bunch of pre-set information but the users
will need to update some of the info before appending the data. For instance
there may be 20 weeks, each of which needs a date entered for service. How
do I let the user see and modify the form/query in datasheet view before
appening the data to a table?
 
Ian said:
I want to use an append query as a "hot button" as follows:
Users need to add a variety of information for each contract. I have
created a select query that uses a bunch of pre-set information but the users
will need to update some of the info before appending the data. For instance
there may be 20 weeks, each of which needs a date entered for service. How
do I let the user see and modify the form/query in datasheet view before
appening the data to a table?
Click to expand...

Write your append (insert) query. From that SQL copy the SELECT portion,
add the appropriate FROM clause if necessary, and save that as a new
select query. Code the hot button to run the new select query. You can
code a new form or control to allow the user to confirm whether the
append should be executed.
 
"SMartin" -- I don't know a lot (any) VBA so I need to do this with macro's
and query design. I created a select query and used it to populate a
datasheet view of a from (gave me the correct info). I wrote an append query
with the form fields in each of fields for the append query. The problem is
it only appends the first row of data from the datasheet view. Any idea how
to get it to append all of the results? Here is the sql statement from the
append query.

INSERT INTO Services ( ContractID, ClientID, ProdName, ProdFee )
SELECT forms.frm_hb_loadservices.ContractID AS Expr1,
forms.frm_hb_loadservices.ClientID AS Expr2,
forms.frm_hb_loadservices.ProdName AS Expr3, forms.frm_hb_loadservices.fee AS
Expr4
FROM Contracts
GROUP BY forms.frm_hb_loadservices.ContractID,
forms.frm_hb_loadservices.ClientID, forms.frm_hb_loadservices.ProdName,
forms.frm_hb_loadservices.fee;


Ian.
 
The select query you wrote to populate the datasheet is where you want
to start. Once the datasheet is updated, the select query should return
all the updated values.

I suggest you code your hot button to return the select statement. You
don't need to write any code to do this as you probably know. Use the
wiz to set a button to open the query. Or better yet, open a /new/ form
displaying the results. On the new form, drop two new buttons. One to
"update these values?", which fires the append query. The other button
"cancel" simply closes the form. All this is doable with the wizard alone.

As opposed to my original suggestion, base your append (insert) query on
your original select query. Since it does not point to a specific record
displayed on the form you should be able to insert the lot. Make sure
you include any form filters you might have in a WHERE clause.

Test this on a copy of your database!
 
Back
Top