Re: update query: still having problems

Hi,




Remove the last parenthesis in the code I supplied, should be

DoCmd.RunSQL " UPDATE Department
SET Department.rate = [forms]![employee]![department
subform].Form.controls![rate]
WHERE Department.employeeID=[forms]![employee]![department
subform].Form.controls![employeeID]; "




With an SQL statement, you update the table, not the VBA Form, so, the left
side of the SET *MUST* be a table and a field:

SET tableName.FieldName = ...


***** If the right part of the = is a field from another table, then you use
an inner join,

but it is probably preferable to start with a SELECT clause:

SELECT Department.*, OtherTable.*
FROM Department INNER JOIN OtherTable ON Department.SomeFieldA =
OtherTable.SomeFieldB


where "OtherTable" is "the other table" you mention, but not supplied ita
name (unless it is sheet1 ? ), and "SomeFieldA" and "SomeFieldB" are the
appropriate fields making the "link" between the two tables, the two fields
defining the "lookup". I don't know what they are, given the information you
supplied.



Once you get that right (in design view, if you prefer), add the WHERE
clause to limit the records in table Department that are selected.

Once the right records are displayed, with the select, limit the selected
fields to the one to be updated:


SELECT Department.Rate
FROM ... WHERE...


and now, change the SELECT query to an update query. In the grid, under the
field Department.Rate, at the line Update To, add

[OtherTableName].[FieldNameSupplyingTheValue]


And then you have your query.



*****If the right side of the = is a control in a form, not a value from
another table, then use the appropriate syntax as previously suggested
(without the ending mismatch closing parenthesis)




Hoping it may help,
Vanderghast, Access MVP

"scubadiver" <scubadiver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:200C18BB-FC7C-4512-A141-6FA9EEF10DBC@xxxxxxxxxxxxxxxx
You are correct that the table to be updated is "department" but I need to
take the information I need from "sheet1". I thought the 2nd line would
something along the lines of:

SET [forms]![employee]![department subform].Form.controls![rate] =
[sheet1].[rate]

Your code throws up a syntax error: Invalid SQL statement.

"Michel Walsh" wrote:

Ha, you use CurrentDb? try from the User Interface or with DoCmd.
CurrentDb
does NOT solve FORMS!... syntax for you, while the UI and DoCmd does.

Also, for table and fields, preferable to use the dot syntax; the bang
syntax is generally reserved to VBA objects.

Also, your table, to be update, is Department? so try:


DoCmd.RunSQL " UPDATE Department
SET Department.rate = [forms]![employee]![department
subform].Form.controls![rate]
WHERE Department.employeeID=[forms]![employee]![department
subform].Form.controls![employeeID]); "


(in one line, or with the appropriate line continuation)





Hoping it may help,
Vanderghast, Access MVP


"scubadiver" <scubadiver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8761BF28-3C81-4F66-9B87-7E67C9FE9857@xxxxxxxxxxxxxxxx
This is what I now have:

UPDATE Department AS Subform
SET sheet1!rate = [forms]![employee]![department
subform].Form.controls![rate]
WHERE ([sheet1]![employeeID]=[forms]![employee]![department
subform].Form.controls![employeeID]);

I am wondering whether the "SET" line should be the other way round.

I get parameter boxes for

sheet1!rate
sheet1!employeeID
forms!employee!department

"Michel Walsh" wrote:

Hi,

Try the full syntax:

FROMS!formName!SubFormControlName.FORM.CONTROLS!controlNameInTheSubForm


(all cap word are keywords).


Note that you use the name of the control of the subform control, NOT
the
name of the form embedded into it (they MAY differ, and obviously, if
so,
it
is important to use the right one).



Hoping it may help,
Vanderghast, Access MVP

"scubadiver" <scubadiver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:17D7060D-4AC4-4178-85DD-1F0B8691B575@xxxxxxxxxxxxxxxx
I want to update a field called "rate" in the "department" subform
table
from
a field called "rate" in a temporary table called "sheet1".

I am getting parameter value boxes. Here is my SQL.

UPDATE Department
SET [sheet1]![rate] = [forms]![employee]![department
subform].Form![rate]
WHERE ([sheet1]![employeeID]=[forms]![employee]![department
subform].Form![employeeID]);








.