update query help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scubadiver - 08 Aug 2006 14:00 GMT

I have a main form (called "employee") and a subform (called "department").
The two forms are connected using "EmployeeID" and I want to update the field
[rate] in the "department" subform from another table called "Sheet 1" (which
is temporary). "Sheet 1" has two fields [employeeID] and [rate].

I want to put the information from [Sheet1].[rate] into [department].[rate]
using [employeeID] as the indicator but i'm not too sure of the SQL. The rows
in the "department" subform already have multiple rows.

cheers.

Reply to this Message

NathanJ - 08 Aug 2006 16:52 GMT

UPDATE tblDepartment
set departmentRate = forms!Sheet1.rate
WHERE tblDepartment.EmployeeID = forms!Sheet1.EmployeeID;

I think that is what you're looking for.
I hope it helps

> I have a main form (called "employee") and a subform (called "department").
> The two forms are connected using "EmployeeID" and I want to update the field
[quoted text clipped - 6 lines]
>
> cheers.

Reply to this Message

scubadiver - 17 Aug 2006 13:46 GMT

I had originally created the query so it would change the rate for weekID and
EmployeeID. It did work but I have decided to simplify it just to employee.

Can you tell me what might be wrong with the following. I want to update the
information from "sheet1" to "department":

UPDATE Department
SET Department.Rate = forms!sheet1.rate
WHERE (Department.EmployeeID=forms!sheet1.employeeID);

I get an "enter parameter value" for the following:

forms!sheet1.employeeID

> UPDATE tblDepartment
> set departmentRate = forms!Sheet1.rate
[quoted text clipped - 13 lines]
> >
> > cheers.

Reply to this Message

John Spencer - 17 Aug 2006 14:06 GMT

Try replacing the periods with exclamation marks. Also make sure that the
controls have the exact name you are referring to.

UPDATE Department
SET Department.Rate = [forms]![sheet1]![rate]
WHERE (Department.EmployeeID=[forms]![sheet1]![employeeID]);

If you still get a parameter request, then it is likely that you have
misspelled something.

>I had originally created the query so it would change the rate for weekID
>and
[quoted text clipped - 35 lines]
>> >
>> > cheers.

Reply to this Message

scubadiver - 18 Aug 2006 11:06 GMT

I've replaced mine with yours and I still get an "enter parameter value"
message for:

[forms]![sheet1]![employeeID]

> Try replacing the periods with exclamation marks. Also make sure that the
> controls have the exact name you are referring to.
[quoted text clipped - 45 lines]
> >> >
> >> > cheers.

Reply to this Message

John Spencer - 18 Aug 2006 13:01 GMT

It sounds as if the form isn't open or the control on the form has a
different name. Or the form has a different name.

> I've replaced mine with yours and I still get an "enter parameter value"
> message for:
[quoted text clipped - 55 lines]
>> >> >
>> >> > cheers.

Reply to this Message