Re: update query: still having problems

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]);






.