Run Update Query Using the Where Clause

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

Guest

I want to run an update query that update a field only on the current form
which is a continuous form.

Is this possible and if so, do I do this in the SQL of the query and I'm not
sure how.

This is what I have for the SQL text of the query:

UPDATE InventoryTransfersFromForm, InventoryTransfersQuery SET
InventoryTransfersFromForm.BinQuantity =
[InventoryTransfersFromForm]![BinQuantity]-([InventoryTransfersQuery]![QTYTransfered]);

The update query works, but it updates all records on the form, previous and
current.
 
I want to run an update query that update a field only on the current form
which is a continuous form.

Is this possible and if so, do I do this in the SQL of the query and I'm not
sure how.

This is what I have for the SQL text of the query:

UPDATE InventoryTransfersFromForm, InventoryTransfersQuery SET
InventoryTransfersFromForm.BinQuantity =
[InventoryTransfersFromForm]![BinQuantity]-([InventoryTransfersQuery]![QTYTransfered]);

The update query works, but it updates all records on the form, previous and
current.
Click to expand...

It's not clear what you want to update: this individual record, or all
the records currently displayed on the subform. If the former:

UPDATE InventoryTransfersFromForm, InventoryTransfersQuery SET
InventoryTransfersFromForm.BinQuantity =
[InventoryTransfersFromForm].[BinQuantity]-([InventoryTransfersQuery].[QTYTransfered])
WHERE <key field> = Forms![MainFormName]![SubformName].Form![Control]

If the update should be for all subform records, use

=[Forms]![MainFormName]![LinkingFieldName]

using the name of the field that is your Master Link Field for the
subform.

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update primary key field with multiple "where" clauses 0
Update Table Field Value with SQL Query 2
update query using like criteria 3
Append Query Issue 0
Query with "In" in the WHERE clause isn't updateable 1
Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1
where clause 2
Update query to update new qty and cost 1

Back
Top